Share via

Read Committed and Updates

  • Article

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

Comments

  • Anonymous
    May 24, 2007
    Change a little,when we create a unique index on table t2 column b,a blocking operator between the scanand 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 ScanI 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.

  • Anonymous
    May 24, 2007
    create a unique index on the a column of t2 table

  • Anonymous
    May 25, 2007
    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: http://blogs.msdn.com/craigfr/archive/2006/12/04/semi-join-transformation.aspx.  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.

  • Anonymous
    May 31, 2007
    In my last post , I explained that SQL Server holds read committed locks until the end of an update statement

  • Anonymous
    June 07, 2007
    In my last two posts, I discussed two scenarios - one involving updates and another involving large objects

  • Anonymous
    July 17, 2007
    The comment has been removed

  • Anonymous
    July 24, 2007
    I'll do a post on rowcount top to answer this question.  I'll try to write it soon.

  • Anonymous
    December 27, 2012
    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?

  • Anonymous
    January 02, 2013
    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. HTH Craig

  • Anonymous
    July 30, 2015
    Craig, 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.