Not a diving death experience but this comes close...
On my very first ocean dive at Catalina Island (and this was still during my OW training), I witnessed a pretty bad accident. I was already on deck, and they happened to pull an unconscious diver up. She had suffered an air embolism from shooting up from ~60fsw. I watched as they treated her...and as this going on I hear that she is not breathing nor does she have a pulse. I watched in awe as the crew continued to perform CPR on a seemingly lifeless body. Minutes later, a Coast Guard boat came to take her to the chamber on the island. She does manage to survive this ordeal, but she stayed unconscious for months after. I moved to college shortly after that, so I'm not sure what her status is now.
Witnessing this affected me a whole lot, especially since it took place on my first ocean dive. I was pretty nervous on my second trip for my checkout dives, but once I was in the water I was fine.
Did this stop me from diving? No. HOWEVER, it did teach me that I should always, ALWAYS monitor everything I'm doing. It also taught me to keep calm in emergencies, as this incident showed me what could happen if I don't use my head.
-Francis