Personally, i've found that the scary things in aviation are when you do something in an aircraft that is supposed to produce a desired effect, and that doesn't happen. i.e. We train for emergencies all the time. First time I lost an engine in a twin, I was suprised and "awfully concerned" shall we say, but we did the procedure and things worked out. One thing that I can recall that has really actually scared me so far is this story:
Teaching a CFI candidate- today we're doing the spin training flight, C-152, nice clear day. Climb up to around 6500 ft, I demonstrate a couple, then have the student try a few. We discuss some ways to accidentally get in a spin, etc. and he asks if I can show something more along the lines of a developed spin, since we've only had a few rather sedate ones so far. "Sure," I say.
I start a power off entry at about 6700 feet, and we drop into a left spin. One, two, three, four turns, it's not a really developed spin, but it's close, with a decent rotation rate. I apply controls to recover and...
Hmm...i'm sitting here with Right Rudder and forward elevator and...we're still spinning to the left. Not good. Not at all. Five, six, seven turns... I have to confess, for a moment I considered that I might not make it back from this flight, the first time that's ever happened to me.
Looking back at it now, I have to grin slightly. Afterward, my student told me that he didn't know I was trying to recover right away. He said, "I didn't know anything was wrong until you yelled 'HOLY S***!' in the airplane." That, I found out, is a good way to scare your flight students.
We probably ended up doing between 3 and 6 more turns before the airplane recovered, I really don't know how many. I did notice that there was a horizon at the top of the windscreen at one point--we were going towards a flat spin. I do remember pushing forward even more on the yoke and we ended up slowly coming out of the spin at around 3300 feet. Strangest spin exit I ever felt, and I've done lots of spins. Rotation stopped with the nose about 8 degrees below the horizon (vs. +40 degrees down in a normal spin) and a slight left bank.
We never did figure out why that happened. We were within weight and balance, did all normal procedures. About the only thing that might have contributed that I can remember is that I might not have pushed the yoke far enough forward on the initial recovery attempt. I now tell students during spin training, "Get that yoke FORWARD on recovery, don't just bring it up slightly."