BioShock is pretty damn scary for the first few levels. I wouldn't buy it if I was shopping for a horror game in itself, but the tense atmosphere is a huge part of the game's appeal. While I am just about the only person on the internet who adores BioShock 2, I can't call it scary like the first game simply because Rapture isn't new by the time you get to the sequel.
Alan Wake can get pretty creepy but I was never immersed in it very well.
But if you want proper horror then you've going to have to migrate to the PC for Amnesia: The Dark Descent and its less refined predecessor series Penumbra. They are the two most immersive, atmospheric horror games I've ever played. They do horror
right.
I need to check out Call of Cthulu. It apparently does a good job of keeping the player tense.
Uh, Dead Space? I mean the game isn't focused on just thongs jumping out...
I should certainly hope not!