This was one of the questions I did not answer in my previous look at Shared locks in standard transaction isolations. In that post the lowest level lock the READ COMMITTED test took was an Intent Shared (IS) lock on the single data page in the table. But I do remember reading somewhere that READ COMMITTED transactions only hold Shared locks on a page while data is read from the page, not for the duration of the query. The effects of this were hinted at with the Phantom Reads experienced during the READ COMMITTED query without a transaction.
To look into this further I tried a similar approach used previously, setting up the environment as follows.
create database locks;
go
use locks;
go
create table data
(
col1 char(1) primary key,
col2 char(4500) not null
)
insert into
data
values
('a', 'a');
insert into
data
values
('b', 'b');
go
--drop view vwLocks
create view
vwLocks
as
select
cast(request_mode as varchar(5)) as request_mode ,
cast(request_status as varchar(10)) as request_status ,
cast(resource_type as varchar(15)) as resource_type,
case
when resource_type = 'OBJECT' then
cast(object_name(resource_associated_entity_id) as varchar(20))
else
cast(resource_associated_entity_id as varchar(20))
end as resource_name,
cast(resource_description as varchar(15)) as resource_description
from
sys.dm_tran_locks
where
request_session_id = @@spid
go
-- drop and re-establish the connection to close any locks
-- hanging around from creating the db.
This created a table where only a single row can fit on a data page, so each of the two rows added must be stored on a different data page. To verify the number of data pages in the table I used DBCC IND. It’s undocumented but there is some info out there, especially in Inside Microsoft SQL Server 2005: The Storage Engine.
Pages of type 1 are the in row data pages, in my DB the first data page is 143 and the NextPagePID column shows that the next page is 154. To view the contents of the pages use DBCC PAGE after first turing on the trace flag 3604, I’ve used these undocumented methods previously.
dbcc ind (locks, data, -1)
-- to view the contents of the pages
dbcc traceon(3604)
dbcc page (locks, 1,143,3)
The next step was to read from the data table and observe the locks taken.
set transaction isolation level read committed
begin transaction
select data.col1, l.* from data outer apply vwLocks as l
commit
/*
col1 request_mode request_status resource_type resource_name resource_description
---- ------------ -------------- --------------- -------------------- --------------------
a S GRANT DATABASE 0
a IS GRANT PAGE 72057594038321152 1:143
a IS GRANT OBJECT data
a IS GRANT OBJECT vwLocks
b S GRANT DATABASE 0
b IS GRANT PAGE 72057594038321152 1:143
b IS GRANT OBJECT data
b IS GRANT OBJECT vwLocks
*/
When reading both of the records the view showed locks on page 143, this was not what I expected. I thought it would show an Intent Shared lock on page 143 when reading row “a” and an Intent Shared lock on page 154 when reading row “b”.
The query plan was helpful in solving this mystery. The results of the query against vwLocks are cached by a Table Spool operation and used to perform the join for both rows from the data table.
The Remote Scan in the lower right corner is the read against sys.dm_tran_locks.
As seen in the properties of the Remote Scan operation reading from sys.dm_tran_locks occurs once and the results are used once (Actual Rebinds = 0 and Actual Rewinds = 0). However the Table Spool operation is evaluated twice (Actual Rebinds = 1 and Actual Rewinds =1).
From the help page on Logical and Physical Operators…
“If an operator is on the inner side of a loop join, the sum of the number of rebinds and rewinds should equal the number of rows processed on the outer side of the join. A rebind means that one or more of the correlated parameters of the join changed and the inner side must be reevaluated. A rewind means that none of the correlated parameters changed and the prior inner result set may be reused.”
So I needed to remove the caching effect of the Table Spool to see the lock state for each row read from the table. I tried using join predicates and table valued functions that took parameters but they all included spooling and returned the same values. Finally I settled on a c# table valued function that queried vwLocks; the query engine did not dare cache the results from something so wacky as a clr routine.
The c# function queries the view using the context connection, this is the connection the request is running on. It then reads the results into objects so it can dispose of the SqlDataReader and associated objects before returning the IEnumerable collection. The Fill method is then called by SQL to project the data in each object in the collection to the scalar column values of the table.
The full c# code was.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
private readonly static string _sql = "select * from vwLocks";
//the table definition property has been re-formated to fit on the page
//it should be on a single line
[Microsoft.SqlServer.Server.SqlFunction(
DataAccess=DataAccessKind.Read,
FillRowMethodName="Fill",
IsDeterministic=false,
Name="tvf_c_locks",
SystemDataAccess=SystemDataAccessKind.Read,
TableDefinition="request_mode nvarchar(5), request_status nvarchar(10),
resource_type nvarchar(15), resource_name nvarchar(20), resource_description nvarchar(25)")]
public static IEnumerable tvc_c_locks()
{
using (SqlConnection conn = new SqlConnection("context connection = true"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(_sql, conn))
{
using (SqlDataReader r = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
List result = new List
();
while (r.Read())
{
result.Add(new Row(
safeString(r, 0), safeString(r, 1),safeString(r, 2),
safeString(r, 3),safeString(r, 4)));
}
return result;
}
}
}
}
private static SqlString safeString(SqlDataReader reader, int index)
{
if (reader.IsDBNull(index))
{
return SqlString.Null;
}
return new SqlString(reader.GetString(index));
}
public static void Fill(object row, out SqlString request_mode, out SqlString request_status,
out SqlString resource_type, out SqlString resource_name, out SqlString resource_description)
{
Row r = row as Row;
request_mode = r._request_mode;
request_status = r._request_status;
resource_type = r._resource_type;
resource_name = r._resource_name;
resource_description = r._resource_description;
}
public class Row
{
public readonly SqlString _request_mode;
public readonly SqlString _request_status;
public readonly SqlString _resource_type;
public readonly SqlString _resource_name;
public readonly SqlString _resource_description;
public Row(SqlString request_mode, SqlString request_status,
SqlString resource_type, SqlString resource_name, SqlString resource_description)
{
_request_mode = request_mode;
_request_status = request_status;
_resource_type = resource_type;
_resource_name = resource_name;
_resource_description = resource_description;
}
}
};
With the udf deployed to SQL Server using Visual Studio I queried it as follows.
set transaction isolation level read committed
begin transaction
select data.col1,l.* from data outer apply dbo.tvf_c_locks() as l
commit
/*
col1 request_mode request_status resource_type resource_name resource_description
---- ------------ -------------- --------------- -------------------- -------------------------
a S GRANT DATABASE 0
a Sch-S GRANT METADATA 0 assembly_id = 6
a IS GRANT PAGE 72057594038321152 1:143
a Sch-S GRANT OBJECT tvf_c_locks
a IS GRANT OBJECT data
a IS GRANT OBJECT vwLocks
b S GRANT DATABASE 0
b Sch-S GRANT METADATA 0 assembly_id = 6
b IS GRANT PAGE 72057594038321152 1:154
b Sch-S GRANT OBJECT tvf_c_locks
b IS GRANT OBJECT data
b IS GRANT OBJECT vwLocks
*/
A number of new locks taken during the query to maintain the stability of the c# function. But the results show that when reading row “a” a Page level Intent Shared lock was taken on page 143 only; and when reading row “b” an IS lock was taken on page 154 only. This looks like the result I wanted; page level locking taken for the duration of the read from the page and not for the duration of the query.
If the page level locks were maintained I would expect to see the lock on page 143 also present when row “b” was read. Such as under a REPEATABLE READ transaction where the contents of page 143 must be protected from change for the duration of the query / transaction as shown below.
set transaction isolation level repeatable read
begin transaction
select data.col1,l.* from data outer apply dbo.tvf_c_locks() as l
commit
col1 request_mode request_status resource_type resource_name resource_description
---- ------------ -------------- --------------- -------------------- -------------------------
a S GRANT DATABASE 0
a Sch-S GRANT METADATA 0 assembly_id = 6
a IS GRANT PAGE 72057594038321152 1:143
a Sch-S GRANT OBJECT tvf_c_locks
a S GRANT KEY 72057594038321152 (6100bc414817)
a IS GRANT OBJECT data
a IS GRANT OBJECT vwLocks
b S GRANT DATABASE 0
b Sch-S GRANT METADATA 0 assembly_id = 6
b IS GRANT PAGE 72057594038321152 1:143
b IS GRANT PAGE 72057594038321152 1:154
b Sch-S GRANT OBJECT tvf_c_locks
b S GRANT KEY 72057594038321152 (6100bc414817)
b IS GRANT OBJECT data
b S GRANT KEY 72057594038321152 (62000610418e)
b IS GRANT OBJECT vwLocks
In these results the lock on page 143 is still present when reading row “b” from page 154. As seen in previous posts these locks will be maintained to the end of the transaction.