THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Document the locking behavior

In a multi-process/multi-thread system, locking is central to maintain data consistency and keep things in order. It is inevitably at the root of almost all the concurrency and scalability problems. To understand the behavior of such a system and to troubleshoot its performance problems, you have no choice but to have a good grasp of its locking behavior.

For this to happen, we need to begin with understanding the locking behavior of the basic building blocks offered by SQL Server in construcitng such a system. As such, one would think that the basic locking behavior of these building blocks (such as the T-SQL commands and operations) would be well documented. Unfortunately, that is not the case at all. In fact, the locking behavior of the basic SQL Server constructs is rarely, if at all, documented in SQL Server Books Online.

True, we may have learned about how commands such as UPDATE STATISTICS may acquire and release locks from blogs, newsgroups, or whilepapers. But it seems to me that locking is such a fundamental and integral part of any command or operation that without its explicit description the documentation for the command or operation would be incomplete. True, we all have run tests to find out a certain locking behavior. But I'd rather spend time not to study the locking behavior of the basic SQL Server constructs. Instead, it would be more productive to spend time studying the behavior of the system constructed on top of them.

True, the locking behavior of even some of the most basic commands can be complex and dynamic in nature. It is probably difficult to document. That should not be the reason for not even trying.

I wish in the documentation for each SQL Server command or operation there were a separate section on its locking behavior, just as there is a section for syntax and a section for permissions. Is that too much to wish for?

Published Thursday, April 2, 2009 8:58 PM by Linchi Shea
Filed under:



Mike Walsh said:

It's a lot to wish for but too much?  I don't think so. I would like to see that for some of the operations at least.

They put the permissions required for various operations into Books Online and that was a useful addition (whenever it was added, I forget when but it's been there awhile now)

Open a connect item and get folks replying/blogging about it :)

April 2, 2009 9:38 PM

Mike Walsh said:

And like the heel that I am, I just re-read your post and noticed you already mentioned the permissions :)

April 2, 2009 9:39 PM

Greg Linwood said:

I wish SQL Server used a multi version concurrency model by default (rather than the old Read Committed) b/c then we'd see fewer blocking problems to begin with..

April 3, 2009 1:54 AM

Paul White said:

Definitely yes.  Even if a comprehensive description is too optimistic, some basic information on locking behaviour would be nice.


On the subject of wish lists for documentation, could we also please have some definitive information of the effect of various commands and features on the ability of the QO to generate a parallel plan?

Discovering for oneself which things (e.g. encryption functions, CLR UDFs passed a MAX datatype...) force a fully serial plan, and which require a serial zone with a parallel plan (e.g. backward scans) is frustrating.  Especially when testing a new design only to find some undocumented restriction forces a serial plan where parallelism is absolutely required!


[Craig Freedman's article at is a good start - but doesn't cover everything, sadly.]

April 3, 2009 7:14 AM

Linchi Shea said:

> I wish SQL Server used a multi version concurrency model by default

That is probably too much to wish for because the default locking behavior is so fundamental to the SQL Server engine.

April 3, 2009 7:41 AM

Andrew Kelly said:

> I wish SQL Server used a multi version concurrency model by default

The problem with that is like Linchi stated too many people don't understand how locking works.  Too often they would wrte apps that make important decisions when relying on old data. I know Oracle uses that model for its default but I can't even begin to count how many developers that switched from Oracle to SQL and only when they started getting blocked did they find out all that time they were potentially reading old data.

April 3, 2009 9:13 AM

Alexander Kuznetsov said:

>I wish SQL Server used a multi version concurrency model by default

What about READ COMMITTED SNAPSHOT? It is used by default if set up as such.

April 3, 2009 9:13 AM

Paul White said:


That's true but seeing how few people notice/complain about reading statement-level consistent committed data (the entire Oracle user base for example) should tell us something.  Anything requiring better isolation than provided by READ_COMMITTED_SNAPSHOT should probably be running at REPEATABLE READ or higher anyway.  Recall that READ COMMITTED can read the same row twice, or one row not at all...and so on.  Most business people (the guys paying the bills) are delighted by SNAPSHOT for the readers-don't-block-writers thing and vice-versa.  Reading the most recently committed data seems eminently sensible!  Also bear in mind that many OLTP shops go READ UNCOMMITTED to avoid the blocking and deadlocking associated with READ COMMITTED.  Pure OLTP is rare, rare, rare.  Pretty much everyone runs some reports or other non-trivial selects concurrently with write operations.  /Paul

April 3, 2009 9:31 AM

Greg Linwood said:

Linchi - what do you mean by "fundamental to the SQL Server engine"? Do you mean that the engine isn't capable of working well with READ COMMITTED SNAPSHOT?

I think a bigger challenge is as Andy says - SQL devs tend not to undersstand MVCC, probably b/c they don't have a long history of using it. Perhaps its time to change & head down that path as a default behaviour.

The NOLOCK hint is used on such a widespread basis that chaos pretty much rules already - shifting the default to READ COMMITTED SNAPSHOT has to be a step in the right direction imo..

April 3, 2009 9:39 AM

Linchi Shea said:


I was referring to the fact that if you look at how READ COMMITTED SNAPSHOT is implemented, it is an 'add on' to the engine. In other words, they need to find ways or build new mechanisms on top of what is already there to get that to work. There were issues. I blogged about an issue a while back when it was observed to have adverse performance impact by just turning it on without actually maintaining any multiple versions. That particular issue may have been resolved(haven't checked it for a while now to be sure).

Let me know if we see READ COMMITTED SNAPSHOT is used in producing database benchmark scores.

April 3, 2009 12:11 PM

Alexander Kuznetsov said:


Where is the Connect item I could vote for?

April 3, 2009 12:21 PM

RussellH said:

I have to agree with everyone saying that READ COMMITTED SNAPSHOT should be the default locking behavior going forward.  The only drawback I see is that it could break old code that depends on the old behavior and the new code written by developers who expect the old behavior.

I agree with Andrew that a lot of Oracle developers don't understand that their query is reading old data, but I think a lot of SQL Server developers don't understand the consequences of readers being blocked by writers, and start throwing around NOLOCK hints like crazy (as Greg Linwood said).  I'm dealing with code now that had mandated NOLOCK on query.  They are reading aggregations that never existed at all.

The bottom line for me is that it easier to write code that is both fast (highly concurrent and scalable) and correct with READ COMMITTED SNAPSHOT.  But you still have to know what you are doing.

April 3, 2009 12:22 PM

Alexander Kuznetsov said:

Hi Russell,

>I'm dealing with code now that had mandated NOLOCK on query.

>They are reading aggregations that never existed at all.

You can get incorrect totals under READ COMMITTED just as well:

April 3, 2009 1:33 PM

Linchi Shea said:

Okay Alex, I submitted a connect item with text from here almost verbatim, though I didn't ask the rhetorical question whether it's too much to ask :-)

April 3, 2009 1:43 PM

Alexander Kuznetsov said:

Voted. Thank you Linchi!

April 3, 2009 2:04 PM

Greg Linwood said:

I agree it would be very helpful if this was better documented Linchi, so I've also voted a 5.

April 3, 2009 5:53 PM

Alexander Kuznetsov said:

How comes we two both voted 5 and:

Rating: 4.6 after 2 ratings  [?]

April 3, 2009 6:41 PM

Linchi Shea said:

I clicked on the five, and that bumped the rating up to 4.7. Curious math???

April 3, 2009 7:10 PM

Greg Linwood said:

I think this is a known issue - I recall some discussion on it in the MVP forum a while back but can't remember the reason. I'm sure the first vote was 5 , but said 4.6 before I lodged my vote.

April 3, 2009 10:31 PM

Paul White said:

Having thought about this some more, I'm not sure that BOL is the place for this.  

BOL is an excellent resource, but that doesn't mean that it should contain everything.  BOL works well at the level of detail it is currently targeted to.  The place for the detailed locking documentation is probably MSDN/TechNet.

An example article on MSDN that seems to cover locking very well at the same time as expanding on the documentation in BOL is"> and the 2005 version

I have voted 4 for the connect article, for the above reasons.

Note that the average rating on Connect is computed using an aggregate at READ UNCOMMITTED ;)

April 3, 2009 11:40 PM

RussellH said:


Thank you for the link to your article, I'm forwarding it to our DBA and developer groups.

April 4, 2009 12:25 AM

James Luetkehoelter said:

Ok, a late comment to the discussion, but I think Linchi has it on the head why SNAPSHOT_READ_COMMITTED in SQL Server is no where near comparable to the row versioning used in other systems. The whole snapshot approach in SQL Server is a shell game - instead of having to design around readers/blocking writers, SQL Server just shifts the management of it to TempDB (poor gets so much abuse now). It's a systemic problem due to the transaction log combining the concepts or rollforward/rollback.

So, yes, SNAPSHOT_READ_COMMITTED might be a good solution for any given environment, but keep in mind that the cost (size and load on Tempdb) might outway the benefits.

I think (and if I have mistated correct me Linchi) that is what you were referring to with the comment "That is probably too much to wish for because the default locking behavior is so fundamental to the SQL Server engine". I agree 100%. First and formost for any DBA is to know thy engine.

April 4, 2009 12:03 PM

John Paul Cook said:

Voted, thanks for opening it on Connect!

April 7, 2009 1:27 PM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement