Introduction
Trying to explain a database / transaction deadlock to a non-technical person can be a challenge.
The concept however is something we have dealt with since pre-school.
Imagine a simple colouring exercise of the globe. Unless you are artistically gifted, blue and green are all that is needed to complete the picture. Being a state funded school, there is only one green and one blue crayon.
Now imagine two different, but fundamentally equivalent, kids with one subtle difference.
George likes green, so he will always start colouring with green, then uses whatever colours are remaining.
Bobby likes blue, so he will always start colouring with blue, then uses whatever colours are remaining.
Comparison
Both of these approaches are valid. They both end with the same result in the same period of time using the same amount of crayon.
However they are both very focused on their work and will not share nicely. Once they have a crayon they will not release it until the whole picture is completed.
In database terms, this is like two different routines, possibly written by two vendors, working on the same database. Both could be well written and work perfectly on their own. When forced to work together, the fun begins.
What happens when these two try to work together at the same time?
The Deadlock
George and Bobby:
- Work in their own order
- Both Insist on holding on to a crayon once they have to use it.
| George | Bobby | The Result |
|
| They both start.
George takes the green crayon and paints green.
Bobby takes the blue crayon and paints blue.
|
| 
| George finishes the green and asks for the blue.
Bobby says ‘no way, I have not finished yet!’
George Waits.
Bobby keeps painting the blue.
|
| 
| George asks for the blue again.
Bobby says ‘no way, I have not finished yet!’
George Waits for Bobby..
Bobby finishes the blue and asks for the green.
George says ‘no way, I have not finished yet!’
Bobby Waits for George.
THIS IS A DEADLOCK!
|
At this stage everybody waits… forever… unless the teacher breaks the cycle and forces one of them to start again (and hopefully learn to share better). While this seems unfair to the victim, the reality is, without outside intervention, neither would ever finish.
Modern databases (and smart teachers) recognise when this has happened and choose one as the deadlock victim. This one has to start over so that the other can continue. At least both will eventually be able to finish.
Consistent Approach
George and Bobby:
- Agree to work in the same order
- Both insist on holding on to a crayon once they have to use it.
In database terms, this is like putting a transaction around the entire routine
| George | Bobby | The Result |
|
| Someone has to win in the race for the first crayon!
|
|
| Bobby is still waiting for the green to be released.
|
|
| Bobby is still waiting for the green to be released.
George finishes and returns all the crayons.
|
|
| Bobby Starts.
|
|
| |
|
| Bobby finishes.
|
They both get there in the end, no one had to start over, but you would agree that this is not really multi-processing.
Consistent Approach + Resource Release
George and Bobby:
- Agree to work in the same order
- Agree to release crayons when they have finished with them
This is the database equivalent of placing transactions around discrete operations - as small as possible while still maintaining integrity.
| George | Bobby | The Result |
|
| Someone has to win in the race for the first crayon!
|
|
| George finishes with the green and puts it back. He then gets the blue crayon.
Bobby can now start the green.
|
|
| Bobby finishes with the green and returns it, he can't start the blue as Bobby is still using it.
Later on George finishes and returns all the crayons.
|
|
| Bobby can now start the blue.
|
|
| Bobby finishes.
|
They both get there in the end, no one had to start over and some tasks could be performed simultaneously without too much waiting.
You See Timmy!
Apparently there once was this cheesy show about a dog named Lassie. These shows always ended in a sugar-coated ‘You See Timmy” moment where the moral was explained. As this story is about sharing, I felt that we had some lessons Timmy would be proud of.
- When playing with friends, make sure you play the game by the same rules.
- Don't ask for something unless you really need it.
- Put something back as soon as you are finished with it.
- Sharing is important.
This translates into the following guidelines for database access:
- Access locked resources in the same order.
- If a query is read only use a WITH (NOLOCK) hint.
- If you have a transaction, keep it only as long as you have to.
- Sharing is important.
May all your crayons never break!
History
- 2nd October, 2008: Initial post
