I have had few of my characters killed. The first was in an early version of Call of Cthullu. It was a stupid death determined by a GM who made us roll for every little thing we did. He made me roll to identify an empty case from a 38 Special. Because I let my friend talk me into rolling up a big dumb brick because "the party needed one", he didn't have much intelligence. Because the character had low intelligence, he was unable to read "38 SPCL" that would have been stamped on the bottom of the case.
So, when I wanted to throw a grenade, he had me make a series of rolls and arbitrarily determined it bounced back to land at my feet to explode and kill my character and the party. I refused to play with that person again.
Second time I lost a character, it was a PC I was running as an NPC in home brewed fantasy game system we call Legendary Realm. There is no resurrection or reincarnation. It was also one of the few times I actually killed a player character. The party was trying to stay a jump ahead of the Elven Army while working to save the world. The Elves arrived in the human village in an airship and the party was preparing to slip out the back way hampered by a character unconscious from illness. However, the Elves threatened to kill everyone (Elves and humans don't get along) and burn the village to the ground if the party didn't surrender. Also, the party needed something to delay the Elven Army to get away.
My original plan was that the three Orc NPCs would sacrifice themselves in an epic battle while the party made good their escape. The Orcs had sworn loyalty to the party and were well liked. However, during an argument between two of the Elven PCs, one was reminded of duty. She decided it was her duty to go meet the commanding officer of the Elven Army. My character (also Elven) warned her that if she did so, she would die. It wasn't a hint. it wasn't ambiguous. The player stated her character was going to go meet the Elven Commander and try to talk sense into him. My character sighed and said "Then, I'll go with you." The third Elf PC decided to go along. My character finally told his sweetheart how he felt about her (an Elf played by my wife), the PC Elf left her journal with the comatose PC (who was human) and the three went to delay the Elven Army.
The PC Elf met with the Elven Commander and did her best to explain what the party was doing was the best for the Elven people. The Commander was bound and determined to stop them as his orders dictated. There was a lot of political "high jingo" driving the Commanders actions. A huge scandal was in the offing, left over from the war between the Elves and the Dwarves. Finally, the Commander had enough and attacked. There was an EPIC battle. All three characters took damage beyond their life levels and kept fighting, cutting a path through the Elven troops. Finally, the NPC Elf fell. My character was close to being done, as was the PC Elf. My character pulled in as much magic as he could, then pulled in twice as much more. When the PC Elf saw what he was doing, she simply nodded in agreement. When he took his last blow, he could no longer control all the power of the magic he took in himself and there was a terrible explosion, killing a large number of the Elven troopers and blowing a large crater in the village common.
All three Elves died, the Orcs lived and the party escaped. The PC was the best character the player ever had. At the end of the session, the player was elated. "That was awesome!" she said.
Later, she confided to me that after the gaming session, she broke down and cried a good long time.
Anybody can kill a character. But it takes a talented GM to bring a character so close to the brink the player begs them to just "Kill my character and get it over with!" and return to the next gaming session for more. That's what our Traveller campaign was like. No characters ever died. But there was more than a few moments we wished they had.