It Was a Dark and Stormy Night (A Story About Deadlocks)
It was a dark and stormy night; a coven of database administrators was gathered at the local watering hole, trading “big fish” stories (interlaced, like a concoction of couscous sprinkled with parsley, with the occasional “How I Trashed the Database” story) and quaffing the generous libations poured by the master of the house.
Suddenly the frantic shriek of a cell phone filled the air with a discordant cacophony that seemed to rend the very heavens.
The youngest database administrator pulled out his cell phone and read the message faster than any gunslinger from the Wild West ever drew his revolver and gunned down a jackdaw sitting atop a pine. “Why are they calling me about deadlocks?” he screamed at the dark and stormy heavens.
“Don’t sit there whining while the entire database is deadlocked!” scolded a grizzled old graybeard. “Watch that database like a hawk! When I was a young feller, I singlehandedly managed many thousands of databases! You live in a country club by comparison!”
He tipped his head back and poured the rest of his beer down his throat. Then his face fell to the table and he began to snore.
“True! True! Well said!” chimed the cheery database administrator on his left. “At my previous company, we treated deadlocks like vermin and immediately killed the transaction that was causing the deadlock!”
“If deadlocks occur frequently, there is no need for cell phone messages,” said the serious database administrator on his right. “It would be sufficient to send out an email message whenever a deadlock occurs.”
“Bah! Humbug!” said the youngest database administrator. Then, remembering his manners and his low ranking, he hastened to add, “God bless us, every one!”
The hour was getting rather late, so they shook the grizzled old graybeard awake and went their different ways, the youngest database administrator still grumbling under his breath.
By this time, the night was no longer dark or stormy, and a full moon filled the heavens with a softly glowing silver light that helped them fit their keys into the keyholes of their cars.
We trust that they all got home safely. God bless them, every one!
Click here for the rest of the article (PDF) which appeared in the February 2006 issue of the NoCOUG Journal. However, there are two terrible mistakes in the following paragraph of the article:
What does a transaction have to do if it is picked as a victim in a deadlock scenario? The transaction will receive the error “ORA-00060 deadlock detected while waiting for resource,” indicating that it was picked as a victim and that all of its work has been rolled back. The transaction is free to try again, and there is a more-than-considerable chance that it will succeed the second time around.
If you don’t spot the mistakes, you can review the answers to questions 4 and 6 in the follow-up article Oracle Annoyances for Geeks: Deadlocks that I wrote for the November 2007 issue of the NoCOUG Journal. The reason why I and so many others have made the above mistakes is that it is easy to assume that Oracle works in the same way as other database engines.