Update and Exclusive locks mostly are used for Data Manipulating Language delete and update. Fisrt of all, let’s have a quick glance below:
use tempdb
set nocount on
if OBJECT_ID('abc') is not null
drop table abc
create table abc(id int)
insert into abc values(1),(2),(3)
begin transaction
delete abc where id = 1
exec sp_lock @@spid
rollback transaction
/*
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
52 2 1365579903 0 RID 1:172:0 X GRANT
52 2 1365579903 0 PAG 1:172 IX GRANT
52 2 1365579903 0 TAB IX GRANT
52 1 439672614 0 TAB IS GRANT
*/
From result, you can ignore the line 17, the IS lock on that line on master database (dbid = 1) is look up values in the master database, like RID, PAG, TAB. While data being deleted, there’s on IX (Intent Exclusive) lock granted to the table (TAB), IX lock on the page where the records located, and X (eXclusive) lock on the row. After transaction is rolledback or committed, the lock on those resources will be release. Seem like there is nothing to the Update lock. That’s true if you use this tool or sys.dm_trans_locks to check the locking behaviour. Your will see more locks by using below.
For testing purpose, I created table SimpleTable with 4 records in 2 data pages. Every 8k SQL Server data page can only hold 2 records.
use master
set nocount on
if DB_ID('test') is not null
begin
alter database test set read_only with rollback immediate
drop database test
end
go
create database test;
go
alter database test set recovery simple
alter database test set auto_create_statistics off
alter database test set auto_update_statistics off
alter database test set allow_snapshot_isolation off
alter database test set read_committed_snapshot off
alter database tempdb set auto_create_statistics off
alter database tempdb set auto_update_statistics off
go
use test
create table SimpleTable(id int , text1 char(3000))
go
insert into SimpleTable
values (100, replicate('a',111)),
(200, replicate('b',111)),
(300, replicate('c',111)),
(400, replicate('d',111))
Let’s use Extended Events to monitor the locking behavious for deletion.
CREATE EVENT SESSION locks ON SERVER ADD EVENT sqlserver.lock_acquired, ADD EVENT sqlserver.lock_released ADD TARGET package0.asynchronous_file_target(set filename = 'c:\temp\locktest00') WITH (MAX_MEMORY = 4096KB,EVENT_RETENTION_MODE = NO_EVENT_LOSS ,MAX_DISPATCH_LATENCY = 1 SECONDS,MEMORY_PARTITION_MODE = NONE,TRACK_CAUSALITY = ON,STARTUP_STATE = OFF);
ALTER EVENT SESSION locks ON SERVER STATE = START
begin transaction
delete SimpleTable where id = 300
rollback
ALTER EVENT SESSION locks ON SERVER STATE = STOP
go
DROP EVENT SESSION locks ON SERVER
GO
declare @Temp table (ID int identity(1,1), EventData xml);insert into @Temp(EventData) select cast(event_data as xml) event_data from sys.fn_xe_file_target_read_file ('c:\temp\locktest00*', 'c:\temp\locktest00*.xem', null, null) a;with x as (select a.ID, x.value('../@name', 'varchar(128)') EventName, x.value('../@timestamp', 'datetime') EventTime, x.value('@name','varchar(128)') [Col], case when isnull(rtrim(x.value('(./text)[1]', 'varchar(128)')), '') = '' then x.value('(.)[1]', 'varchar(128)') else x.value('(./text)[1]', 'varchar(128)') end [Value] from @Temp a cross apply EventData.nodes('//data') ed(x)) select * from( select ID, cast(EventName as varchar(20)) as EventName, cast(resource_type as varchar(10)) as ResourceType, cast(mode as varchar(10)) Mode, cast(owner_type as varchar(12)) OwnerType, cast(database_id as int) DBID, cast(resource_0 as varchar(16)) Res0 , cast(resource_1 as varchar(16)) Res1, cast(resource_2 as varchar(16)) Res2, cast(lockspace_workspace_id as varchar(20)) lockspace_workspace_id, cast(lockspace_sub_id as varchar(10)) lockspace_sub_id, cast(lockspace_nest_id as varchar(10)) lockspace_nest_id, cast(transaction_id as int) TransID, EventTime from x pivot( MAX([Value]) for Col in ([resource_type], [mode], [owner_type], [transaction_id], [database_id], [lockspace_workspace_id], [lockspace_sub_id], [lockspace_nest_id], [resource_0], [resource_1], [resource_2])) pvt ) y where ResourceType not in ('METADATA') and Mode not in ('NL')
go
/*
ID EventName ResourceType Mode OwnerType DBID Res0 Res1 Res2 lockspace_workspace_id lockspace_sub_id lockspace_nest_id TransID EventTime
----------- -------------------- ------------ ---------- ------------ ----------- ---------------- ---------------- ---------------- ---------------------- ---------------- ----------------- ----------- -----------------------
2 lock_acquired OBJECT IX Transaction 13 2105058535 0 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
3 lock_acquired PAGE IU Transaction 13 153 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
4 lock_acquired RID U Transaction 13 153 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
5 lock_released RID U Transaction 13 153 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
6 lock_acquired RID U Transaction 13 153 65537 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
7 lock_released RID U Transaction 13 153 65537 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
8 lock_released PAGE IU Transaction 13 153 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
9 lock_acquired PAGE IU Transaction 13 155 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
10 lock_acquired RID U Transaction 13 155 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
11 lock_acquired PAGE IX Transaction 13 155 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
12 lock_acquired RID X Transaction 13 155 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
14 lock_acquired RID U Transaction 13 155 65537 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
15 lock_released RID U Transaction 13 155 65537 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
17 lock_released RID X Transaction 13 155 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
18 lock_released PAGE IX Transaction 13 155 1 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
19 lock_released OBJECT IX Transaction 13 2105058535 0 0 0x0000000080014200 1 1 910515 2011-10-13 06:23:33.210
*/
Let’s see how it works.
- Line 15: An IX lock is granted to the object, which is table SimpleTable.
- Line 16: An IU lock is granted to the first page, Res0 is page number, Res1 is file ID. Like the relationship between IS and S lock, an IU lock is applied to a page before U lock is granted to the row.
- Line 17: An Update lock is granted the first record of the table; Res1 in this case is the combination of file ID and record number in the page(the position in the slot array.). In SQL Server, Update lock is not compatible with Update lock on the same resource. If a U lock’s already granted to a record, it’s impossible that the same record have U lock granted by other sessions.
- Line 18: In this line, the U lock is release. Because, SQL server use applies U lock on the record, reads it, and finds out that not the record to be updated.
- Line 19: Like line 17, now SQL Server is going to check the second record in the page. Res1 is still a combination of file ID and record position. (See my previous post)
- Line 20: SQL Server figures that’s not the row to be modified. So it releases the U lock.
- Line 21: Since there are no more records to be checked in the first page, the IU lock is released.
- Line 22: SQL Server checks second page in this table, page 155. An IU lock is applied to the page
- Line 23: An U lock is granted to the first record in page 155. We knew that SQL Server is check whether the record is the one to be modified.
- Line 24: Yes, that’s the record. Then SQL Server converts the IU lock on the page to IX lock to let the page to be ready for modification
- Line 25: The U lock on the first record in page 155 gets converted to X lock. After the conversion is done, SQL Server deletes the data there.
- Line 26: After the row is removed, the next record in the page is check. SQL Server has to do a full table scan because there is no index on the table, that’s why the next record is checked. The lock for this operation is U lock.
- Line 27: We know the last record is id=400. So the lock is released. No modification needed
- Line 28, 29, 30: Now the transaction is rolled back. SQL Server starts releasing locks. It releases X lock on the record first, then IX lock on the page, then IX lock on the table
This is about deletion. What about update? You can try it out yourself. The locking behavior should be the same. You might have question that why SQL Server uses update lock to perform a search for deletion and update? I will explain it in my next post after elaborating the relationship among locks.
Hi John, The line numbers are not correct in the explanation. Can you please correct?