At a recent Charlotte SQL Server Users Group meeting, one member asked what’s the difference between locking and blocking.  I figured I’d cover that today.

Locking

When you work in your database, you’re not alone.  There are other users connected hitting the same database as you, the same tables as you.  You have to share the resources.  Because of this, the database engine has to be able to protect the data from conflicting requests.

Without locking can you imagine what kind of chaos would result if two users tried to update the same record at the same time, each making a different change?

Pretty bad stuff, right?

Well locking can prevent those bad things from happening.  There are several types of locks, none of them are bad.  I’m just going to cover three today, the basics.  You need to  have an idea of what they’re used for.

• Shared(S) – if a shared lock is established on a table, such as a SELECT statement, other queries that use a shared lock can be run.  But if one of those queries requires an exclusive lock, it will have to wait until the shared locks clear (and vice-versa).  Shared locks are released when the query completes, unless you use the REPEATABLE READ query hint.
• UPDATE(U) – only one query at a time can get an update lock (per object).  This lock will upgrade to an exclusive lock as needed.
• Exclusive(X) – No one else is going to access this object…unless they use NOLOCK or READ UNCOMMITTED.  And honestly, if they do, they’d better be sure they know why they’re using that hint.  Just sayin’.

Blocking

Now that you understand these types of locks, you could see how you could get a series of queries come in that could conflict with each other.

Query one wants to update table two based on table one.

Query two wants to update table one based on table two.

Both establish exclusive locks.  Query one ends up being blocked by query two, it came in a split second sooner.  As soon as query two completes, query two begins.  It completes some time later.

So a locks can cause blocking.  They can also get more serious.  If you have a situation where one query blocks another, and that one in turn blocks another, you can eventually get into a situation where neither query can complete.  This is deadlocking.  It’s the equivalent of a mexican standoff…in your server.

In that case, you have to bring an extra gun to the showdown.  It ends badly, you have to KILL a spid or two, and no one want’s that.

Unless you work in Hollywood.

In a later posts, I’ll discuss techniques for discovering the root cause of your deadlocks, and how you can prevent them from happening in the first place.  For now, I hope you’ve learned something.  If you have any questions, send them in.  I’m here to help!