For his most recent "Experienced Points" article on The Escapist, Shamus Young tackles the continual reduction of any meaningful penalty to dying in a video game, with the penalties utilized in Ultima Online, EverQuest, World of Warcraft, and Too Human used as reference points.
The single-player game Too Human was widely criticized for its lengthy death animation. It was twenty seconds long, which is far shorter than even the relatively gentle penalties of World of Warcraft. So one of the most punishing single-player games is less severe than one of the gentlest online games. Why is this? People making online games have decided that games need to punish players for failure, but I wonder how many of them have stepped back and asked why this is and what the punishment is supposed to accomplish. Personally, I think death penalties have been reduced way too far. When even modern day cooperative games lack any real sense of failure when your allies die, there's a problem.
We can look at this by taking these ideas to their extremes. A game where death is permanent would only appeal to the hardest of the hardcore masochists. On the other hand, you can imagine a game where there was no death penalty at all. If you fall in battle, you pop right back up at full health with no interruption at all and keep playing. Since this would basically make you invulnerable, I don't think that would be fun either. There would be no reason to learn to play well, because it wouldn't be any different or more rewarding than playing ineptly.
So games do need some sort of death penalty, but I don't think they need very much. Single player games are still fun, even though they never inflict more than a minute or two of entertainment damage on the player. People still work to get good at them and desire to learn to play them well. Death brings about a break in forward progress and flow, and I think that very little additional punishment is needed to make death unpleasant for most people. I know when I play World of Warcraft I strive to avoid it, even when I'm under level ten and death has no penalty. I don't like failure, and I suspect that's true of most people that sit down to play a videogame. The desire to learn and the drive to do well provide a great deal of incentive for players to avoid dying, and is probably a far better motivator than grind-inducing XP debt.