I've always had mixed feelings about that for story-driven games. There's a part of me that wants a more plausible approach (I.E. my protagonist should not be invincible, or shouldn't have the best of luck for everything he does, or tries to do), one that could even cause "death". But another part of me knows that it would be done mostly just for the sake of doing it.
I mean, ultimately, I'd find that to be superfluous, considering how you'll of course just reload after that and simply avoid the choice that caused your "death". Indeed, that usually means that the protagonist is a force of nature, will never really die and can overcome any and all challenges the universe can throw at him/her. You might even be enough of a bad ass to drink through your helmet. Now, of course, it might add some... let's say... variety to how things can branch out during a given situation, or mission. You might want to see your character die just "for the heck of it", to satisfy your curiosity, or you'd simply genuinely take a bad decision on your first time doing that mission and just die as a result, or get killed, or fall off a cliff and break your neck, and then have a good laugh at it. But in the end, we'll always just reload, then avoid the "death choice" and move on. In terms of development time I'm sure that doing something like that would cost more (in time and money) than it'd be worth doing, I suppose unless it'd be done maybe just one or two times throughout the entire game.
Meh, still not sure, but really I think it's just for the heck of it, rather than being something relevant to the story that's being told. I... might be wrong. I mean there's that other half of me who actually want to see SOME of that happening, with moderation.