I'm with you on scary games. As a youngin', I witnessed the really old, old game called Alone in the Dark... or is that the old Nickelodeon show? No, that's Are You Afraid of the Dark? Anyhow, tangents aside, I can't play horror games. Dead Space, no thanks... Resident Evil, never. Bioshock, however, was a great game. I played through it and had a grand time (though it was on easy). The story is great, there are some freaky moments, but there's nothing "scary" about the whole thing (unless you were to play on a harder mode and invested emotions into the character). There is, as another said, quite some unease throughout the entire game because everyone in Rapture is just plain insane; however, you can't be too scared by a game in which enemies will sing "Jesus loves me / this I know / for the Bible tells me so."
Go for it. I'd say it's worth the 5 bucks and just play on easy to remove the "I'm going to die" fear. Because, unless you start trying to control your character by repeatedly bashing your skull into the keyboard and hoping everything around you dies, you'll be fine.