Why are U locks required when using Read Committed Snapshot Isolation

Question

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

Answer 1

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