Worst Option "One and Done": If the players fail they never get a check again. This can stop your entire adventure from going forward. You always need to let your players try from a different angle. Maybe not picking the lock but breaking down the door is good. But remember there are times this is totally the best option. If they fail a climb roll, maybe you gave them a save, but let them fall. Think of it this way if the party is in no danger and has the option to go back to the bar and become a farmer, dont let a bad roll stop the adventure.
Badder Option "Reroll when things change": This is where you only let the players try again if something has changed. Often Game Masters will let you check if you increase your skill, which isn't great as it halts things until you level up. Other times you have a game master that lets you try again if you have come up with a better solution. This seems a good solution at first, but its not. You encourage your players to start with minimum effort and ratchet up their attempts so they are not defeated by their own dice. We ran into this situation with a gate that would not open. We eventually had a block and tackle and a horses helping us and we still failed the roll. That could have been funny having the rope snap, but we would have come back with chain, then oxen. then elephants.
Bad Option "Just Reroll": we all have seen a player continue and continue to search for a secret door. Your players will eventually say I search until I find something if you let them. Letting them reroll can take away from the sense of accomplishment and turns the game into mechanics rather than role playing.
Ok Option "Take 20": This is basically just reroll but it doesn't pull you out of the role playing. Its a good option for players that don't like thinking about their characters actions much beyond the dice.
Good Option "Reroll with bonus when things change": This is a good way to go because your players come up with better ideas from the start. If they fail they are encouraged to think about it and try again. It can also be the start of other scenes. My cleric thought about going to the library with a rubbing of the runes from the tower to get an idea of their source. It could have been another skill roll with a bonus or it could have been a little scene where my cleric talked to an archivist.
Great Option "Side adventure": This is where your attempts take you down another side quest. Perhaps the archivist said "Yes I know those runes and brought out a man with the same cut into his back. "He is the tomb of Tsaggqua, he has not spoken since we found him" seeing your rubbing the man starts screaming. That is creating a new quest based off the work you did just to solve a problem.