Strangely enough, I feel Borderlands actually pulled off a fairly balanced blend of mechanics to make death a real nerve-wracking problem without making it unduly frustrating. Save points were easy to identify, but relatively few and far between. Dying brings you back to the safe zone, but costs you 7% of your total funds. However, even if you get taken down, be it by a swarm attack or a cheap spawn-from-behind-you enemy, you're given a chance to turn it around by killing an opponent, any opponent, while in your last stand. While ammo is cheap, gear gets expensive, so you have to hold on to the resources you've got.
I think the only change they probably would have needed to make would have been to cause death to also cost the player a single piece of gear in their backpack--because weapons couldn't be stored in safe areas like in Fallout (without a DLC purchase anyway), the risk of losing a useful mod or powerful gun that you know you might need brings new, genuine anxiety to the player, but as noted above, even being downed is survivable, so it isn't an automatic death sentence if you're down to your last hit points. In so many words, the threat of losing some but not all of your stuff because of something you could have avoided can be a good sell for fearing death in a game without becoming frustration/savescumming bait as with roguelikes.