I think that this is a fascinating question!
I would start by stipulating that Players, almost universally,
do not like dying. That's obvious, right? At best, it's an inconvenience (waiting to get resurrected), at worst, it's a disaster (losing a favorite character and starting over).
In fact, that's why you seldom see "perma-deaths" in fictional works of an ongoing nature; you don't want to lose that sense of identification you've had with a fictional character. As much as many people can discuss how awesome it was that some TV program "kept it real" by killing off a character (usually one played by an actor in a RL contract dispute), most people tend to dislike it.
Which brings us to the question of D&D. At some point, either the DM is providing the characters with "plot armor," through calibration of encounters, or not. And this gets to the bigger debate- sort of a rule utilitarianism for both the DM and Players.
Neither the DM nor the Players wants a Player death or ... a TPK. Ever.
But disallowing the possibility tends to de-stabilize the game; it is no longer a "game," and becomes more akin to wish-fulfillment (within the scope of D&D - other rules systems are, of course, different). It is all well and good to pretend that there are other consequences and stakes involved through the creation of narrative drama and what passes for the feels within the game, but unless you severely modify the game, the basic core premises of D&D (zero-to-hero, XP for killin' stuff, get rich or die tryin') make it all reward, no risk. Which ends up not being fun after a while.
Look, it's great to play Civilization, and constantly save the game every turn so I can't make a wrong decision; but that's not really playing or winning. If I need to see how awesome I am, I don't need to play D&D on easy mode, I just need to look in the mirror.
