Home Dashboard Directory Help
Search

Document the locking behavior of each basic SQL Server construct by Linchi Shea


Status: 

Closed
 as Fixed Help for as Fixed


15
0
Sign in
to vote
Type: Suggestion
ID: 430251
Opened: 4/3/2009 10:40:58 AM
Access Restriction: Public
0
Workaround(s)
view

Description

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.



Details
Sign in to post a comment.
Posted by Microsoft on 7/5/2011 at 6:26 PM
Hi Linchi,
I'm closing this item as "fixed". I have updated the T-SQL Reference template that writers use when creating or modifying T-SQL Reference topics. I've added a section in the template that addresses locking behavior.

You will see this content added over time to various topics as we review the topics and update them.

Best,
Gail Erickson
SQL Server Documentation Team
Posted by Microsoft on 4/7/2009 at 10:19 AM
Hi Linchi,
Thank you for submitting this request. I am checking with some of the program managers to assess the feasibility of incorporating this content into BOL. I'll send an update when I have more information.

Regards,
Gail Erickson
SQL Server Documentation Team
Posted by Microsoft on 4/7/2009 at 10:19 AM
Hi Linchi,
Thank you for submitting this request. I am checking with some of the program managers to assess the feasibility of incorporating this content into BOL. I'll send an update when I have more information.

Regards,
Gail Erickson
SQL Server Documentation Team
Posted by Paul White NZ on 4/3/2009 at 8:43 PM
This information would be useful, but I believe it belongs on MSDN, given its advanced nature. A reference to more information available on MSDN/TechNet in BOL might be useful though. See http://msdn.microsoft.com/en-us/library/ms191261.aspx for an example of the sort of detail on locking behaviour I would like to see. I particularly like the way the lock information is integrated with other contextual information about online index rebuilds.

Paul White
Posted by Greg Linwood on 4/3/2009 at 2:51 PM
I agree - better documentation on locking would be very helpful
Cheers,
Greg Linwood
Posted by AKuz on 4/3/2009 at 11:03 AM
This is highly important. We have to learn all the ins and outs by trial and error, and we have to re-learn for every new version. This is very time consuming.

Cheers,
Alex Kuznetsov,
SQL Server MVP
Sign in to post a workaround.