I've seen dozens of castle nuts come loose, more often from riding around in vehicles and rattling around than shooting; but it can certainly happen.
However, I've rarely seen it cause any kind if failure. Typically the stock just gets loose and someone borrows my wrench to tighten it. After they've done that a few times, they stake it or use some kind of light threadlocker. When it does rarely cause a failure, the failure is always the buffer retaining pin being launched into low orbit next time the rifle is disassembled.
Most of the time it has happened has been at shooting classes; but I'll occasionally come across someone at the range with wobbly stock.