Depends. If it's a death without consequence it destroys the tension. If the death still holds consequence, then it will hold an appropriately equal level of tension with it based on how likely it is to occur within the parameters of the story.

For example (gaming): Death in a lot of video games holds no consequences other than having to load the game. It can be frustrating but it's hardly the end of the world, especially since most games let you choose when and where to save and load. Now take Dark Souls, a game that doesn't let you save/load and will ruthlessly slaughter you, causing you to lose experience and progress. Death suddenly has consequences attached. There is a palpable tension in Dark Souls when faced with death--especially if you have a lot of experience to lose--than in, say, Mass Effect, where you just load the game and try again with a different approach.

Or, take X-Com. Deaths suck but you can always reload unless you toggle the "Ironman" option in the latest edition, which prevents loading the game past the current turn. Suddenly all your squaddies' deaths hold impact, especially if you renamed them to RP characters or living people.

It's why nobody talks about or cares about Sheppard's dramatic death at the beginning of Mass Effect 2. It was a death that was, for all practical purposes, without consequence to the player. It had no impact on how they played the game, it limited no options, and it was entirely out of their control as a contrived way to start the story.

To summarize: The greater the consequences attached to death, even if the death is reversible or temporary, the greater the tension one will derive from it. Especially if the consequences are permanent in some manner, like you could bring someone back from the dead but they would lose all their memories, or bring someone back from the undead but only as a walking corpse, etc.