Read Committed and Large Objects

In my last post, I explained that SQL Server holds read committed locks until the end of an update statement (instead of releasing the locks as soon as each row is released) if there is a blocking operator between the scan or seek of the rows to be updated and the update itself.  In this post, I'll take a look at a similar result involving large objects.

Normally, when SQL Server moves data through a blocking operator such as a sort, it makes a copy of the data.  Once SQL Server makes a copy, there is no need to preserve the original row or source of the data.  However, since large objects (e.g., varchar(max)) can store up to 2 Gbytes, it is generally not practical to make copies of large objects.  Instead, whenever possible, SQL Server uses "pointers" to the data instead of making copies of the data.  To ensure that the pointers remain valid, SQL Server does not release any locks on the rows that contain the large objects until the statement completes.

Let's observe this behavior.  Begin by creating the following table:

create table t (pk int primary key, i int, lob varchar(max))
insert t values (1, 1, 'abc')
insert t values (2, 2, 'def')
insert t values (3, 3, 'ghi')

In session 1 lock the third row:

begin tran
update t set i = i where pk = 3

Now, in session 2 check the spid (we'll use it later to look at the locks) and run this query which scans the table and reads each large object (using the default read committed isolation level):

select @@spid

select lob from t

This query uses a trivial plan which consists of a clustered index scan:

  |--Clustered Index Scan(OBJECT:([t].[PK__t__2D27B809]))

Because session 1 has a lock on the third row of the table, the scan blocks.  While it is blocked, we can check which locks it holds by running the following query in session 1:

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
PAGE           IS            LOCK          GRANT
KEY            S             LOCK          WAIT
OBJECT         IS            LOCK          GRANT

We see the the scan is not holding any key locks and is waiting for the one key lock held by session 1.

Next, kill the scan in session 2 and try the following query:

select lob from t order by i

The plan for this query includes a sort:

  |--Sort(ORDER BY:([t].[i] ASC))
       |--Clustered Index Scan(OBJECT:([t].[PK__t__2D27B809]))

As I explained above, when we check the locks held by this query, we find that due to the large object and the blocking sort, this query holds key locks on each row it touches:

 resource_type  request_mode  request_type  request_status
-------------  ------------  ------------  ---------------
DATABASE       S             LOCK          GRANT
KEY            S             LOCK          GRANT
PAGE           IS            LOCK          GRANT
KEY            S             LOCK          WAIT
OBJECT         IS            LOCK          GRANT
KEY            S             LOCK          GRANT

If you have any doubt whether the extra key locks are due to the large object, repeat this experiment with the following nearly identical query which does not return the large object:

select i from t order by i

Finally, try one more query:

select i from t where lob > 'a'

The plan for this query uses an explicit filter operator to evaluate the predicate on the large object:

  |--Filter(WHERE:([t].[lob]>[@1]))
       |--Clustered Index Scan(OBJECT:([t].[PK__t__2D27B809]))

The filter is not a blocking operator and the query does return the large object.  Nevertheless, if you run this query, you will observe that SQL Server once again holds locks on each row touched by this query.  In this example, SQL Server is overly conservative and retains the locks even though they are technically unnecessary.

Comments

• Anonymous
  June 07, 2007
  In my last two posts, I discussed two scenarios - one involving updates and another involving large objects
• Anonymous
  June 06, 2012
  Hi Craig, Just an update.  Not sure when this changed exactly, but the shared locks are no longer held (overly conservatively) for the pipelined Filter example.  Just to be clear for anyone else reading this comment, the behaviour remains the same as described for a stop-and-go (blocking) operator. Tested on: SQL Server 2005 build 5296 (SP4 CU3) SQL Server 2008 build 5775 (SP3 CU4) SQL Server 2008 R2 build 3720 (SP2 CTP) SQL Server 2012 build 2316 (RTM CU1)