Personally I think the reason that water levels in a lot of games are so frustrating is because you have the extra worry about running out of oxygen and you either can't attack enemies or you're very limited in what attacks you can use.
Some of my least favorite levels ever are:
Rusty Bucket Bay in Banjo Kazooie. Surprise surprise it's a water level. I used to hate it level because I could never get the jiggy behind the ship's propellers (until I played the XBLA version last year) and it used to bug the crap out of me. That and the whole level just looked dilapidated and shitty, giving it a miserable atmosphere. I know that's what it's supposed to look like and you wouldn't expect any different from a dirty ship in polluted water but I always found it a really stark contrast from the rest of the game.
Chemical Plant Zone in Sonic 2. I always used to drown just before the boss when the water level rises. And the drowning music in Sonic games used to scare the shit out of me. I heard that music A LOT of times at this part of the game.
High Security Compound on Tomb Raider 3. I fucking hate levels where you lose all your weapons, but this one takes it one step further and robs all your medikits as well. God help you if you chose the Nevada levels last because you lose everything you've been stockpiling throughout the whole game.