Document the locking behavior

re: Document the locking behavior

Mike Walsh — Fri, 03 Apr 2009 01:38:15 GMT

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 :)

re: Document the locking behavior

Mike Walsh — Fri, 03 Apr 2009 01:39:05 GMT

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

re: Document the locking behavior

Greg Linwood — Fri, 03 Apr 2009 05:54:00 GMT

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..

re: Document the locking behavior

Paul White — Fri, 03 Apr 2009 11:14:23 GMT

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

<mini-rant>

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!

</mini-rant>

[Craig Freedman's article at http://blogs.msdn.com/craigfr/attachment/2167013.ashx is a good start - but doesn't cover everything, sadly.]

re: Document the locking behavior

Linchi Shea — Fri, 03 Apr 2009 11:41:56 GMT

> 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.

re: Document the locking behavior

Andrew Kelly — Fri, 03 Apr 2009 13:13:07 GMT

> 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.

re: Document the locking behavior

Alexander Kuznetsov — Fri, 03 Apr 2009 13:13:39 GMT

>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.

re: Document the locking behavior

Paul White — Fri, 03 Apr 2009 13:31:04 GMT

Andrew,

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

re: Document the locking behavior

Greg Linwood — Fri, 03 Apr 2009 13:39:40 GMT

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..

re: Document the locking behavior

Linchi Shea — Fri, 03 Apr 2009 16:11:14 GMT

Greg;

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.

re: Document the locking behavior

Alexander Kuznetsov — Fri, 03 Apr 2009 16:21:48 GMT

Linchi,

Where is the Connect item I could vote for?

re: Document the locking behavior

RussellH — Fri, 03 Apr 2009 16:22:46 GMT

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.

re: Document the locking behavior

Alexander Kuznetsov — Fri, 03 Apr 2009 17:33:59 GMT

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:

http://www.simple-talk.com/sql/t-sql-programming/the-case-of-the-skewed-totals/

re: Document the locking behavior

Linchi Shea — Fri, 03 Apr 2009 17:43:45 GMT

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 :-)

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=430251

re: Document the locking behavior

Alexander Kuznetsov — Fri, 03 Apr 2009 18:04:17 GMT

Voted. Thank you Linchi!

re: Document the locking behavior

Greg Linwood — Fri, 03 Apr 2009 21:53:17 GMT

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

re: Document the locking behavior

Alexander Kuznetsov — Fri, 03 Apr 2009 22:41:43 GMT

How comes we two both voted 5 and:

Rating: 4.6 after 2 ratings [?]

re: Document the locking behavior

Linchi Shea — Fri, 03 Apr 2009 23:10:07 GMT

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

re: Document the locking behavior

Greg Linwood — Sat, 04 Apr 2009 02:31:50 GMT

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.

re: Document the locking behavior

Paul White — Sat, 04 Apr 2009 03:40:20 GMT

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 http://msdn.microsoft.com/en-us/library/ms191261.aspx">http://msdn.microsoft.com/en-us/library/ms191261.aspx and the 2005 version http://msdn.microsoft.com/en-us/library/ms191261(SQL90).aspx

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 ;)

re: Document the locking behavior

RussellH — Sat, 04 Apr 2009 04:25:50 GMT

Alex,

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

re: Document the locking behavior

James Luetkehoelter — Sat, 04 Apr 2009 16:03:46 GMT

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 Tempdb...it 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.

re: Document the locking behavior

John Paul Cook — Tue, 07 Apr 2009 17:27:36 GMT

Voted, thanks for opening it on Connect!