Pathologic 2, it's a Russian horror game. There are real, permanent repercussions for dying and loading a save won't help you get rid of them. Sadly saying too much is a huge spoiler, but there's actually a storyline that's revealed with each death. Your deaths also affect the physical world.

[This comment has been deleted by an automated system]

Monaco is a fun example where stealth frequently fails and yet, you just have to scramble to do something and ruuuun. You can end up hiding and trying again but short of getting everyone killed, it's hard to get a game over. Your friends can revive you, as long as they don't get caught and killed themselves.

It's a good mechanic where it's more "let's go save Dave" then "thanks Dave now we need to restart".

No I don't know any Dave's, names have been changed to protect the guilty.

Rogue Legacy has this as its whole gimmick. The second one is pretty good!

Getting over it?

Assassin's Creed. They are basically games inside games.

Bioshock games (and System Shock before them) have in-game systems for reviving protagonists after death. Sometimes they're Quantum Reconstructors that need to be turned on in each level to use them, sometimes they're Vita-Chambers ready to use, sometimes it's your all-in-one utility companion Elizabeth with a medical bag. In all cases you're free to continue the fight after your death, though sometimes with penalties like restored enemy heath or monetary costs.