Don't get me wrong. I will allow characters to die. But there are good deaths and bad deaths. When a character is played out to the bitter end - thats a good death.
One of my favourite deaths was in a Deadlands game. I played a gunslinger, the silent noble type. He was in a hand to hand melee. Another member of the party, a flamethrower weilding lunatic burn them all and let god sort them out Priest decided to flame the whole group of us. His defence, those of pure heart shall be spared the fires of hell, didn't cut it my character thought. So he silently swore that there would be a showdown when the adventure was done.
So emerging at last, having defeated whatever it was we were fighting, my character called his out. OOC the rest of the group were rather surprised but once they learnt of the promise the gunfighter had made to himself, everyone jumped back IC and we got on with it.
The gunfighter put everthing into it, and he was fast. He emptied the pistol, hitting the Priest 5 times before the Priest had even had a chance to raise his weapon. But the Priest survived, and then pulled the trigger. Triple double whammy damage and ... well, the Priest's player always did have a spooky luck.
All in all, everyone agreed, a smashing way to finish a great game. And the gunfighter lives on eternally.
Coming back to find your character dead is not a good death.