Over the course of role-playing game history, there have been many different methods to decide when a character dies. However, taking D&D as an example, typically there is only one system. For anyone who has been reading my earlier posts, I tend to think that the rules should fit the game that the Dungeon Master is trying to create. For this reason, I'll go over a few different ways to handle death (this is mainly meant for newer players that didn't experience other editions and their death rules). I will avoid talking about ways you can eliminate death complete since I will be handling that in a different post. It will be mainly focused on D&D.
Dead is Dead
There is always the classic old D&D of less than 0 hit points being dead. It's simple, but it may result in a lot of character creation. From what I've seen, this has become less popular over time. It does a very good job of creating tension for death. There's not much else to say.
The current D&D 5th edition system is fine, but as you get to high levels the odds of having an instant death become less and less. This is because as your total health increases, the amount of damage you need to take past 0 to instantly die increases. If you don't like that, you can easily scale it in a different way. Care needs to be taken to avoid making lower levels deadlier if not intended. I generally find the best way is to either use the original rules (this means players don't instantly die at higher levels unless they suffer extremely large such as a fall or the breathe weapon of a powerful dragon).
If the deadliness of low level play isn't your thing, it's easy to house rule to be a little more forgiving. Just ignore the instant death rule and roll saves. I prefer to use the standard method located in the rules but hey, this works just fine too.
The overall level of the party will make a difference. A higher level party that has access to spells that revive the dead may be perfectly fine with more deadly rules from the onset, though they may needed added magic items to revive the cleric if something happens. The rules for death need to be taken into account along with the intended mood as well as the other parts of the game. Stricter death rules combined with more magic items for the party make for a completely different experience (in an undead setting where the dead and dying are almost instantly corrupted to unlife, this may help build the tension and fear).