Minor leaks at the attachment point between the valve and cylinder are common but are not sudden or catastrophic. They can be identified early in the dive. Otherwise, here are some of the actual reports I've seen of failures in the water:
1) O-ring failure at the sealing surface between the tank valve and the regulator. Not uncommon. This is a problem particular to yoke valves, never heard of it happening with DIN. Usually happens at the very beginning of a dive, but DumpsterDiver reported an incident of this type at depth. [ETA: crossposted, this is the incident DumpsterDiver reports upthread]
2) Clogged dip tube. I've only seen one report of this. It was unclear to me how the tank accumulated enough particulate contaminants to pose a problem.
3) O-ring failure between the tank valve and cylinder neck. Cause unclear in the reports I've seen although I have trouble imagining how it could happen without the wrong parts or insufficient assembly torque.
4) Burst disk. Can fail as a result of a history of overfilling the tanks, or as a result of excessive assembly torque. I've seen one report.
5) Valve stem seal failure. Not uncommon on older valves. Some shops replace all the valve O-rings at the annual tank inspection, which I think is prudent.