First published on MSDN on May 31, 2007
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.
