as long as i can save as many times as i want, i don't care how many times i die. my first games were the sierra games, and i learned to save early and save often. what irks me, though, is when you can only save at certain points (more likely to happen in RPGs or action games) or when the game has a very limited number of save slots (X-Files, which I'm playing right now, only has THREE!)
i'm more bothered by dead ends than dying, and these don't happen very often anymore. i think the developers know we hate them!
i don't really care if a game brings me back to the point right before i died... in some cases i think it would be more fulfilling if it didn't, so i had to figure out what i'd done wrong and where to restore in order to fix the problem.
oh, and i think a well-integrated "game over" screen can enhance the story. i can think of a few games (Final Fantasy 8, GK2, Shadow of Destiny) where the game over screen gave me chills. a lot of the old sierra games also had humorous death messages, and some even gave hints on how to avoid the death the next time.
ps GK3 and the pendulum did drive me batty, though.