Let's try an experiment. Begin by creating the following simple schema:
create table t1 (a int, b int)
create clustered index t1a on t1(a)
insert t1 values (1, 1)
insert t1 values (2, 2)
insert t1 values (3, 3)
create table t2 (a int)
insert t2 values (9)
In session 1, lock the third row of table t1:
begin tran
update t1 set b = b where a = 3
Now, in session 2 check the spid (you'll need it later) and run the following update at the default read committed isolation level:
select @@spid
update t1 set t1.b = t1.b
where exists (select * from t2 where t2.a = t1.b)
This update uses the following plan:
|--Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[b] = [t1].[b]))
|--Top(ROWCOUNT est 0)
|--Nested Loops(Left Semi Join, WHERE:([t2].[a]=[t1].[b]))
|--Clustered Index Scan(OBJECT:([t1].[t1a]))
|--Table Scan(OBJECT:([t2]))
This plan scans table t1 and looks each row up in table t2 to see whether the row must be updated. The scan acquires U locks on each row of t1. If the row is updated, the update upgrades the lock to an X lock. If the row is not updated, the scan releases the row and the lock since we are running in read committed isolation.
Since session 1 is holding a lock on the third row of table t1, the udpate blocks when the scan of t1 reaches the third row. At this point, we can check what locks session 2 is holding by running the following query in session 1 (or any other session):
resource_type request_mode request_type request_status
select resource_type, request_mode, request_type, request_status
from sys.dm_tran_locks
where request_session_id = <session_2_spid>
------------- ------------ ------------ --------------
DATABASE S LOCK GRANT
OBJECT IS LOCK GRANT
KEY U LOCK WAIT
PAGE IU LOCK GRANT
OBJECT IX LOCK GRANT
As expected, we see only one outstanding U lock request.
Next, return to session 2, abort the blocked update, and run the following statement:
update t1 set t1.a = t1.a
where exists (select * from t2 where t2.a = t1.b)
Notice that this time we are updating the clustering key of the index. Updates to the clustering key can cause rows to move within the index. To ensure that a row is not updated, encountered again by the same scan, and updated a second time (which would be incorrect), SQL Server must add a blocking operator between the scan and update of table t1. This requirement is known as "Halloween protection." Indeed, the new plan includes a sort:
|--Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[a] = [t1].[a]))
|--Top(ROWCOUNT est 0)
|--Sort(DISTINCT ORDER BY:([t1].[a] ASC, [Uniq1002] ASC))
|--Nested Loops(Inner Join, WHERE:([t2].[a]=[t1].[b]))
|--Clustered Index Scan(OBJECT:([t1].[t1a]), ORDERED FORWARD)
|--Table Scan(OBJECT:([t2]))
Once again this update blocks. Let's check the which locks it is holding by running the above query on the sys.dm_tran_locks DMV:
resource_type request_mode request_type request_status------------- ------------ ------------ --------------
DATABASE S LOCK GRANT
OBJECT IS LOCK GRANT
KEY U LOCK WAIT
KEY U LOCK GRANT
KEY U LOCK GRANT
PAGE IU LOCK GRANT
OBJECT IX LOCK GRANT
This time we see that there are two granted U locks. What's going on? Shouldn't these locks have been released since we are running a read committed scan? Not so fast! With the blocking sort operator in the plan, no rows are updated until the scan completes. If SQL Server simply released each U lock when the scan of t1 released each row, none of the rows would be locked when the update started. Without any locks, another session could slip in and modify the rows that we'd already scanned and which we were planning to update. Allowing another session to modify these rows could lead to incorrect results and data corruption. Thus, SQL Server retains these locks until the statement (not the transaction) finishes executing.
Microsoft