5

Reading an older overview of CouchDB published on the IBM site HERE, I was surprised to find the following:

Most modern databases have started to move from locking mechanisms to MVCC, including Oracle (since V7), MySQL (when used with InnoDB) and Microsoft® SQL Server 2005 and later.

I am using SQL Server quite a bit at the moment (V2012) and never considered that there may be any form of MVCC. I have also used with (tablock, holdlock) in stored procedures before.

Does SQL Server really implement MVCC anywhere and how does that reconcile with the idea of with (tablock, holdlock) if it does?

12

Does SQL Server really implement MVCC anywhere

Yes, since SQL Server 2005.

The SQL Server terminology is "row-versioning isolation levels". See the product documentation tree starting at Locking and Row Versioning. Note in particular that there are two separate "MVCC" implementations, read committed isolation using row versioning (RCSI) and snapshot isolation (SI).

and how does that reconcile with the idea of with (tablock, holdlock) if it does?

Using that combination of hints serializes access to whole table(s). It is the least concurrent option available, so using these hints should be very rare. Whether a particular use could be replaced with RCSI or SI isolation depends on the specific circumstances. You could ask a follow-up question with a specific example if you want us to address that aspect in detail.

You might also like to read my series of articles on SQL Server isolation levels.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.