It's kind of interesting, but dying in a game affects me differently now that I'm older than it did when I was a kid. When I was a kid, dying seemed to inspire me to try harder, and to keep playing longer. Sure, I might get frustrated eventually and quit, but not dying was always the challenge I was fighting against. But now, if I die more than a few times in a game, I will usually quit and do something else. I might come back later, but unless the game is really, really exceptionally good it will eventually prompt me to stop playing it. I just don't have time to sink six hours of my day playing the same section of a game over and over again. Plus, I feel like I'm playing for reasons other than to just survive to the end--I try to win in a particular way, or experiment with the different options and ponder the implications, or create a particular little story within the story, etc. Dying until you win just seems overly simplistic to me nowadays.
That being said, I think it is a very hard game mechanic for designers to figure out. A game where death is entertaining is great--I still go back through my old adventure games and spend hours roaming around dying in different ways to enjoy the amusing animations and messages that accompany the many ways to die. But in some games this would undermine the atmosphere. It's a tough balance to strike between rapid reload time (making death no more than a momentary inconvenience and therefore making it have less apparent consequence) and actual punishment (death is significant and takes a bit to recover from, but you risk the players losing interest).
I would say that if it is easy to die, then generally it should be quick and easy to recover. If death is a more significant setback, then it should be harder to die. The real tough decision, however, is in horror games--the perception that it is easy to die will make the game scary, but if you actually die the tension is eliminated and from that point on it's no longer scary, because you've faced the worst the game has to offer and it's not that bad. I would say that for a horror game death should have a consequence and it should be easy to die but also easy to narrowly avoid dying--you want the player to feel fragile and vulnerable, but you want their reaction to be frantic and panic-stricken as they struggle to stay just ahead of the death nipping at their heels. If it's easy to die and hard to avoid dying without knowing what to expect (like in Resident Evil 4), the player will make their first trip into a new area a "recon" mission where they just run in and see what there is to see without trying to survive. Then, they know what to expect and can breeze through it the second time through.