Like so many previous XP's I'm finding myself in almost total agreement with what is being said. Messing things up can be brilliant. The moments like in Just Cause 2 when I developed a really stylish plan, where in my head a scenario plays out where I stick some bombs to a car, speed towards some solders, leap out, detonate the car-missile just as it hits the soldiers, picking off the strays with my pistols from the air before landing perfectly on nearby roof. However when I actually attempt this to find what actually happens is that in all the excitement I detonate the car about 10 feet before where I should have, missing everyone then face planting onto the wall of a building only to be turned into a bullet sandwich is the sort of thing I find so memorable.
However if there's one thing I will say, it's that I really don't mind the whole die --> respawn from nearest save method, purely because I'll always know I failed. What I do mind is games where you have plan A, then the moment that messes up you die. I much prefer having plan A, then when you mess it up you can still have a chance with plan B, and depending how skilled you are plan C.
The best example I can think of this is Skyrim. When I'm being an assassin (the best build) there is always an added challenge in that you can't really afford to be seen. At the start of the game I have 100 Health, 60 levels later I might only have 150 Health, so a high level Draugr can 2 or 3 hit kill me, so I have to desperately struggle through. What I like is that the struggle will never be as rewarding as doing it properly, yet it's tense and can be done. If I enter a room with 3 Draugr Scourges and 2 Deathlords I get a tremendous sense of satisfaction when I clear the room without detection. If I am seen, sure I can get all the enemies into a nice little chain then Fus Ro Dah them all into a corner and fire arrows at the body pile until everything stops moving but it'll never be as rewarding, so I refine my tactics and try harder next time.
So like everyone else I enjoy a game which doesn't punish you for cocking up when trying something different. Incidentally, it's why I'm not really a fan of most stealth games. Even if I have multiple methods of completing a mission like in Hitman: Blood Money it always comes down to how perfectly I can plan my route of attack, how perfectly I know the area, and how long I'm prepared to wait. I can see full well why people would enjoy it, but if I have to spend 20 minutes surveying an area, tracking enemies etc. then another 40 initiating my plan I like to have a functional but unrewarding failsafe. Call me a rubbish gamer all you want, but if I'm 30 minutes into my hour long mission when a mis-timed break from one cover to a next results in the whole building I'm in being alerted to my presence or not waiting an arbitrary 20 minutes for a guard to stand in the position I want him in results in my instantly failing a mission then I just get bored and annoyed with a game.
So basically what I'm saying is I can deal with a game killing me and knocking my progress back 15 or 20 minutes as long as I have a chance to make amends for a mistake and can pull an unlikely victory from the jaws of defeat. I don't like a game which knocks my progress back by an equal amount of time by an equal amount of time where a single mistake leaves me dead.
Moments like being on Arkham Asylum with a room full of 6 or so armed thugs only to be shot down to a slither of health are made all the better when you can swing up to the gargoyles, have your heart pounding and up your game so you take out every thug knowing full well 1 shot will be enough for a kill are infinitely better and more rewarding then sticking your head up in the wrong nanosecond and having it blown off by a sniper 2 blocks away on any first person shooter, only to be sent back 20 minutes and then knowing that perhaps next time you should look for a sniper you had no way of knowing existed.