Oofta
Legend
That's the point though. The OP is discussing how failure can impact the game, but making it so certain situations basically slam things to a halt rather than making them harder or forcing alternate approaches
Also, you're seeming to confuse mistakes and rolling low, given you refer to players not PCs (and indeed the sentiment only works with PCs). It's not a "mistake" if you roll to search a room with Investigate, and despite having Advantage from someone helping you or w/e, the dice roll low. Depending on the DM if you carefully describe the search you might auto-succeed anyway, but this is the sort of thing that tended to be an issue.
In my experience it's mostly an issue in pre-written adventures. As @jgsugden says, if there's even a little bit of a sandbox and the players are aware it exists, it's usually possible for them to work around it. In at least 50% of the adventures I've written which were a bit more railroad-y (normally I got for "scenarios" which are what @Oofta is describing I think, i.e. stuff is happening, do you want to get involved? Maybe an NPC asks you to, but you don't have to, and could ignore them and get involved in a different way), the players surprised the hell out of me with an approach I totally hadn't considered and which was really good.
As @prabe says too this can be a learned behaviour. This is one of the reasons I look askance at a lot of Adventure Path-type stuff, and particularly this idea that these sort of heavily-worked pre-gen Adventure Path-type adventures are a "good way for DMs to learn", because I actually feel like they push people into a particular way of approaching adventures which is quite limited. I have met players before who were befuddled by the idea of having to do their own thing. Indeed, full disclosure, I've kind of been one - not in D&D, but in Shadowrun, at one point in a very rail-road-y adventure the writers expect the PCs to go off and do research and talk to contacts and so on, and there's no way forward without them guessing this and having the appropriate skills/contacts to do so. Me and the other players were mystified by this. But it was largely learned because the entire SR campaign up to then had been basically "doing what we were told".
But I think the main thing is that you can make failed rolls and stuff interesting by designing for that, rather than just making them punishing. Personally I think it's generally better, if you want "punishing consequences" or the like to be looking more at that happening because of fundamentally terrible plans, rather than mere bad rolling. Now, there are some plans that pivot on a few rolls, and maybe some of those are fundamentally terrible plans, but most of what the OP is referring to isn't that.
In my campaigns the world is always changing and evolving. Sometimes for the better, sometimes for the worse. Most of the changes are pretty insignificant, but I have had city-states fall because the adventurers failed. In other cases, PCs turned a potential big bad (and potential long term threat) into an ally. In other cases they succeeded beyond my expectations and a region was left in a far more stable state than I had been anticipating affecting my general concepts for potential future campaign arcs.
It goes both ways. Sometimes the PCs fail and bad things happen. But unless it's a TPK, it's not the end of the campaign it's just opening up different obstacles. In other cases failure may be a setback but not a permanent one. Especially if running a mystery scenario, if they miss a clue it's not going to be the last clue they will ever get. It just means that if they had picked up on the clue they would have solved the mystery more quickly, perhaps they could have prevented some damage or even a death. Maybe their reward will be slightly less if they fail.
In other words, there will never be a secret door they must find. If they find the secret door they can bypass a fight or be in position to set up an ambush. If they fail to stop the dragon, the dragon burns their home base to ash and their favorite Num Num bakery is gone, the owner fled to parts unknown. That just sets up another path, another story arc where they have to face down a more powerful foe with a rallying cry of "Remember Num Num!"