I'm afraid I don't play a lot of horror games, and the few I have played failed to get much of a reaction out of me. Still, I can think of an example or two I'm sure.
I suppose Silent Hill: Homecoming pulled it off to a degree. Despite some genuinely disturbing imagery it wasn't a very scary game, but it was pretty tense because A) it's really hard, b) save points are few and far between and c) while the game ramps up the difficulty later by facing you off against tougher enemies in larger quantities, you yourself get very little in the way of improved equipment. The fire axe gets the job done eventually but still isn't particularly effective, and although you do pick up a few guns, finding any actual ammo is like finding a needle in a haystack.
Oh, and note that there are only two difficulties: Normal and Hard. I was playing on Normal.