Two come to mind, one moreso than the other:
Last mission on Driver for PlayStation. While being a WONDERFUL game (this was before GTA 3), the last mission was fucking ridiculous. You had to drive the President of the United States in something along the lines of a Lincoln Towncar across the entirety of the game's rendition of San Francisco while you have HORDES of cars trying to ram you and spin you out of control. Easy to dodge them usually, the real clencher? TIME LIMIT. The time limit was RIDICULOUS. After the 57th try at the final mission, and I considered myself good at the game, I threw my PS controller across the room and put a dent in the dry wall (controller was ok). Turned off the PSX.
Next morning, booted it up, beat it the first try. I was in some sort of "clear casting" state (WoW) and saw things before they happened or something
Most frustrating videogame moment I can think of.
In a close second: The Egg Viper boss in Sonic Adventure for Dreamcast. That boss was DAMN hard also.