The Object of the Question
In my almost two decades of RPGing, I've usually treated the probability of character death as a result of random chance in a matter-of-fact manner: as a thing that is, without strong feelings in favour or against it. If there's a chance of my character dying on a session, I'll play that game; at worst, if the chance is too high and I'm attached to my PC, I'll take more measures to reduce it. Likewise, if the system makes death non-random or outright impossible, I'll play that game too.
Random death, for the purposes of this question, is used to mean death that occurs, whether directly or indirectly, as a result of random number generators like dice, coin flips, cards etc.
An example of a directly random death would be making a Health roll to resist a heart attack from an assassin's exotic poison, or having an enemy's bullet roll more damage than one has HP (in a system where getting HP below a certain number results in death). An example of an indirectly random death would be failing a roll to sneak past an extremely powerful enemy, which results in getting hit into a combat whose inevitable outcome is death.
A purely plot-driven death, by contrast, can result from something like refusing to evacuate from a ship that's about to blow up: the outcome is quite clear without needing to consult the dice at all (and often without needing to consult the system at all either, at least outside rules-literalist gaming groups).
A risk of a purely plot-driven death can be effectively very similar to a random one if it's based on a non-informed choice - a simplistic (but hopefully not used in real games) example would be choosing between two paths, one of which has an unavoidable and undetectable deadly trap, while another doesn't, without knowing which is which.
Attitudes and Reasons
But of course many (most?) people have stronger opinions on this matter, in one direction or another. So far I've seen three broad categories of non-apathetic players when it comes to this topic:
1) The Pro-Safety Crowd
First, there are people who are unwilling to play games with PC death (randomised or any). Even if I don't share the sentiment, I can understand the appeal of their argument, and understand what desires such game design is meant to cater to: preventing leaving dead end threads; preventing a story from stalling with the loss of a protagonist; allowing 'safer' attachment to a PC; making the price of failure something that isn't as final; focusing on the aftermath of a loss etc.
These seem like clearly stated goals that can be pursued in a design of a game. Even in a system that does feature a chance of death, a campaign can be designed to pursue those goals at least in part.
2) The Pro-Death Crowd
But there's the second group: people who feel that there must be a risk of character death in a roleplaying game. But when I asked them what makes it so, I had a harder time understanding their reasoning.
There's the appeal to realism, but too often I've seen it voiced by people who don't pursue games with realism-oriented design in general, so I'm thinking that's not a dominant reason for this preference. There's the appeal to the thrill of having to start over based on chance, but asking further probing questions (such as how high should the risk be, on average, per session) tends to give answers that make me sceptical of this being the dominant reasoning either.
3) The Pro-Threat Crowd
There's apparently a third group: people who like to think that they're facing insurmountable odds, but don't really want to deal with the statistically likely outcome of truly facing such odds. I'm listing this group here mostly for to avoid conflating the design goals oriented at them with the design goals meant to cater to the second group.
Back to the Heart of the Question
For the people in the second group, what are the actual appeals of having a chance of (usually random) death in a game (whether in the form default system rules, or the way optional rules, houserules and campaign structure interact to produce actual probabilities)?
Since neither pure realism nor the existence of a given range of 'mathematical' tension seem to be the true motivators for group 2's preferences, can perhaps those true motivators be found and used more directly in design of systems (or campaigns)?
Is there perhaps a reasoning or appeal that I'm simply missing for whatever reason?
Ideally, I'm interested less in individual opinion, and more in things that are widespread among players of the mentioned type.