A question for 5e DMs out there (and, I suppose, players of 5e DMs). When a PC fails at something, what happens in your game?
Whatever consequences would or could logically follow from that failed attempt...that don't grind the game to a halt...unless the only logical consequences would grind the game to a halt. Priority one: logical consequences. Priority two: not grinding the game to a halt. If following priority one violates priority two, so be it.
There's also fail forward and success at cost. Failure doesn't have to stop things dead. You can, and should, use that as an opportunity to add consequences and complications to the fiction. One of the worst things you can do is have nothing happen after a roll. If you're rolling dice, something should happen either way.
Think of ability and skill checks as saves. You're saving vs consequences. You succeed on the roll, you avoid negative consequences, i.e. the lock is picked and doesn't make noise and you don't get spotted and you don't break a lock pick. You fail on the roll, you suffer one of those consequences, i.e. the lock is maybe picked (not picking the lock could be a consequence), and/or you make noise or you get spotted or you break a lock pick. Generally only one consequence on a failed roll, two on a fumble. But it all depends on the fiction and what would logically follow. If the thief doesn't use lock picks to pick the lock, then there's no chance that they'll break a pick in the lock.
For example: the rogue fails to pick the lock.
Anything that would or could logically occur. Fail and the lock is still locked, make too much noise, break a pick, spotted by guards, alert whoever's on the other side of the door, etc.
The bard fails to seduce the queen (or the barkeep, or whomever).
The full range of human (and humanoid) responses to someone trying to seduce you. Social skills aren't mind control. Some NPCs are married, some asexual, some bi, some straight, some gay, some lesbian, some only into certain body types, some into certain species, etc. Your PC's natural 20 doesn't matter if the person they're seducing is simply not attracted the PC's type. In those cases, there shouldn't even be a roll. If there's no chance, don't roll. If there's a possibility, roll.
This is why it's a good idea to roll for the NPC, too. As a gauge for how they're doing and where they're at in the moment. You can't know that info for every random NPC the PCs interact with, so leave it up to the dice. Make it an opposed roll and compare the numbers. The lower the PC's roll in comparison, the worse the response. The queen might think the bard is joking and laugh or she might throw him in prison for impertinence. The barmaid might slap them across the face because that's the 50th pass she's had to deal with in the last hour.
The party fails to find a path through the mountains.
They have to find another way around, the trip takes twice as long, etc.
The party uses up a lot of resources fighting cultists, and decides to take a long rest instead of rushing to stop the evil summoning.
Then the evil is summoned and whatever should happen as a logical consequence of that happens. Will the evil destroy your home village unless you rush to stop it? Then you just sacrificed your home village by taking a long rest instead of rushing to stop the summoning ritual. It's not the referee's job to protect the PCs from the players' choices. If the player chooses to stand in the lava, then they take the damage. If the player chooses to jump off a 100ft tower, then they take the damage. Etc.
Whatever logically follows from the fiction.
If there are no stakes, why have them roll? If nothing is at stake, there shouldn't be any rolling. Simple failure ("no you don't do that, now what?") is pointless and boring. If that's all that happens on a failed roll, don't have them roll. Something should always happen if the dice are involved. As above, they're saving vs consequences.
Do you tell the party the stakes?
The fiction should be clear enough that the stakes are obvious in context. If they're sneaking around someone's castle, they should know that guards are present and might find them, servants are roaming the halls and might see them, the lord and lady of the castle might hear of the sneaks and be angry, etc.
If there's no way for the characters to know the potential consequences, then the players don't get to know either. The info the players have should match the info the characters have as much as possible. It avoids metagaming.
What happens when a player wants to try again?
Let them. The circumstances of the fiction should have changed with the first failed attempt, so the character trying again has to deal with the new situation. You failed to pick the lock and made a lot of noise, now you can hear the guards coming down the hall...what do you do? Try to pick the lock again. Okay, you manage to pick the lock just as the guards round the corner and they see you.
If possible, I'd especially like examples based on actual play. I'm not looking for general advice, so much as examples of how people have actually dealt with this in play.
Everything I've said above has come from examples in play.
The PCs are sneaking into a castle and fail a check, they make noise, are spotted, alert the servants, the lord of the castle hears of their treachery, etc. The barmaid slaps the seducer across the face. The queen throws the seducer in prison. Broken picks. Lost time. Destroyed villages. Etc.