Yeah, Fail Forward design from stuff like Fate and PbtA is the optimal solution to “failing rolls sucks,” but requires a high degree of responsiveness – especially if you want to go with a particularly striated roll-result possibility chart (Fate accounts for Failure, Tie, Success, and Success with Style–akin to a crit). It can be tough as a GM, and takes some real training to remember not to force yourself into that position unnecessarily by calling for skill checks at times when there’s not really any compelling tension to be resolved. For instance, a competent thief should be able to just pick a standard lock if given sufficient time and privacy. If either of those factors aren’t guaranteed, the downsides/negative outcomes of poor rolls are way easier to generate on-the-spot, even if they still get some or all of the loot.
As an aside, both of those systems also use bell-curve distribution core dice mechanics. Fate has the 4 Fate dice (d6s with two sides each for -1, +1, and 0) that cluster around a flat result (so the character’s Skill rating – generally +0 to +4-- is extremely important) with very narrow tail possibilities of +4 or -4, and a typical baseline difficulty of about 2 for “not guaranteed but not crazy challenging” style tasks. PbtA just uses 2d6 with a more regular curve peaking at a 6 on the dice and usually relatively modest +0 to +3 bonuses from abilities or situational perks; the result bands are 0-6 fail, 7-9 mixed success, 10+ full success (and some advanced abilities unlock a crit-style perfect succcess on 12+). In either case, you leave off the crazy-town swinginess of d20s where, hey, your 10-year veteran soldier has an equal likelihood of beheading a red dragon with a single swing of his vorpal sword as he does of cutting off his own foot every time he makes an attack. Ugh, d20.
Something like the Crisis system from Torment, where major challenges were carefully hand-designed with lots of interactivity and cascading result-possibilities, are basically the best way to handle this in a CRPG where the “GM” can’t just pull an interesting, story-advancing failure-result out of their ass, since said GM is an unintelligent machine. But they also require a ton more work than just “chest need check DC 20 hurrdurr,” and hey, game development’s expensive and time-consuming.