Document the locking behavior

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

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

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

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

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

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

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

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

Linchi,

Where is the Connect item I could vote for?

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.

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/

Voted. Thank you Linchi!

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

How comes we two both voted 5 and:

Rating: 4.6 after 2 ratings  [?]

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.

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

Alex,

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

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.

Voted, thanks for opening it on Connect!

PingBack from http://www.pythian.com/news/1919/log-buffer-142-a-carnival-of-the-vanities-for-dbas