12,555,894 members (63,182 online)
alternative version

#### Stats

50.4K views
31 bookmarked
Posted

, 2 Oct 2008 CPOL
 Rate this:
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.

 + =

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?

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.

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.

## History

• 2nd October, 2008: Initial post

## Share

 Software Developer (Senior) CSC New Zealand
No Biography provided

## You may also be interested in...

 Pro Pro

 First Prev Next
 Great 'Dummies' guide but... MR_SAM_PIPER6-Oct-08 13:37 MR_SAM_PIPER 6-Oct-08 13:37
 Re: Great 'Dummies' guide but... Andrew J Dixon6-Oct-08 13:41 Andrew J Dixon 6-Oct-08 13:41
 Re: Great 'Dummies' guide but... MR_SAM_PIPER6-Oct-08 13:52 MR_SAM_PIPER 6-Oct-08 13:52
 Re: Great 'Dummies' guide but... Andrew J Dixon6-Oct-08 14:24 Andrew J Dixon 6-Oct-08 14:24
 Re: Great 'Dummies' guide but... Donsw23-Jan-09 5:02 Donsw 23-Jan-09 5:02
 A better example supercat93-Oct-08 8:59 supercat9 3-Oct-08 8:59
 Re: A better example [modified] Andrew J Dixon5-Oct-08 10:31 Andrew J Dixon 5-Oct-08 10:31
 Re: A better example Andrew J Dixon5-Oct-08 11:42 Andrew J Dixon 5-Oct-08 11:42
 Re: A better example supercat96-Oct-08 6:10 supercat9 6-Oct-08 6:10
 Re: A better example [modified] Andrew J Dixon6-Oct-08 10:56 Andrew J Dixon 6-Oct-08 10:56
 Re: A better example supercat96-Oct-08 13:51 supercat9 6-Oct-08 13:51
 Re: A better example DQNOK7-Oct-08 4:14 DQNOK 7-Oct-08 4:14
 Re: A better example Andrew J Dixon7-Oct-08 10:37 Andrew J Dixon 7-Oct-08 10:37
 Re: A better example Andrew Rissing15-Oct-08 5:54 Andrew Rissing 15-Oct-08 5:54
 The concept will be understood sooner if... The Founder2-Oct-08 20:05 The Founder 2-Oct-08 20:05
 Re: The concept will be understood sooner if... [modified] Andrew J Dixon5-Oct-08 10:36 Andrew J Dixon 5-Oct-08 10:36
 Re: The concept will be understood sooner if... The Founder5-Oct-08 19:44 The Founder 5-Oct-08 19:44
 Re: The concept will be understood sooner if... Andrew J Dixon6-Oct-08 10:48 Andrew J Dixon 6-Oct-08 10:48
 Re: The concept will be understood sooner if... The Founder6-Oct-08 10:55 The Founder 6-Oct-08 10:55
 Very good Günther M. FOIDL2-Oct-08 16:45 Günther M. FOIDL 2-Oct-08 16:45
 Re: Very good [modified] Andrew J Dixon2-Oct-08 17:18 Andrew J Dixon 2-Oct-08 17:18
 Re: Very good Günther M. FOIDL2-Oct-08 22:42 Günther M. FOIDL 2-Oct-08 22:42
 Re: Very good [modified] Andrew J Dixon5-Oct-08 10:45 Andrew J Dixon 5-Oct-08 10:45
 Re: Very good Günther M. FOIDL5-Oct-08 20:56 Günther M. FOIDL 5-Oct-08 20:56
 Re: Very good Andrew J Dixon6-Oct-08 10:51 Andrew J Dixon 6-Oct-08 10:51
 Last Visit: 31-Dec-99 18:00     Last Update: 25-Oct-16 23:23 Refresh 1