Been a long time since i was a player but as a gm i enjoy putting the players who are supposed to be playing compentent people mess up by random luck. In tv shows with ensembles of competent protagonists dumb luck rarely plays a part(at least in the procedurals) so i like when it does actually. Then again, as a weak player i did not like to live in fear of the rng of the dice.. If i recall correctly.

Weird. I just always assumed this was the rule rather than the exception.

One thing I thought was really neat is the 1E DMG page 95 dungeon. One of the very first rooms PCs are likely to explore contains the key to the hidden treasure room. But it can be lost forever on a single bad die roll. What makes it work is PCs only discover it is a key if they succeed at obtaining it. If it is lost, they have no idea they will never be able to obtain the key to the treasure room. It's cool that "failure" can be determined on a random roll right at the start of the adventure without destroying the motive for undertaking the adventure.

Here's the thing, though. What is failure? At what level of abstraction are we talking? If the PCs are truly heroes, if they dust themselves off and persevere in the face of failure, all failure means is that they were in Act II rather than Act III. If they do not persevere on to Act III and ultimate victory, then it wasn't randomness that caused them to fail in the final analysis.