Read Committed and Updates

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

select resource_type, request_mode, request_type, request_status
from sys.dm_tran_locks
where request_session_id = <session_2_spid>

resource_type  request_mode  request_type  request_status
————- ———— ———— ————–

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
————- ———— ———— ————–

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.

Comments (10)

  1. caoyexun says:

    Change a little,when we create a unique index on table t2 column b,a blocking operator between the scan

    and update of table t1 becomes tale spool(Eager Spool) operator ,but not sort operator.

    The following query plan:

    StmtText                                                                                                  LogicalOp

    —————————————————————————–      ——————–

    |–Clustered Index Update(OBJECT:([t1].[t1a]), SET:([t1].[a] = [t1].[a]))   Update

     |–Table Spool                                                                                         Eager Spool

          |–Top(ROWCOUNT est 0)                                                                 Top

               |–Nested Loops(Left Semi Join, WHERE:([t2].[a]=[t1].[b]))            Left Semi Join

                    |–Clustered Index Scan(OBJECT:([t1].[t1a]))                           Clustered Index Scan

                    |–Table Scan(OBJECT:([t2]))                                                  Table Scan

    I come from China and my English is not good.

    I hope you can understand what I say.

    What you wrote in your blog is very helpful to me.

    Thanks a lot.

  2. caoyexun says:

    create a unique index on the a column of t2 table

  3. The eager spool, like the sort, is a blocking operator.  It consumes all of the input rows before returning any results.  In my original example, the optimizer chose to transform the left semi-join into an inner join and use a sort distinct to remove any duplicates and ensure that each row is updated at most once.  This is similar to the transformation that I described in this post:  With the unique index on t2(a), there is no need to remove duplicates so the optimizer uses an eager spool.  With either the spool or the sort, we see the same locking behavior.

  4. In my last post , I explained that SQL Server holds read committed locks until the end of an update statement

  5. In my last two posts, I discussed two scenarios – one involving updates and another involving large objects

  6. cssgli says:

    Hi Craig,

    Why is "Top(ROWCOUNT est 0)" introduced to the execution plans in your examples?

    What’s meaning of "est"?

    Thank you so much!

  7. I’ll do a post on rowcount top to answer this question.  I’ll try to write it soon.

  8. Martin Smith says:

    Hi Craig,

    You say "Without any locks, another session could slip in and modify the rows that we'd already scanned and which we were planning to update."

    However in your example none of the rows qualify for the update. Is there any reason the locks aren't released earlier? Does this need to be established in the scan?

  9. Craig Freedman says:

    Unfortunately, the locks are acquired by the scan before it is known whether a row joins and, thus, qualifies for the update.  Once a lock is acquired, it is held until the end of the statement.



  10. Nick says:


    Is there a difference between durations of row-level locks and page-level ones? From what I have seen, the engine does not release page locks (even the UI ones) until the scan completes (under READ COMMITTED) even if no clustering key is involved. And this leads me to believe it is what is causing lock escalations that we see whenever the optimizer chooses to do a scan against a large table even if, ultimately, no rows qualify for an update. Appreciate your help.