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