I feel like a lot of people are approaching this from the DM side. That's backwards - the players are the ones whom will most feel the death of a character, you need to approach this from your feelings as a player.
I like this angle; the player perspective is central here.
In a game with the base assumptions of D&D - you are putting your life and limb on the line to do heroic things - as a player I find it off-putting if death is not on the table.
This makes sense. In some games, I completely agree. I quibble with one minor element of your characterization below.
Why should I both to play intelligently if I know I have plot armor? Where's the thrill of victory if my character can't lose? . . . But overcoming challenges, be it a puzzle, a social encounter, or a combat, is fun. Having no fear of death so we spend 45 minutes rolling lots of dice to a preordained end result is not.
In the long-term campaigns that I've run (AD&D, D&D2-5, GURPS, and DFRPG) where character death was largely off the table, the characters could definitely lose. Results were never preordained. The characters' goals were regularly thwarted, sometimes in permanent ways. Of course, new goals would emerge, but I couldn't predict what these would be.
This has been true as a player, too. The most emotional scene I recall from any game I've played in was in a scifi game where the villain totally outsmarted us and blew up a ship full of refugees that we'd been trying to ferry to safety. Over the course of many prior adventures, we had gotten to know the leaders of the refugees and had planned everything out on their behalf. It all came to a head in a climactic session, and we blew it. Our characters were fine, not even in much danger, but every adult in the room was crying. That was an amazing session.
An element that stands out from that experience, looking back, is that total party failure is so much more dramatically satisfying as a player than TPK. After a TPK, usually everybody is just peeved about the whole thing. Losing an individual character in a great death scene can be satisfying, but usually the full group isn't sharing the grief in the same way. One player lost his or her character; the others are sad on their behalf. But they're also eager to press on. When the whole party fails together, losing something they all care about, there's this profound moment when everyone is grieving together.
And then, hopefully, plotting their sweet, sweet revenge.
The DM encouraging a discussion pre-game / session zero is the best way.
QFT.