Resurrection mechanics in D&D suffer from trying to maintain the conceits of earlier generations of the game.
Think of any other game. If you're playing Chess and you lose, you can just set up a new game and play again. There's no punishment in your next game. No one else playing in the same room has to stop their game and wait for you to set your next game up.
If you're playing Metroid and your character dies, you can reload your last saved game. You might lose a few minutes of playtime, but you learn from what beat you and you can try again. Ditto any modern RPG, with the exception of a few 'hardcore' games.
Think of any story you read or watch. Characters generally don't die unless it's good for the story.
Why do we let D&D create bad stories?
The game should have failure states that don't end the story. You should never be able to die from things that aren't intentional by the GM or yourself. Any other failure in combat should at worst result in you being defeated but not dead.
I am in this camp.
last fantasy game i ran, between death and return i did for each character a "between arc" set of scenes in the afterlife where they did a little journey and usually met ancestors and always had a tough choice presented at the end with reasons to go back or stay (dead.) They also usually got some nugget of the future.
Additionally, i had cults that either saw those who had been brought back as "blessed by the goddess of death" and treated them as gifted or oracles as well as cults who believed this was an affront to the natural order and hunted them down to send them back "where they belong."
i even added a special ability tree they could develop based off their brush with death including some divinations and such - even speak with dead if they needed - as rituals and the like.
i actually had one character who said after the game he was partly disappointed that because his character was built for optimizing survival and he always played cautius and never died - he never got an "after death."
OBVIOUSLY, the treatment of death is a core element of a setting - whether it be death of "extras" or of "names" (NPCs of note/import/meaning) or of the stars (PCs). Decisions on how that is treated will be a foundation element of your game.
But it certainly doesn't **have to be** drastic or the game is meaningless or any sort of the usual hard core. look at how many myths and legends have a death as a key plot element - spawning a trip to the underworld or some such.
FOR GAMEPLAY, i do not need death as the price of failure for my players to see a loss as meaningful.
I tell them day one - you will not die by dice. You may die by excessive stupidity (usually repeated), by neglect (others don't try to save you) or you may die by your own intent. But you wont die by dice or by DM fiat.
That has not prevented very serious investment in characters or setting by my players or them feeling loss when it hits home and they fail at important things (or even when they pass up on important things.) More than a few times they have seen cases where they passed on "opportunities" only to see later on someone else took it and made good from it.
I find that when Gm tends to overemphasize death as "losing" with penalties and such like "you start back at first while we are all 10th" that also tends to make characters focus more or focus only on their survival as key. They become very invested in the survival and combat cuz it costs you most everything you have accomplished. Risk a years play and rewards for stealing a bauble that looks life-or-death... why would someone do that if that bauble isn't worth everything you have and will do?
My general rule for bringing in new characters is you come back at the level of the others (starting - no Xp) and your gear value is roughly half what would be expected. i don't care if that is due to death or due to preference and agreement with me on character change.
failure is IMO not to be feared, but is definitely a case where the direction of story goes not the way your character wanted. its a different story, not a punishment. For my players, that loss of influence is more than enough a bad thing for them to not want it to happen.
yeah, i rambled.