Generally, dying and resetting in game releases all of the tension that's been built up to that point in the player. The specific game design determines how we want players to deal with losing. The determining factor of how the game handles it is how quickly the player gets right back into things.
Sometimes we want the player to get right back into the action immediately. Death is merely an inconvenience, and the tension loss is momentary (and minimal) at best. The designers clearly want the player to keep playing. This choice emphasizes keeping the player in the flow state as long as possible. In games like this, players often have multiple lives that are merely a resource to continue playing, or even unlimited continues. Minimal friction between attempts generally means that there's little to no tension lost. Arcade games like the Simpsons, Double Dragon, House of the Dead, and so on are excellent examples of this.
Sometimes we want the player to take some time and reflect on their loss, so we intentionally reset the tension through game loss, followed by a cooldown period (e.g. a corpse run, having to replay some non-trivial amount of content, losing some resources, etc.). This can emphasize the encounter and encourage the player to consider why they lost and perhaps come up with a new approach or an opportunity to make a different choice. Many games today lean more heavily into this mindset because we don't expect players to lose very often (except maybe in harder games like Dark Souls), so designers want players to reflect on what caused the game loss.
Most games are somewhere in between. The biggest factor in how much tension is lost between attempts is the amount of time it takes for the player to get back to where they lost. The longer the amount of time it takes, the greater the tension release and rebuild must be.
Got a burning question you want answered?