I've played a lot of characters (and GMed for a lot of players...) where it would have been much more fun for the character to die horribly than to continue living on and on in a world they refused to adapt to, and that they would never miraculously find their place to thrive in, for that same reason. I find it pretty satisfying.
In one RP, the GM gave my character incredible superpowers. She could alter reality, teleport, etc., but at one point the pseudo-deity that gave her these powers made it so a little voice said 'I told you so' in her ear, every time she used them. So, after a mishap landed her in a deadly swamp, she stumbled to her death instead of using her magic to escape, to neutralize the poison, or do anything else. Hearing that dude again might have motivated her to self-destruct, anyway! The GM felt bad and provided a really convoluted way out involving time travel, though. That wasn't nearly as satisfying.
Likewise, I've given PCs second chances, sometimes repeatedly, when they do things like trigger a lethal trap they were already warned about, or try to argue with the god of truth and justice in hopes of converting him to moral relativism when he asks their purpose in life is. It's a waste of time, it seems. When a player was so willing to throw away their first chance, there doesn't seem to be any reason to believe they intend to ever do any better. Stupid mistakes are a good ending, so long as the player knows they were taking a big risk.
I've seen a lot of unsatisfying character deaths, too. Sometimes it's random, just the dice, but it's even worse when it's because the GM threw in some overpowered tech, and no one realized how catastrophic its effects would be if it hit someone, until that actually happened. That last bit happens a lot, especially in sci-fi games, where people tend to assume a rifle's a rifle until they realize it's capable of vaporizing a cubic meter of flesh.
The worst is when a character dies due to favouritism. Horror movie-style off-screen teleportation seems to be a popular means for this to happen, since they get to claim events followed a logical progression, even when they were hidden, and both threads of RP were biased to generate the favoured result. PCs getting killed by sniper fire, or in a 'surprise round', are very likely to have this effect. The GM tends to explain what the situation was only when it's too late, before falsely falling back on 'you should have known!'
So, there's a balance to be struck. It's awful for a character to keep surviving while they dig themselves ever deeper, and it's not good for a character to die from an unheralded threat, unless they're cooperating with the GM to create some dramatic impact. It's great to follow through and kill a character who defies all reason, takes a big gamble, and turns out not to have such amazing luck, after all. Otherwise, it gets painful to watch!