Read Committed and Bookmark Lookup

  • Article

In my last two posts, I discussed two scenarios - one involving updates and another involving large objects - where SQL Server extends the duration of read committed locks until the end of a statement instead of releasing the locks as soon as each row is released.   In this post - which I promise will be the last in this series on read committed locks - I will discuss a final scenario involving bookmark lookups where SQL Server holds read committed locks longer than expected.

As you might expect, if you've read my prior two posts, this final scenario occurs when there is a blocking operator between the non-clustered index seek and the bookmark lookup operation.   There are basically three cases:

  • A sort on the outer side of the nested loops join.
  • An "OPTIMIZED" nested loops join.
  • A nested loops join "WITH PREFETCH."

The following example demonstrates a bookmark lookup with a nested loops join with a prefetch:

create table t (a int, b int, c char(1000))
create unique clustered index ta on t(a)

set nocount on
declare @i int
set @i = 0
while @i < 1000
  begin
    insert t values (@i, @i, @i)
    set @i = @i + 1
  end
set nocount off

create index tb on t(b)

select c from t where b < 25

Here is the query plan for the select:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([t].[a], [Expr1004]) WITH UNORDERED PREFETCH)
       |--Index Seek(OBJECT:([t].[tb]), SEEK:([t].[b] < (25)) ORDERED FORWARD)
       |--Clustered Index Seek(OBJECT:([t].[ta]), SEEK:([t].[a]=[t].[a]) LOOKUP ORDERED FORWARD)

Note the "WITH UNORDERED PREFETCH" keywords on the nested loops join.

I am not going to demontrate it , but when SQL Server executes this query, it holds S locks on the rows returned by the index seek until the query finishes executing.  If you want to view these locks, you can set up an experiment similar to the ones from my prior two posts.

Why does SQL Server acquire these extra locks?  Suppose that SQL Server did not hold the S locks on the index seek until the end of the statement but rather released them immediately.  The prefetch results in a delay between when the index seek returns rows and when the join processes the rows and the executes the clustered index seek.  During this delay, there would be no locks held on rows returned by the seek but not yet processed by the join.  Another session could slip in, modify the row, and cause an inconsistency similar to the one that I demonstrated with index intersections in this post.

Before I wrap up with this topic, I want to emphasize a couple more points.  First, updates can also use prefetching.  So, if you have a query plan with an update that includes a prefetch (again, look for the "WITH PREFETCH" keywords), SQL Server holds locks on the source of the rows to be updated just as it does when the plan includes any other blocking operator such as a sort or spool.  Second, to avoid corrupting data, updates always acquire locks even when run at read uncommitted isolation level and, if necessary due to blocking operators in the plan, hold these locks until the end of the statement.  Queries that access large objects and queries with bookmark lookups do not acquire or hold locks when run at read uncommitted isolation level.