But the threat of character death can be a very interesting, recurring point in games like D&D that feature lethal combat as a common challenge resolution.
Both in and of itself, in the tension that risk creates is a spice for combat - which is a length mechanical aspect of the game when it occurs. Add in the simple fact that many/most combats, especially in the published modules, aren't about anything except overcoming in order to move forward in the plot. Leaving either it's foreordained you'll win, or having a DM willing and experienced enough to think of suitable alternatives every time.
One way of reading this is
D&D is often played essentially as a railroad through a plot, with the action in play consisting in
combats where the stakes are 'do we beat it or do we suffer some sort of loss in the form of PC death?'
I think that's probably true, but maybe a bit candid for the taste of some!
Many games, with D&D being a big example, tend to not have mechanical consequences other than loss of HP, which can lead to PC death. There are some minor exceptions to this across editions that allow for some kind of consequence that lingers, but they’re pretty few and far between. I think this is a big part of why many GMs lean so heavily on PC death.
Of course there can always be narrative consequences…failing to clear the Caves of Chaos or failing to learn what happened to the Carlyle Expedition or what have you. But every game can have those.
So with PC death, what’s the consequence for the player? They lose that PC, yes….but they just replace it with another. Perhaps the lost PC was particularly enjoyable to them or what have you, but the game goes on, and the player continues to play.
This is why I said that death isn’t always the most meaningful consequence. There could be (and in many games are) ways forward from that point that allow the player to continue with that PC but which result in a more meaningful change in the game than simply swapping out a PC.
I think that
mechanical consequences does not contrast with
narrative consequences very strongly in the context of PC death, at least without adding more: after all,
that the PC died is an event in the fiction just as much as it is a (potential) mechanical change to the player's game position.
An example of "adding more" is Epic Tier 4e, where often the dead PC comes back to life via a special ability, and so the PC death is primarily a mechanical event that drains a resource, much like spending a healing surge, with its contribution to narrative being tension and pacing and that's it,
unless the table does the work of feeding the death into the narrative in some fashion. (At my table, sometimes we did and sometimes we didn't.)
If the consequence of PC death, for the player, is that they have to play a new PC, is that a mechanical hit or a narrative hit? That will depend on the details of the game and (often) table practices. To be perfectly honest, I don't think D&D really has a coherent conception of what the game effect of a PC dying is supposed to be. It leaves it entirely up to the players at a given table to decide. Different approaches here will have different effects on the mechanic-narrative correlation: if the player gets to bring in a new PC who joins the current party on "the quest" but they are down a level or an item or something, then that is no narrative consequence but the player's position takes an immediate mechanical hit.
On the other hand, in a Classic Traveller game it might be possible to bring in a new PC who is
better than the dead one (due to random rolls) and who is just as narratively integrated (depending on how the game is being approached at the table). This is less likely in D&D but not impossible if PCs are low level and being generated via random stat and starting money rolls.
The whole thing is weird and badly underexplained in mainstream PCs.