Damn, you all already posted a lot of cool examples.
Btw, I'm skipping this as someone is adding spoilers..

Mention of a lava death takes me back to Tomb Raider again for the 'Temple of Midas' death when you turn to gold. It's pretty ugly in both TR 1 and Anniversary. I love it because as it's happening Lara is watching it happen with this terrified look on her face.

Another visitor. Stay a while. STAY FOREVER!

Lol doesn't she have the exact same expression on her face the entire TR1? Oh wait, anniversary? Yeah, that really was a good one :) Great game too. Perhaps even better than Legend.

Having your whole party crushed in Wizardry 8, while climbing the rope in the Sea Caves, because of the buggy engine. (This is why you should never play on the Ironman.)

It's kind of weird how their screams of agony fills me with delight. Must be the intense nostalgia trip I get each time :P

TR is my favorite series ever yet I've had Underworld installed for 6 months and haven't played it yet. :P I'm really depressed now.

The tentacle-mouth-thingy in Another World. Also, the impaling one, which was way more graphic than I expected at the time.

Also the guilhotines in the original PoP, which have already been mentioned...

It's pretty cool, but I didn't enjoy it as much as Legend or Anniversary. You do get to ride a pretty sweet bike though.

At least your TR 'crime' isn't quite as bad as mine. I still haven't finished either TR3 or The Last Revelation. Well, at least not without cheating in the case of TR3. I really should have another go, but this time by starting with Nevada.
Eish, come to think of it, I haven't finished TR1 either :P Scandalous...

Chopping off the enemy's head in Barbarian, and then a goblin using it as a football.

Sorry I can't find it. In case you want to spend more time on that, it's a ship battle against Captain Crow and the death happened around the first two minutes during a quicktime event which I failed.

Spelunky constantly delivers on good deaths.

The best deaths in video games are often the ones where I get cocky and die spectacularly in the face of unsurmountable opposition.

Yes, I was playing Spelunky a few days ago and was having a pretty good game until I suddenly did something wrong and watched helplessly as my guy pingponged all over the place, fell down a pit, and got skewered on some spikes. I had like 7 hit points when it all started *sigh*

Scout's and Engineer's taunt kills in TF2. I like the scout's kill the most.

https://youtu.be/sUwxfU_L9m0?t=1m18s

Not like a particular character death, but it was so funny after single-handedly killing an army of Nilfgaardians to free Triss Merigold in TW2, as she's walking past all the bodies she's like, you did all this? And Geralt replies, they didn't kill themselves. XD