Having a character die in most games is a pretty trivial thing. You reload and go again, respawn and go again, or begin the level anew. No biggie. Some games though really go big on character death and make it a huge deal like Diablo when playing on Hardcore or oldschool RPGs. Of course there is a huge cultural component to this where the Hardcore people scoff at the Softcore people calling them 'WOW kiddies' and the Softcore people laugh at the Hardcore folks calling them nolifers. As usual both sides that spend time flaming each other on various forums are fools because there is clearly no 'better' way.
I have been reading some RPGs that deal with this very explicitly lately like the Dresdin Files RPG, the Leverage RPG, and Nobilis which pretty much make the assumption that the characters are immortal. Not that the characters can do anything, of course, but the baseline is that characters can get beat up, defeated, or stymied but that losing a fight means they run away while badly injured. They make no attempt to frame this as a fair simulation and describe everything in terms of the narrative. A character sacrificing themselves deliberately to accomplish a goal that is worthy of their sacrifice is all good, randomly getting offed by a mook who rolled well is not.
I don't know exactly how I feel about this sort of thing. I know for sure that I hate the idea of Hardcore in Diablo 3 because my character will die when I get a lag spike or disconnect and my skill will not be the point of failure. I see little fun in that. Being able to die from a monster attacking me when I play a heroic fantasy RPG though feels entirely okay. Obviously I want the GM to make fights that won't kill characters all the time because story and character development is key for me but the thrill of knowing I could die makes victory so much sweeter. I think the key is that I enjoy tactical decisions in an RPG and I am completely okay with my decisions making the difference between life and death.
I suspect that threat of death for failure to play well is why I find DnD so frustrating. At low levels anything can kill you and no amount of strategy is relevant. The monster is going to roll an attack and if it rolls a 20 you die. You have no defensive action you can take that will alter that scenario. Of course high level play is similar in that the beholder is going to blast you with an eye ray and you will roll a save to see if you die or not. I feel a lot better about HBT in that department because characters have quite a few alternatives for increasing their survival from the simple Defend action to using a Power that keeps you out of harm's way. You might still die but at least you can feel like you have control over the situation and when things get ugly there are always options to give you an out.