There's a sweet spot for fear of death. Sure, if PCs can't die, there's probably no fear of death (but this is largely a strawman argument). On the other hand, if player death is a regular occurrence you end up with apathy and frustration, not fear. Maybe paranoia, which some people conflate with fear, but in my experience (unlike fear) excessive paranoia bogs the game down and makes it a boring experience, with the players checking every square of the dungeon for traps.
IME, to kindle fear of death in players, the potential for death should be present without death becoming omnipresent. Fear of character death is mostly about attachment. If characters rarely last more than three sessions, for example, it's the rare player who will grow attached to those characters. Players don't typically invest heavily in disposable characters. If the character is seen as disposable, then character death is merely an inconvenience, if that. IMO, the sweet spot is to have at least one character death per campaign, with the possibility of a few more. The players invest and grow attached to their characters, and therefore have legitimate stakes in that characters survival. Since the character is not viewed as disposable, the specter of death is real (despite being rare).