My scariest time have been when some one else had a problem. In a few cases it was students, either my own or those of another instructor but I was close enough to get involved. A few times I was assisting and it was my job. Once, as a DM candidate, I left my wife (who was doing fine but otherwise engaged watching some tiny creature) on a bridge span in the gulf to go after a student who was having buoyancy control problems and drifiting out to sea. After getting the student squared away and back with the instructor, I didn't have a ton of air left and had to go back down to look for my wife. As luck would have it, she was fine and met me right near the line. There was plenty of gas but if she hadn't have been right there I would have been stuck between a rock and a hard place.
Then another student of the the same instructor sunk out of control and freaked on her first post cert dive. The instructor who was with us (sort of) was above with ear trouble and the DM who was with us was a mile ahead "leading" leaving me (the DM candidate) to do the best I could. Every one lived but I was scared as she beat the crap out of me, refused to take a reg and just faught to ascend and screamed for all she was worth. I was scared again later at lunch when they all jumped on me because I handled her from in front rather than moving around behind her...never mind that the whole thing wouldn't have happened if she was taught the basics before being certified, had her gear put together right and/or one of the responsible folks were a half way decent buddy. I was scared during that conversation because I thought I was going to strangle somebody...or all of them. Well, hey, that shop definately taught me rescue skills because they kept trying to kill students and leavbing me to take care of things!
There have been dives that didn't go right. Once I became ill on a dive when I was a ways back in a cave and a few other times there were just things going on...line trouble, challenging passages, miscommunications or just people watching that I didn't want to be to embarased in front of. I like to do well but I don't always. In those instances, though I had dark thoughts in the back of my mind, they were kept at bay and I did as I was trained to do, sorted the important from the unimportant and prioritized sufficiently for a desireable outcome. I'd catagorize those instances as being under pressure rather than scared.
I love being underwater and some of the best times of my life are connected with diving. Some of the worst times of my life were also underwater. Note, though that, most if not all the bad times would have been avoided if I had learned, the right way from the right people in the first place.