Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server

Database Deadlocks for Dummies

Rate me:
Please Sign up or sign in to vote.
4.76/5 (17 votes)
2 Oct 2008CPOL4 min read 75K   31   25
Explaining Database deadlocking to non-technical people

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.

b0g0.png

 + 

crayons_32.png

 = 

b2g1.png

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.

GFirst.gif

=

b0g0.png b0g1.png b1g1.png b2g1.png

Bobby likes blue, so he will always start colouring with blue, then uses whatever colours are remaining.

GFirst.gif

=

b0g0.png b1g0.png b2g0.png b2g1.png

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

b0g0.png

b0g0.png

They both start.
George takes the green crayon and paints green.
Bobby takes the blue crayon and paints blue.

b0g1.png

b1g0.png

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.

b0g1.png

b2g0.png

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

b0g1.png

b0g0.png

Someone has to win in the race for the first crayon!

b1g1.png

b0g0.png

Bobby is still waiting for the green to be released.

b2g1.png

b0g0.png

Bobby is still waiting for the green to be released.
George finishes and returns all the crayons.

b0g1.png

Bobby Starts.

b1g1.png

b2g1.png

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

b0g1.png

b0g0.png

Someone has to win in the race for the first crayon!

b1g1.png

b0g1.png

George finishes with the green and puts it back. He then gets the blue crayon.
Bobby can now start the green.

b2g1.png

b0g1.png

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.

b1g1.png

Bobby can now start the blue.

b2g1.png

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) CSC
New Zealand New Zealand
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralGreat 'Dummies' guide but... Pin
MR_SAM_PIPER6-Oct-08 13:37
MR_SAM_PIPER6-Oct-08 13:37 
GeneralRe: Great 'Dummies' guide but... Pin
Andrew J Dixon6-Oct-08 13:41
professionalAndrew J Dixon6-Oct-08 13:41 
GeneralRe: Great 'Dummies' guide but... Pin
MR_SAM_PIPER6-Oct-08 13:52
MR_SAM_PIPER6-Oct-08 13:52 
GeneralRe: Great 'Dummies' guide but... Pin
Andrew J Dixon6-Oct-08 14:24
professionalAndrew J Dixon6-Oct-08 14:24 
GeneralRe: Great 'Dummies' guide but... Pin
Donsw23-Jan-09 5:02
Donsw23-Jan-09 5:02 
GeneralA better example Pin
supercat93-Oct-08 8:59
supercat93-Oct-08 8:59 
GeneralRe: A better example [modified] Pin
Andrew J Dixon5-Oct-08 10:31
professionalAndrew J Dixon5-Oct-08 10:31 
GeneralRe: A better example Pin
Andrew J Dixon5-Oct-08 11:42
professionalAndrew J Dixon5-Oct-08 11:42 
GeneralRe: A better example Pin
supercat96-Oct-08 6:10
supercat96-Oct-08 6:10 
GeneralRe: A better example [modified] Pin
Andrew J Dixon6-Oct-08 10:56
professionalAndrew J Dixon6-Oct-08 10:56 
GeneralRe: A better example Pin
supercat96-Oct-08 13:51
supercat96-Oct-08 13:51 
GeneralRe: A better example Pin
DQNOK7-Oct-08 4:14
professionalDQNOK7-Oct-08 4:14 
GeneralRe: A better example Pin
Andrew J Dixon7-Oct-08 10:37
professionalAndrew J Dixon7-Oct-08 10:37 
JokeRe: A better example Pin
Andrew Rissing15-Oct-08 5:54
Andrew Rissing15-Oct-08 5:54 
GeneralThe concept will be understood sooner if... Pin
mackaaij2-Oct-08 20:05
mackaaij2-Oct-08 20:05 
GeneralRe: The concept will be understood sooner if... [modified] Pin
Andrew J Dixon5-Oct-08 10:36
professionalAndrew J Dixon5-Oct-08 10:36 
GeneralRe: The concept will be understood sooner if... Pin
mackaaij5-Oct-08 19:44
mackaaij5-Oct-08 19:44 
GeneralRe: The concept will be understood sooner if... Pin
Andrew J Dixon6-Oct-08 10:48
professionalAndrew J Dixon6-Oct-08 10:48 
GeneralRe: The concept will be understood sooner if... Pin
mackaaij6-Oct-08 10:55
mackaaij6-Oct-08 10:55 
GeneralVery good Pin
Günther M. FOIDL2-Oct-08 16:45
Günther M. FOIDL2-Oct-08 16:45 
GeneralRe: Very good [modified] Pin
Andrew J Dixon2-Oct-08 17:18
professionalAndrew J Dixon2-Oct-08 17:18 
JokeRe: Very good Pin
Günther M. FOIDL2-Oct-08 22:42
Günther M. FOIDL2-Oct-08 22:42 
GeneralRe: Very good [modified] Pin
Andrew J Dixon5-Oct-08 10:45
professionalAndrew J Dixon5-Oct-08 10:45 
GeneralRe: Very good Pin
Günther M. FOIDL5-Oct-08 20:56
Günther M. FOIDL5-Oct-08 20:56 
GeneralRe: Very good Pin
Andrew J Dixon6-Oct-08 10:51
professionalAndrew J Dixon6-Oct-08 10:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.