Oct 172011
 

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.

Share/Bookmark
Add Comment Register



 Leave a Reply

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="">

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: