I don't think I've ever had anything fail on me diving, but I've been with people who have run out of air (my buddy was constantly using all his up), so having to breathe off someone else's tank isn't uncommon.
As I mentioned in my sibling comment, I did have a scary time on the Blue Hole. I think my other most nervous dives were:
- Pacific dive in Costa Rica in rough seas and surge. We suddenly had visibility drop to near zero when we hit the outflow current of a river. Definitely a lesson in how quick conditions can change.
- Cavern diving in a cenote in Mexico. Nothing weird happened, but we went kinda far in, and I get nervous in overhead environments.