The first kind is a deep primal fear that all humans have. It taps into the "fight for your life" parts of our brain and injects the game with the raw visceral energy that comes from mortal danger. The second kind of fear is barely fear at all. It's stress, mostly. Just about any game can accomplish that much. If the threat of losing was all it took to make a game scary, then I Wanna Be The Guy would be the most heart-stopping frightfest in history.
Creating real fear requires immersion, and sending the player back to the loading screen kills that. A second ago they were afraid for their lives. Now they remember they're in their living room, it's all just a game, and the danger was never real to begin with. You can threaten them all you like but once you actually kill the character, the player will remember you're all bark and no bite because you can't really hurt them. The worst you can do is stop them from progressing in the game, which just isn't all that terrifying.
My beloved Silent Hill series really shows off this problem from time to time, as do the early Resident Evil games. The game is always disturbing and dripping with atmosphere that awakens a profound sense of dread in the player. But the game is at its least scary when it's at its most difficult. I'm always uneasy before I die, but if I hit a tricky spot and die a couple of times then I'm yanked out of the world and suddenly I'm playing a videogame again. Even after I get past the rough spot, it takes a while to settle back into the mood.
So, if the goal is to genuinely scare a player you want to make them feel like they could die at any moment. At the same time, you want to avoid killing them as much as possible. Like watching the maniac chase the cheerleader for the second time, a good storyteller can make the perceived threat very high, even when (in the back of your mind) you know the actual threat is low or none.
This is not to say there's no room for punishingly hard survival games with taut resource management. I recently modded Fallout 3 to turn it into exactly that. That sort of gameplay is a blast if you're in the mood for it, but it's not a magical shortcut to creating fear. Too often, when game designers want to scare us, they ignore the all-important "atmosphere" and "story" parts of the game and just take out half the health kits and save points.
There are a lot of "survival" games out there. Dead Space is survival action. Modded Fallout 3 is survival RPG. The recent Resident Evil games have been survival comedy. But Silent Hill is the only mainstream survival horror we have left.
That's a shame, because I think there's room for more.
And because I'm curious: What's your scariest gaming moment?