While there are many types of locks in SQL Server 2005 issues generally revolve around Shared (read), Exclusive (write) and Update (read now to write later) locks on resources. And given that the majority of operations on a database are read operations, the taking and holding of shared locks is often the centre of attention.
An Exclusive or Update lock taken during a transaction at any level of transaction isolation is held until the end of the transaction. Even under READ UNCOMMITTED isolation level where uncommitted rows from other transactions can be read. Shared locks however may be taken and released at different times during a query depending on the isolation level of the transaction.
Shared locks are taken for two reasons:
- First to implement ACID isolation from existing operations, they are taken to ensure that the rows are not currently being modified as part of an uncommitted transaction.
- Second to implement ACID isolation from future operations, locks are held for varying lengths of time to prevent rows from been modified another transaction.
I guess it’s really just one reason, as the holding of the locks impacts on the taking of the locks. But I find it easier to think of things in this way.
The sys.dm_tran_locks view shows all the locks held on a server; so I’ve created a view that filters the results by the connection ID to see what happening. The Lock modes page in Books Online discusses the various locks SQL Server uses and their Lock Compatibility.
I think this article post would be too long (to write and read) if I took a detailed look at the implications of the different locks taken. Hopefully I’ll find time soon to drill down into some of the situations and look at what concurrent operations are prohibited by the locks taken. The locks taken by the system will vary depending on the amount of data, its distribution, the operations performed and the load on the system. So the test scenario I have used below is deliberately simple and contrived as I just wanted to see what happened in the simplest case I could imagine; a single row in a single table with a clustered index.
You may also noticed I have not covered any of the snapshot isolation levels; again for simplicity I’ve stuck with the old pessimistic modes.
First to setup the test environment.
create database locks;
go
use locks;
go
create table data
( col1 char(1) primary key)
insert into
data
values
('a');
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.
The view vwLocks returns just the information I need for now to see what locks have been taken. To see the change in the locking levels I’ll:
- Begin a transaction
- View the locks before performing a read
- View the locks during a read by joining the data table and vwLocks
- View the locks after the read
- Commit the transaction
- View the locks after the transaction
All the queries look like the one below with the isolation level changed.
set transaction isolation level serializable begin transaction select * from vwLocks select * from data cross apply vwLocks select * from vwLocks commit select * from vwLocks
All the queries take a Share (S) lock on the Database to let others know they are using it. They also take varying locks on vwLocks to ensure its stability for the duration of the query.
SERIALIZABLE ISOLATION
Before the read, only the Database and vwLocks have locks taken on them. The transaction takes an Intent Shared (IX) lock on vwLocks to protect it during the query. I would have thought Shared (s) or Schema Stability (Sch-S) would be the lock of choice, so I’ll try to take a deeper look into this lock decision later.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
During the read the query takes an Intent Shared (IS) lock on the table (OBJECT, data); then on the page that contains the data (PAGE, 1:143) and finally a RANGES-S (R) lock over the PKID values (KEY). The locks are taken in that order as the intent locks are used to signal that the transaction may take a certain lock at a lower level.
According to the Lock Compatibility documentation an Intent Shared lock and an Intent Exclusive lock are compatible. As these locks can only be taken down to the Page level another lock is required to protect the rows read by the query.
The RANGES-S lock is a shared lock that protects a range of values in the key, the range is described by a hash value in the resource_description column. For a table with a clustered index the rows are locked through KEY locks rather then ROW locks. The RANGES-S lock could not be taken if there were any existing Update, Exclusive or Range modification lock on the key.
col1 request_mode request_status resource_type resource_name resource_description ---- ------------ -------------- --------------- -------------------- -------------------- a S GRANT DATABASE 0 a IS GRANT PAGE 72057594038321152 1:143 a RangeS-S GRANT KEY 72057594038321152 (6100bc414817) a IS GRANT OBJECT data a IS GRANT OBJECT vwLocks
After the read the same locks are in place with the addition of a new RANGES-S lock on the KEY. SERIALIZABLE isolation needs to protect against phantom reads; if I were to execute the same select again the in transaction it must return exactly the same rows (i.e. no new ones). Given the nature of my select this means no other rows can be added to the table until my transaction completes.
As the hash value “ffffffffffff” is used for the new RANGES-S lock on the table key it seems reasonable to assume this lock covers all possible key values. Preventing new rows been added to the table.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT PAGE 72057594038321152 1:143 RangeS-S GRANT KEY 72057594038321152 (6100bc414817) IS GRANT OBJECT data RangeS-S GRANT KEY 72057594038321152 (ffffffffffff) IS GRANT OBJECT vwLocks
After the transaction has committed we’re back to the same locks we had at the start.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
REPEATABLE READ ISOLATION
Before the read the same locks are taken as in SERIALIZABLE.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
During the read Intent Shared locks are taken on the table and then the page as in the SERIALIZABLE transaction. Once the query reaches the KEY object though it takes a Share lock rather than a Range lock. The resource_description for this lock is the same as the when the SERIALIZABLE transaction took a RangeS-S lock out on Key.
At the moment I don’t know why a Shared lock was chosen for the KEY rather than a Range lock. As you might expect though a Shared lock cannot be taken if there is any form of Update or Exclusive lock existing on the object.
col1 request_mode request_status resource_type resource_name resource_description ---- ------------ -------------- --------------- -------------------- -------------------- a S GRANT DATABASE 0 a IS GRANT PAGE 72057594038321152 1:143 a S GRANT KEY 72057594038321152 (6100bc414817) a IS GRANT OBJECT data a IS GRANT OBJECT vwLocks
After the read lock all of the same locks are in place. No new locks have been added.
REPEATABLE READ protects against the rows that were read in the original query been modified, so the Shared lock is maintained on the Key. It does not protect against new rows been added to the table that would then be returned in subsequent queries so a new RANGE lock is not added to prevent rows been added.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT PAGE 72057594038321152 1:143 S GRANT KEY 72057594038321152 (6100bc414817) IS GRANT OBJECT data IS GRANT OBJECT vwLocks
After committing the transaction we return to the initial same state.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
READ COMMITTED ISOLATION
Before the read the same locks are in place.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
During the read Intent Shared locks are taken on the table and the page, there is no locking on the key objects though.
READ COMMITTED must ensure that any rows been read have not been modified by an as yet uncommitted transaction, as does REPEATABLE READ and SERIALIZABLE. As I found above an Intent Shared lock can be taken if there is an existing Intent Exclusive lock, however it cannot be taken if there is an existing Update or Exclusive lock.
So my assumption here is that to modify a row a transaction must acquire an Update or Exclusive lock on the page. That would limit the granularity of the database to the page level, so I need to do some reading and testing to see what really happens.
Also, I think I read once the under READ COMMITTED the row / page locks taken to ensure the row does not have an uncomitted change are held only as long as it takes to read the data. Rather then been held for the entire duration of the query. I’ve added this to my list of additional things to look into; what would happen if the data was spread out over 2 pages?
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
After the read all locks taking during the read have been released. READ COMMITTED does not guarantee that a two identical reads during a transaction will return the same value, so locks do not need to be maintained for the duration of the transaction.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
After committing the transaction we return to the initial same state.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 IS GRANT OBJECT vwLocks
READ UNCOMMITTED ISOLATION
Before the read only a Schema Stability (Sch-S) lock is taken to protect the view that is to be read. My guess here is that weakest lock that can be taken to protect the view from been dropped while the query is executing. Again more research is needed here.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 Sch-S GRANT OBJECT vwLocks
During the read the transaction takes a Schema Stability lock on the table. Looking at the Lock Compatibility guide the lock that is incompatible with this is the Schema Modification (Sch-M) lock. Which means any existing Exclusive or Update locks held on the table by another transaction will not stop this transaction reading data from the table. This fits with the READ UNCOMMITTED mode of reading data that other transactions have yet to commit.
Also as the query does not takes locks any lower than the table level, it is effectively ignoring any other inflight transactions. Nor is it making any changes to the lock state of the database that would prevent any other transactions from reading or modifying data at the same time.
This is the not the greatest idea in the world. If a query uses multiple indexes on a table or some other mechanism that requires consistency between reads of the same table in the same Select it can experience 601 (”Could not continue scan with NOLOCK due to data movement.”) errors. These happen when say, data is read from a Non Clustered index and then a lookup is performed to obtain the rest of the row (using RID or PKID) however the row has been moved or deleted from the Heap or Clustered index before the read can complete. This scenario comes about because the READ UNCOMMITTED query does not use any locks during its query.
col1 request_mode request_status resource_type resource_name resource_description ---- ------------ -------------- --------------- -------------------- -------------------- a S GRANT DATABASE 0 a Sch-S GRANT OBJECT data a Sch-S GRANT OBJECT vwLocks
After the read no locks are held.
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 Sch-S GRANT OBJECT vwLocks
After the transaction is committed no locks are held
request_mode request_status resource_type resource_name resource_description ------------ -------------- --------------- -------------------- -------------------- S GRANT DATABASE 0 Sch-S GRANT OBJECT vwLocks
Hopefully this post is just the start of several where I drill down into the locking strategies used.
