I see your point. There are games were, as you said, dying feels natural and sometimes even entertaining, but sometimes it's just way too painful.
In singleplayer games, the only game that made death very annoying was FarCry on the PC. That game (without mods that already corrects this problem) only relied on checkpoints and you couldn't never, ever, be able to save and/or quicksave. Sometimes the distance between checkpoints was pretty far and that becomes a real problem about half-way and beyond. I never finished this game for that very reason, and because I hated those damnes mutant monkeys.
For MMOs, belive it or not, I played Ultima Online in a private server a few years ago, after playing World of Warcraft for a year or so (Burning Crusade era) and I felt your pain when you died. I clearly saw where Blizzard took it's inspiration for death, but WoW made it a million times better. Guild Wars was another MMO where the death mechanic is very frustrating.
Guild Wars won't take away your precious XP, it won't wear down your armor or weapons as there is no need at all to repair armor and weapons, in fact, there is no such option, your armor and weapons never wear and are always up and running for the next battle. Nor does it make you walk overly long distances to reach your corpse. So, if those penalties are completely nonexistant on that game, what does it make it so annoying?. When you die, you resurrect at the nearest resurrection shrine, but your stats are lowered temporaly and the only way to remove those are returning to a Post or Town or kill like a million baddies to return to your normal stats.
You're severly weakened and you may not survive the next battle, when you die again, you get even more weaker and so on and so forth until you get your stats at 60% lower than they originally were, forcing you to retreat the nearest Post or Town and battle all the way, again, killing the same baddies you already killed, if you killed any in the first place.
But also death can be rewarding, even make it part of the story itself, just look at Planescape Torment. I can't say much, because it would be a spoiler, but I'll just say that to solve something that may not have a solution at all, death is the only way to go.