LootHunter: In Rock'n'Roll Racing if you lose Championship on a planet, you'll have to try it again. Though even if you lose, you still have all upgrades that you bought. So eventually, you'll able to upgrade the car so you'll probably win. Just not with the first try. Does that count?
I wish more games with growth systems (of some form) would be like that.
This way, you could still have your grueling dungeons with lots of dangers and treasure, die towards the end of it because you ran out of resources (or failed to predict the enemy's turn, or got really unlucky), and still feel like you've made some progress.
One thing that I think RPGs lost after the early days is the idea that dungeons back then weren't all meant to be cleared in one go, and allowing the player to keep everything gained on the failed attempt would allow this tension without making players quit if they die near the end.
(Dragon Quest games come pretty close; you do lose half your gold. Final Fantasy games (at least the ones I've played) just dump you back to the title screen except for FF6 that lets you keep XP and levels (but not esper stat boosts gained from those level ups, but esper stat boosts in that game is a terrible mechanic).
kharille: I think I'd abuse reloading in fallout 2. If you lose a companion there are spares, but they're so unique you'd try to keep them alive. And I think I do that in Baldurs Gate too.
Baldur's Gate 2 makes it very easy to revive characters, but I still tend to reload when that happens due to the fact that such characters drop all their items on death, so you have to re-equip the character after reviving them, which is a real pain.
Some games, like many SaGa games (starting with SaGa 2), Final Fantasy Mystic Quest, and Paladin's Quest and its sequel, make death a temporary ailment; after each battle, dead characters come back to life with 1 HP. (Rarely, there's even games with free revive effects that can be used any time; I've seen this twice, in Dragon Quest 5 (though you can't get that item until the last town, and it's expensive), and in Phantasy Star 3.)
I could also mention that games could make the revive item cheap, as Final Fantasy 4 did. Note that, with revive mechanics, you sometimes have a situation where it's easy to restore a dead character to full HP than a living character. (This last situation actually comes up in tabletop D&D; in 3.5, Heal and Mass Heal now only restore finite amount of HP, but True Resurrection still restores all HP, and if using the Epic Level Handbook (whose rules are in the d20 3.5 SRD, so I'd consider them valid in this edition), there's a feat to remove material components from spells; hence, you actually get to the point where it's fastest to kill and then revive a high HP character than to just heal them unless you're willing to use an epic spell (which some DM's might not allow, or might not allow creating).)