Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I tried to find some information about blocking in SQL Server, but I could not find a concise explanation for what it is and how it happens. Could you please enlighten me?

share|improve this question

2 Answers 2

up vote 11 down vote accepted

Analogies

Sometimes it helps to use analogies away from computers.

Let's say you have a ball and two children. Only one child can have the ball at any one time. However, if one of the children gets the ball and doesn't let go of it because he's distracted (watching TV, for example), then the other child will not get to play with the ball.

The other child is blocked from that resource.

If we compare this to the TV, for example, several children can watch TV at any one point.

Locks

If we move over to the database world, we see that there are different ways to use resources (just like our two examples above). We can perform "reads" or we can perform "writes".

When we want to read the data, there's no reason that other's can't read the data as well--just like two people watching TV. However, if we want to write the data, then we need to make sure that no one else is looking at it. If they are reading it while we're writing it, they will get "dirty" reads. (Meaning, they'll see the data partially written out, which will be invalid.)

In order to insure that these dirty reads never occur, we have two primary types of locks, Read Locks and Exclusive Locks.

Read Lock

You can have several different connections reading from the same datasource at any given time. But to insure that no one changes that data while they're reading it, they take out a Read Lock.

Once a connection has a read lock on a piece of data, all other connections must wait until the Read Lock is released before they can write the data. Others can, however, take out Read Locks of their own on that same piece of data.

Exclusive Lock

If a connection wants to update/insert/delete a piece of data, they have to take out an exclusive lock. This prevents any other connection from also taking out a lock on the data (making the lock exclusive to that connection).

When a connection has an exclusive lock on the data, no other connections may read from the data. This helps prevent dirty reads by insuring that no one can read the data while its being written.

Blocking

"Blocking" is simply a term that means that one connection is holding a lock on a resource when another connection wants to read or write to it. It doesn't necessarily mean that the owner connection won't release it, just that it's currently holding it.

Compare this to the case with a child holding the ball. The child holding the ball is blocking all other children from holding the ball.

Deadlock

I know you didn't ask this, but it's only one more step to get to deadlocks (and it's related very directly to blocking).

Deadlocks can happen when you have two connections that each have a lock, but they want each others resource. In this scenario, it's like two children that each has a ball, but wants the other's ball.

Like children, these connections are not willing to share at all. Each connection needs access to both of the resources in order to continue. However, they are in a state of permanent blocking. In this state, the parent (DBMS) has to come in and choose a loser so that one of the children (connections) can have access to both of the resources.

Once that "winning" connection is done, it releases the resources and then the other ("losing") connection can try again to get to both resources.

So, the concept of a deadlock is where you have two resources that are blocking each other.


Here, you can read more about all the different types of locks SQL Server has to offer and the different resources that can cause blocking/deadlocks. The article is old, but it still applies for SQL Server 2000 through 2008 R2. (There are a few more types of locks added to later versions of SQL Server, but that will give you a starting point.)

share|improve this answer
3  
Nice essay +1 :) –  Stuart Blackler Aug 18 '11 at 14:27
    
@Richard, as you had mentioned if a deadlock occurs, then the DBMS would come in and choose a loser.. Is this done automatically by the DBMS? Or would a poorly designed system (one that experiences deadlocks often) halt until a person initiates the DBMS to do so? –  CenterOrbit Aug 18 '11 at 18:07
1  
For SQL Server, this is done automatically (provided the database can detect the deadlock). It might have trouble detecting it if, for example, it's actually a live lock. –  Richard Aug 18 '11 at 18:19
    
Excellent and very thorough answer Richard. One additional point on live locks (which shouldn't really be compared or related to deadlocks, its just plain old blocking)... SQL will prevent further locks being taken where an exclusive lock is waiting, IIRC after 4 successive overlapping shared locks. –  Mark Storey-Smith Aug 19 '11 at 2:54
    
Thanks for not going into the details of what happens to the losing child in the deadlock situation! :) +1 –  SqlACID Aug 19 '11 at 11:06

Great explanation by Richard, but just wanted to add links to official documentation. These topics were written for SQL Server 2000, but much of the concepts remain the same today:

Understanding and Avoiding Blocking

Understanding Locking in SQL Server

Edit - some additions:

All 3 are very well known SQL Server authors and/or MVPs.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.