I have only had two bad experiences,
#1 Was with a dive boat going out of VA Beach, where we spent something like 6 hours driving (3 out to one spot, 2 to another, 1 back to the final spot) and when I finally got in I was killing myself in the current...
Lesson was to stay in shape for the type of diving you want to do, or don't do it (and be happy being a Caribbean and quarry diver :depressed
Otherwise know your limits and dive within them.
#2 Was my sisters 100th dive about 5 years ago, we were doing a easy familiar dive, about 15 minutes in I took a breath off my reg and got nothing...I got my sisters attention after she finally looked back and she came over. We buddy breathed and I asked her to check my tank to make sure it was all the way on. She went back fiddled with my tank and told me she fixed it. I took one breath from my reg gave her the ok sign, she started swimming away and boom my next breath nothing...again got her attention. Buddy breathed with her, asked her to check again and she write me a note on my slate whoops I turned your air off last time. She turned it back on and we aborted, I was stressed and that point.
Lesson on that was, for one be able to reach and adjust your own tanks valve if for some reason (roll off, stupidity of not having it on, w/e) it needs to be checked under water. And #2 that I should have taken a bunch of breaths while watching my SPG at that point to make sure I was really OK before assuming one good breath was good enough, and kept my buddy with me. #3 was you have to be able to save yourself period...we dive a lot together and know each other but in the OOA emergency it still took a while to get her to realize I needed help. If it had been an insta-buddy I would probably have been doomed...and probably better just doing an emergency accent. Unless they happened to be one of rare good insta-buddys.