Tabletop roleplaying games are generally about shared storytelling, and stories are generally pretty boring if the story ends because the protagonists don't find the clue, don't get past the locked door, or don't survive a battle. Failure needs to continue the story by leading to something else interesting, being only a partial failure, or being success at a cost.
I will detail some of the concepts and approaches used by different systems, including several open source alternative roleplaying games, that can be incorporated into your game, even if the specific mechanics are not.
A related concept is the treatment of character death, which is usually the ultimate failure.
Some systems, like early Dungeons & Dragons, or revival games like Dungeon Crawl Classics, treat the adventuring group more like a wargaming troop, where when one character dies the next steps up to continue as the protagonist. Other modern systems have more explicit success at a cost mechanics or alternative ways to handle death.