Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I consider myself a beginner at Sql Server locking.

My understanding is that when using RCSI, Sql Server doesn't need to issue S locks because it uses row versioning (in most cases). From http://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx we can read the following about U locks:

Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later.

Knowing this, why does Sql Server need to issue U locks (when using RCSI)? It seems to me that Sql Server could simply read the rows, and request a X lock directly if an update must be performed.

The reason I've been thinking about this is because of deadlocks I'm experiencing with 2 sessions updating the same table. Something like this (simplified for clarity):

Session 1:

BEGIN TRAN
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value1
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value2
UPDATE t1 SET col1 = col1 + 100 WHERE col2 = value3

Session 2:

BEGIN TRAN
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value4
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value5
UPDATE t1 SET col1 = col1 + 100 WHERE indexedcol = value6

From what I gather in the Profiler lock report, both sessions have an X lock on some rows of the table, and are requesting U lock on each other's X-locked row => deadlock. However, I know for a fact that each concurrent session will always modify different rows because the value X will never be the same in 2 different concurrent sessions. That is, if those sessions were able to get their requested U lock, sql server would realize it is not necessary to upgrade an X lock anyway.

In conclusion I feel like, even though U locks are supposed to reduce deadlocks, they actually are creating an unnecessary deadlock here.

I understand they can be valuable for other isolation levels, but for RCSI, I don't get it...

share|improve this question

1 Answer 1

up vote 6 down vote accepted

Knowing this, why does sql server need to issue U locks (when using RCSI)? It seems to me that sql server could simply read the rows, and request a X lock directly if an update must be performed.

Unlike SI, RCSI does not detect update conflicts. As documented in Books Online, modifying data under RCSI reads currently-committed data, not a possibly out-of date version. (In the absence of update conflict detection, performing updates based on out-of-date data could result in a "lost update".)

Taking update locks is normal behaviour for a non-row-versioning query that updates data. It is a protection against a common cause of conversion deadlock, but it does not guarantee deadlock avoidance in all cases, especially where a different access path (index) is used to qualify rows to change.

You can find more details about the exact behaviour of RCSI when modifying data in my SQLperformance.com article, "Data Modifications Under Read Committed Snapshot Isolation". There is further background on RCSI in general in the article, "Read Committed Snapshot Isolation".

If the updates really are disjoint, you might consider performing the change using Snapshot Isolation rather than RCSI (which admittedly has complex behaviour in this area).

share|improve this answer
    
Thanks Paul, I had missed that. I will read your post in more detail asap. However it seems a little bit inconsistent (no pawn intended ;-)) that, under RCIS, it is deemed acceptable to make decisions based on out of date reads if those reads cannot be upgraded to X, but NOT acceptable if those reads can be upgraded to X. –  Clement Jul 22 at 7:02
    
Ah I just saw your edit about lost updates. I think I get it now. –  Clement Jul 22 at 7:06
    
I think I spoke too fast. I was thinking about it a bit more and I can't think of how a lost update could happen, even if U locks were not used (that is, sql server takes an X lock straight away). I refer to the first definition of lost update describe here technet.microsoft.com/en-us/library/cc546518.aspx –  Clement Jul 22 at 9:19
1  
@Clement As the first definition says, that can't happen under any isolation in SQL Server, because 'dirty writes' are not allowed. The 'lost update' RCSI read-locking avoids is the second sense: an update based on an out-of-date value. People would not expect a single statement to be vulnerable to that, in the way a read-delay-then-write transaction obviously is. –  Paul White Jul 22 at 11:16
    
I agree it would be confusing for a single statement to be vulnerable to that, although I found no 'official' description of this guarantee in the docs. But I'm thinking sql server could handle this better with the double checked locking pattern. 1) Check if the row needs to be updated 2) If it does, acquire X (no U), 3) Check again if the row needs to be updated 4) Update row if required, otherwise release X straight away. That would avoid unnecessary U locks, which are too eager and can cause unnecessary deadlocks as in the scenario I described above in the question. Makes sense? –  Clement Jul 22 at 12:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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