Alyssa Rosenberg, a blogger whose writing I quite enjoy, covers many new media and pop culture phenomena but doesn't say much about games. In talking about Portal, she begins to address why: "I find dying in-game incredibly stressful." She concludes:

But for all the overblown talk of what video games do or don’t make us comfortable doing, I’m surprised that there isn’t more conversation about what dying in-game makes us feel about our own deaths.

I don't remember which one is Clyde, but I always blame him.
I got thinking about character death in gaming after reading this. Really, it's an odd concept because it's a mixed bag of concepts. We lump a whole bunch of different occurrences and design choices into this one idea.
Thirty years ago, avatar "death" or destruction made perfect sense as a design concept. You need a mechanism in your game to tell players that they have lost or failed at their tasks.
Beyond just communicating failure to the player, avatar "death" used to be a mechanism that meant the kid behind you at the arcade would get a chance, eventually, to put his quarter in the machine and take his turn after you or at least a mechanism by which you would keep pumping in your change for one more chance at the challenge.
Simple, easy — a time and talent constraint built in. The more you suck, the more quarters the machine will consume!
In the 1980s, though, the arcade famously entered our living rooms. Throughout the NES era and into the SNES age, though, we had the convention of game "lives" built in. By then, it was just how we played, and 1UP was part of the lexicon. Trying and failing to cross a chasm or fight an enemy meant you "died," and you needed another guy.
I was always really glad for the collected 1UPs by World 3.
As we all know, though, games have come a long way since the 1980s. There was a time when they were all sets of arcade skills that sometimes had stories attached; now, we have a whole collection of stories that also have skill sections built in.
Unlike Alyssa, I don't get stressed when my first-person player character "dies" in Portal.  Her death is impermanent; the player's respawn is nearly instantaneous and the game puts the avatar pretty much right back at the site of the player's failure.
I no more stress out about launching myself into a turret (oops) than I do about laying a jigsaw puzzle piece in the wrong corner or about missing a move in Tetris. Portal is ultimately about solving puzzles, and although there's a great narrative framework going on, I don't feel personally affected by Chell's ceasing to be; I only feel frustration at my lack of talent or timing.
To infinity and beyond! Wait, what?
That said, there is indeed the frustration of bruised pride to contend with. I don't feel any more attached to the persona of Chell or to her story in Portal 2, but I take more offense at dying because the game is easier. It relies more on thinking puzzles through (which I can do — and do well) rather than on reflex timing (which I can't do well at all). When I fail at something that I could physically have done right because I hadn't thought it through more clearly, I take personal offense at the failure. This puzzle — this story — must have a right answer, and so I must find it. Otherwise, I, personally, have failed.
We very rarely have a limited number of second chances anymore. Our games do still exist along the pass/fail, do/die dichotomies, but our stories, as a general rule, no longer continually penalize failure. Rather than face a character death, we are instead taking a Mulligan on the last five minutes. We get a rewind (sometimes literally as in World of Goo) back to before that jump or that shot or that ambush in the corridor.
Sometimes, though…sometimes, it is still personal. I got really ticked off on the very few occasions when my Commander Shepard died and I had to reload. In her case, I did feel deeply invested in that character. She was important, her story is important, and death didn't feel like taking a do-over on a game mission: It felt like a deeper kind of failure, the kind with some sort of betrayal or judgement attached. The feeling only got worse in Mass Effect 2, where I had the ability to get other characters permanently killed off. During the climactic mission of the game, I routinely let my fear of harming others send me into a kind of paralysis, during which I had to pause the game and pace around the room instead.
My indecision had nothing to do with this. By which I mean, it had everything to do with this.
Of course, that's entirely by design. When BioWare can make me pace around the room and ask my cat for opinions on who should lead a team (his response was to nibble on my arm), they've won. Anyone designing a huge-budget, open-world, cinematic-style, triple-A game is invested in the player's investment. That so many of us seem to have fangirled over this franchise is not an accident.
Not all games, though, are so large scale.

This summer's Xbox indie-darling Bastion has finally made the leap to PC via Steam. After watching all of the game criticism circles consistently lighting up about this title for a couple of months, my curiosity had the better of me, and last week I did something I rarely do and jumped on a day-one purchase. I had some time that evening to give it a whirl.

I kind of suck at Bastion.
It's not a game at which one can suck, exactly, and yet I manage to do so. Still, I can tell that many of my woes are simply clumsiness: The mouse-and-keyboard combination isn't necessarily ideal for titles designed with an Xbox 360 controller in mind, and I might need to remap a couple of keys for easier use. Over time, I will adapt to this system, and after a few days — having mapped my muscle memory to this particular set of mechanics and demands — I will cease sucking.
However, being terrible at Bastion for the time being has proven useful with insights on character death. The gimmick of the game is narration: You hear what you're doing, what you've done, and what you're about to do, and you hear it with inflection and judgement. Thus, the first time Kid plummets off the side of the path to his doom, the narrator is patient and understanding. The third or fourth time, I feel the narrator's patience begins to wear thin.
On the plus side, there are plenty of jars and such to smash with my hammer while I fail to smash enemies.
The voice of the narrator is meant to be kindly and guiding — at least in these early segments of the game. (I don't know if it will change or not; I've intentionally been avoiding spoilers and reviews.)  When he intones, "And so, Kid fell to his death," you get that brief moment of "awwwww."  But immediately — before you can even feel sad that your inept steering threw this little artistically drawn, smashy guy into the abyss — you hear, "Just kidding!" and respawn right where you were…right in the middle of what you're doing.
It's an interesting approach to character death. No reloading of old saves (it's on a console-style autosave system) and not really even any thinking of how you could do it differently next time. In a strange way, it's like a single-player zerging tactic: die, respawn in place, and continue.
I don't know what to make of this kind of death mechanic in my game. It's not an MMO, so I don't need to rely on anyone else's help to get up, nor do I owe anyone else an apology for my failure. It's not the deeply branching story of a cinematic character to whom I become attached, so I don't lament his passing. It's not a failed solution to a puzzle, and so I don't have to think about how to get it right the next time.
As far as I can tell, the narrator is the crux of it. After all, he's going to keep telling the story no matter what. That's what a storyteller does. By framing Bastion in that way, it might genuinely be the most third-person game I've ever played. Players don't really get a chance to put themselves inside the head and body of the avatars they're controlling, the way we are habituated to doing. There's an odd level of detachment that somehow makes character death entirely meaningless — while also giving it sort of the aspect of a milliseconds-long, mid-season cliffhanger.
I'm not sure what I think. I'm barely even an hour into the game and that counts the section I had to play twice due to an unscheduled PC shutdown. (In related news, my next case will have a cat-blocking door or panel over the power switch.) My first hour, though, has made me feel that I care about Bastion's world very much and its player character not at all, which is an interesting and unusual combination. But I want to know what happened, and I'm going to need that narrator to tell me, so play on I shall.