On the Journey to be a SQL Server Professional: Locks needed for SELECT, UPDATE, DELETE and INSERT

Summary:

1. Select a proper Transaction Isolation Level: the higher, more locks. Use the default READ COMMITTED is generally recommended.

事务隔离级别的选定

事务隔离级别越高，隔离度就越高，并发度也就越差。如果选择了比较高的隔离级别，SQL 不可避免地要申请更多的锁，持有的时间也会增加。所以在设计应用的时候，一定要和用户谈好，尽量选择默认的隔离级别（read committed）

2. The length and complexity of the transaction - use simple transactions if possible

事务的长短和事务的复杂度

事务的长度和复杂度决定论这个事务在SQL内部会持续多长时间，也能决定SQL会同时在多少张表和索引上申请和持有锁。事务越简单，就越不容易发生阻塞和死锁。所以这也必须和用户商量好，尽量避免在一个事务里做很多事情

3. Make the affected number of rows as few as possible in a transaction if concurrency is in priority

从应用整体并发度考虑，单个事务一次处理的数据量不能过多

应用的性能，不单要衡量单个连接的处理速度，也要衡量在并发处理的情况下，整体的平均速度怎么样。从连接个体来讲，可能在一个事务里把数据一次都处理掉比较快但是如果处理的数据量很大，就会影响到其他连接同时访问同一对象。所以，如果一个应用的并发要求比较高，就一定要严格控制单个事务处理的数据量。如果有什么事务操作需要访问或修改表格内的大量数据，最好调整到并发用户比较少的时候运行

4、Design and use proper indexes for the DMLs: Make sure you have enough indexes but no more than needed.

针对语句在表格上设计合适的索引

合适的索引能使SQL在读取尽可能少的数据量的前提下，把需要处理的数据找到。如果没有合适的索引，SQL在做select,update,delete的时候，会申请比要处理的目标数据量多得多的锁，从而导致阻塞或死锁。这种情形可以通过加索引的方式提高并发度。同时，SQL在 update, insert, delete的时候，会对有关联的所有索引都做修改，在它们

上面申请锁。从这个角度讲，索引越多，产生的锁的数目也就越多，阻塞和死锁的几率也就会越高

所以数据库设计员需要做的，是要确保有足够的索引，防止语句做全表扫描，但是也要去掉那些对语句运行贡献不大的索引。不能随便往表格上加索引。

--The demo below uses the HumanResources.Employee in SQL Server 2005

--***Locks needed for Select in repeatable read*--

--Setup: let's create two tables with the same structure and data.

--The only difference is that one has a clustered index, another one is heap.

USE [AdventureWorks]

IF EXISTS ( SELECT *

FROM sys.tables

WHERE name = 'Employee_Demo_BTree'

AND type = 'U' )

DROP TABLE Employee_Demo_BTree

CREATE TABLE Employee_Demo_BTree

(

EmployeeID INT NOT NULL ,

NationalIDNumber NVARCHAR(15) NOT NULL ,

ContactID INT NOT NULL ,

LoginID NVARCHAR(256) NOT NULL ,

ManagerID INT NULL ,

Title NVARCHAR(50) NOT NULL ,

BirthDate DATETIME NOT NULL ,

MaritalStatus NCHAR(1) NOT NULL ,

Gender NCHAR(1) NOT NULL ,

HireDate DATETIME NOT NULL ,

ModifiedDate DATETIME

NOT NULL

DEFAULT GETDATE()

CONSTRAINT PK_Employee_EmployeeID_Demo_BTree

PRIMARY KEY CLUSTERED ( EmployeeID ASC )
)

--Add nonclustered indexes

CREATE NONCLUSTERED INDEX IX_Employee_ManagerID_Demo_BTree ON Employee_Demo_BTree([ManagerID] ASC)

CREATE NONCLUSTERED INDEX IX_Employee_ModifiedDatEmployee_Demo_BTree ON Employee_Demo_BTree( [ModifiedDate] ASC)

INSERT [dbo].[Employee_Demo_BTree]

SELECT [EmployeeID] ,

[NationalIDNumber] ,

[ContactID] ,

[LoginID] ,

[ManagerID] ,

[Title] ,

[BirthDate] ,

[MaritalStatus] ,

[Gender] ,

[HireDate] ,

[ModifiedDate]

FROM [HumanResources].[Employee]

IF EXISTS ( SELECT *

FROM sys.tables

WHERE name = 'Employee_Demo_Heap'

AND type = 'U' )

DROP TABLE Employee_Demo_Heap

--This table has 3 non-clustered indexed, no clustered index, thus, a heap table.

CREATE TABLE Employee_Demo_Heap

(

EmployeeID INT NOT NULL ,

NationalIDNumber NVARCHAR(15) NOT NULL ,

ContactID INT NOT NULL ,

LoginID NVARCHAR(256) NOT NULL ,

ManagerID INT NULL ,

Title NVARCHAR(50) NOT NULL ,

BirthDate DATETIME NOT NULL ,

MaritalStatus NCHAR(1) NOT NULL ,

Gender NCHAR(1) NOT NULL ,

HireDate DATETIME NOT NULL ,

ModifiedDate DATETIME

NOT NULL

DEFAULT GETDATE() --Note the PK is a nonclustered index. no cluster index on this table

CONSTRAINT PK_Employee_EmployeeID_Demo_Heap

PRIMARY KEY NONCLUSTERED ( EmployeeID ASC )

)

--Add nonclustered indexes

CREATE NONCLUSTERED INDEX IX_Employee_ManagerID_Demo_BTree ON Employee_Demo_Heap([ManagerID] ASC)

CREATE NONCLUSTERED INDEX IX_Employee_ModifiedDatEmployee_Demo_BTree ON Employee_Demo_Heap( [ModifiedDate] ASC)

INSERT [dbo].[Employee_Demo_Heap]

SELECT [EmployeeID] ,

[NationalIDNumber] ,

[ContactID] ,

[LoginID] ,

[ManagerID] ,

[Title] ,

[BirthDate] ,

[MaritalStatus] ,

[Gender] ,

[HireDate] ,

[ModifiedDate]

FROM [HumanResources].[Employee]

--Case 1: Query a table using clustered index in Repeatable Read

--Step 1. set the isolation level to repeatable read

/*--If you are curious the current xaction isolation level, run the code below:

SELECT CASE

WHEN transaction_isolation_level = 1

THEN 'READ UNCOMMITTED'

WHEN transaction_isolation_level = 2

AND is_read_committed_snapshot_on = 1

THEN 'READ COMMITTED SNAPSHOT'

WHEN transaction_isolation_level = 2

AND is_read_committed_snapshot_on = 0 THEN 'READ COMMITTED'

WHEN transaction_isolation_level = 3

THEN 'REPEATABLE READ'

WHEN transaction_isolation_level = 4

THEN 'SERIALIZABLE'

WHEN transaction_isolation_level = 5

THEN 'SNAPSHOT'

ELSE NULL

END AS TRANSACTION_ISOLATION_LEVEL

FROM sys.dm_exec_sessions AS s

CROSS JOIN sys.databases AS d

WHERE session_id = @@SPID

AND d.database_id = DB_ID();

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET STATISTICS PROFILE ON

--Step 2: start a transaction, run a query on the clustered table, but do not commit it

BEGIN TRAN

SELECT [EmployeeID] ,

[LoginID] ,

[Title]

FROM [dbo].[Employee_Demo_BTree]

WHERE [EmployeeID] IN ( 3, 30, 200 )

/*****notice clustered index is used for the query,

EmployeeID LoginID Title

3 adventure-works\roberto0 Engineering Manager

30 adventure-works\paula0 Human Resources Manager

200 adventure-works\hazem0 Quality Assurance Manager

STATISTICS PROFILE shows it is a clustered index seek.

3 1 SELECT [EmployeeID],[LoginID],[Title] FROM [dbo].[Employee_Demo_BTree] WHERE [EmployeeID] IN(3,30,200)

3 1 |--Clustered Index Seek(OBJECT:([AdventureWorks].[dbo].[Employee_Demo_BTree].[PK_Employee_EmployeeID_Demo_BTree]), ...

--Step 3: Open a NEW query window, run sys.dm_tran_locks to check the locks on the previous connection

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET STATISTICS PROFILE ON

USE [AdventureWorks]

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description] ,

OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Summary: Locks needed when querying a table using the clustered index in Repeatable Read.

(1) S lock on the three rows.

(2) S lock on the database to prevent dropping the database, and

(3) IS locks on the table and the row pages to prevent modifications.

51 --S lock on DB DATABASE GRANT S
51 --S lock on row1 KEY GRANT S (c8000ae6426d)

51 --S lock on row2 KEY GRANT S (1e004f007d6e)
51 --S lock on row3 KEY GRANT S (03000d8f0ecc)
51 --IS lock on Tbl OBJECT GRANT IS
51 --IS lock on p1 PAGE GRANT IS 1:21029

51 --IS lock on p2 PAGE GRANT IS 1:21035

53 --S lock on DB DATABASE GRANT S

for 2nd connection

--Case 2: Query the heap table using a nonclutered key in REPEATABLE READ

-- Step 1: first, let's rollback or commit the previous uncommitted transaction

ROLLBACK

-- Step 2: set the transaction isolation level to repeatable read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET STATISTICS PROFILE ON

-- Step 3: query a heap table
BEGIN TRAN

SELECT [EmployeeID] ,

[LoginID] ,

[Title]

FROM [dbo].[Employee_Demo_Heap]

WHERE [EmployeeID] = 3

/****Note: since EmployeeeID is a nonclustered index, SQL Server needs to find the row from the index.

Stmt Text

SELECT [EmployeeID],[LoginID],...

|--Nested Loops(Inner Join, OUTER REFERENCES:...

|--Index Seek(OBJECT:([AdventureWorks].[dbo].[Employee_Demo_Heap]...

|--RID Lookup(OBJECT:([AdventureWorks].[dbo].[Employee_Demo_Heap]), ...

--Step 4: Finally open a new query window, run sys.dm_tran_locks to check the locks

USE [AdventureWorks]

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description] ,

OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Summary: Locks needed when querying a heap table in Repeatable Read if a nonclustered key is used

(1) S lock on the data row and the nonclustered key.

(2) S lock on the database.

(3) IS locks on the table, the row page, and the nonclustered key page.

Notice the extra locks are needed for the RID and its page. Obviously, more keys or keys/RIDs are used, more locks are required.

Now the question is "Does SQL Server only lock the returned rows and their pages?"

The answer is: It depends. On a clustered table, may be yes, on a heap table, maybe not.

Let's demo the 'No' case in the next section.

51 --S on DB DATABASE GRANT S

51 --S on key KEY GRANT S (03000d8f0ecc)

51 --IS on tbl OBJECT GRANT IS
51 --IS on row pg PAGE GRANT IS 1:21042

51 --IS on key pg PAGE GRANT IS 1:21050

51 --S on row RID GRANT S 1:21042:2

53 --S on DB DATABASE GRANT S

for 2nd conn

--Case 3: Query the heap table using a table scan in REPEATABLE READ

--Step 1: first rollback or commit the previous tran

ROLLBACK

--Step 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET STATISTICS PROFILE ON

--Step 3: update a row in the HEAP table, but do not commit the change

BEGIN TRAN

UPDATE [dbo].[Employee_Demo_Heap]

SET [Title] = 'aaa'

WHERE [EmployeeID] = 70

--Step 4: run the code below in another window. The query should keep running...

--As the three rows are in separate pages, SQL Server decides to do a table scan, instead of nonclustered seek + RID lookup

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET STATISTICS PROFILE ON

BEGIN TRAN

SELECT [EmployeeID] ,

[LoginID] ,

[Title]

FROM [dbo].[Employee_Demo_Heap]

WHERE [EmployeeID] IN ( 3, 30, 200 )

--Step 5: Now check the locks in the previous window

SELECT [request_session_id] ,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description] ,

OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

---------For First Window------------

51--S on DB DATABASE GRANT S

51--U on the key KEY GRANT U (46000227c460)

51--IX on upt tbl OBJECT GRANT IX

51--IX on upt page PAGE GRANT IX 1:21044

51--IU on key page PAGE GRANT IU 1:21050

51--X on upt row RID GRANT X 1:21044:22

--------For 2nd Window, note waiting on ID=70 update finishing, ID=200 is never reached,---------------

54--S on DB DATABASE GRANT S

54 OBJECT GRANT IS

54 PAGE GRANT IS 1:21044

54 PAGE GRANT IS 1:21042

54--S on ID=3 RID GRANT S 1:21042:2

54--S on ID=30 RID GRANT S 1:21042:29

54--S on ID=70 RID WAIT S 1:21044:22

--Step 6: now let's rollback the update, after rollback, the X lock on ID=70 is released. ID=200 can be reached.

ROLLBACK

--Step 7: check the locks again in either window, you will see each page has an IS lock due to the page scan, even the returned rows are not on some pages.

SELECT [request_session_id] ,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description] ,

OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Summary: Locks needed when querying a heap table in Repeatable Read if a table scan is used.

(1) S lock on the rows

(2) S lock on the database

(3) IS locks on the table and all of the data pages.

51--S on DB DATABASE GRANT S

54--S on DB DATABASE GRANT S

54--S on tbl OBJECT GRANT IS

54 PAGE GRANT IS 1:21047

54 PAGE GRANT IS 1:21046

54 PAGE GRANT IS 1:21045

54 PAGE GRANT IS 1:21044

54 PAGE GRANT IS 1:21042

54 PAGE GRANT IS 1:21049

54 PAGE GRANT IS 1:21048

54--S on ID=3 RID GRANT S 1:21042:2

54--S on ID=30 RID GRANT S 1:21042:29

54--S on ID=200 RID GRANT S 1:21047:14 */

/*
-- Make sure the total page is 7 for this table

DBCC CHECKTABLE ('Employee_Demo_Heap')

DBCC results for 'Employee_Demo_Heap'.

There are 290 rows in 7 pages for object "Employee_Demo_Heap".

--Case 4: Query the table with a clustered index in REPEATABLE READ when the update rows and query rows are not overlapping.

--However, if we do the update on the clustered table, it's quite different.

--Step 1: rollback any open tran.
ROLLBACK

--Step 2: first update a row in the clustered table, but do not commit the change.

BEGIN TRAN

UPDATE [dbo].[Employee_Demo_BTree]

SET [Title] = 'aaa'

WHERE [EmployeeID] = 70

--Step 3: run the code below in another window. The query should be done quickly with the 3 rows return

--This is because the X lock is on ID=70, nothing to do with the clustered index seek on ID= 3, 30, 200.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SET STATISTICS PROFILE ON

BEGIN TRAN

SELECT [EmployeeID] ,

[LoginID] ,

[Title]

FROM [dbo].[Employee_Demo_BTree]

WHERE [EmployeeID] IN ( 3, 30, 200 )

/***Much more efficient, only locks whatever needed at the minimum

---the 1st window---

51 DATABASE GRANT S NULL --the database

51 KEY GRANT X (46000227c460) 1 -- the PK

51 OBJECT GRANT IX NULL -- the table

51 PAGE GRANT IX 1:21040 1 -- the PK page

---the 2ns window---

53 DATABASE GRANT S NULL

53 KEY GRANT S (c8000ae6426d) 1 -- ID=3

53 KEY GRANT S (1e004f007d6e) 1 -- ID=30

53 KEY GRANT S (03000d8f0ecc) 1 -- ID=200

53 OBJECT GRANT IS NULL

53 PAGE GRANT IS 1:21089 1 -- key page 1

53 PAGE GRANT IS 1:21088 1 -- key page 2

--****Locks needed for UPDATE in Repeatable Reads****--

--Case 1: Locks needed when updating a non-indexed columns in a heap table in Repeatable Read.

/* 对于update语句，可以简单理解为SQL先做查询，把需要修改的记录给找到，然后在这个记录上做修改。找记录的动作要加S锁，找到修改的记录后加U锁，再将U锁升级为X锁。*/

--Step 1: First rollback

Rollback

--Step 2: Set the T.I.L to REPEATABLE READ

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--Step 3: Update the table

BEGIN TRAN

UPDATE [dbo].[Employee_Demo_Heap]

SET [Title]='changeheap'

WHERE [EmployeeID] IN(3,30,200)

--Step 4: check the locks

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description]

-- OBJECT_NAME(p.[object_id]) AS objectname ,

-- p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Summary: Locks needed when updating a non-key columns in a heap table in Repeatable Read.

(1) S lock on the database

(2) X lock on the rows it modifies

(3) U lock on the every index it uses

(4) IX on the table, the pages containing the modified rows, and

(5) IU on the pages used in the query but not being modified. The more pages scanned, the more I (intention) locks. SQL Server will put I lock on all of the scanned pages although no changes on the pages.

Implications:

(1) Update only what needed as more udpates, more locks

(2) Use only indexes needed as more indexes, more locks

(3) Minimize the use of table scan and maximize use of index seek

这个update语句在非聚集索引上申请了个U锁，在RID上申请了个X锁。这是因为语句借助非聚集索引PK_Employee_EmployeeID_Demo_Heap（index_id是）找到了这条记录。非聚集索引PK_Employee_EmployeeID_Demo_Heap本身没有用到Title这一列，所以他自己不需要做修改。但是数据RID上有了修改，所以RID上加的是X锁，其他索引上没有加锁。

从这个例子可以看出，如果update借助了哪个索引，这个索引的键值上就会有U锁,没有用到的索引上没有锁。真正修改发生的地方会有X锁。对于查询涉及的页面，SQL加了IU锁意向更新锁，修改发生的页面，SQL加了IX锁意向排他锁。

51 DATABASE GRANT S

51 KEY GRANT U (c8000ae6426d)

51 KEY GRANT U (1e004f007d6e)

51 KEY GRANT U (03000d8f0ecc)

51 OBJECT GRANT IX

51 PAGE GRANT IX 1:21091

51 PAGE GRANT IX 1:21098

51 PAGE GRANT IU 1:21075

51 RID GRANT X 1:21098:2

51 RID GRANT X 1:21098:29

51 RID GRANT X 1:21091:14 */

--What if there is an index on the column for update?

--Case 2: Locks needed when updating a nonclustered index column in a table with clustered index in Repeatable Read.

--Step 1: Let's create a nonclustered index for the title column

--Step 2: rollback any uncommitted trans

ROLLBACK

--Step 3: Create the non-clustered index

ROLLBACK

CREATE NONCLUSTERED INDEX [Employee_Demo_BTree_Title] ON [AdventureWorks].[dbo].[Employee_Demo_BTree]

([Title] ASC)

--Step 4

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--Step 5

BEGIN TRAN

UPDATE [dbo].[Employee_Demo_BTree]

SET [Title]='changeheap'

WHERE [EmployeeID] IN(3,30,200)

--Step 6: use the script to check the locks

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description],

-- OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/****Locks needed when updating a nonclustered index column in a table with clustered index in Repeatable Read.

(1) S lock on the DB

(2) X lock on the three clustered index values as they directly link to the data being modified

(3) X on the nonclustered index values, 3 old and 3 new values.

(4) IX on the table and the pages containing the keys used in the DML.

PK_Employee_EmployeeID_Demo_BTree（index_id=1）聚集索引，也是数据存放的地方。刚才做的update语句没有改到它的索引列，它只需把Title这个列的值改掉。所以在index1上，它只申请个X锁，每条记录一个。

但是表格在Title上面有一个非聚集索引IX_Employee_ManagerID_Demo_BTree（index_id=4）,并且Title是第一列。它被修改后，原来的索引键值就要被删除掉，并且插入新的键值。所以在index_id=4 上要申请个X锁，老的键值3个，新的键值3个。

51 DATABASE GRANT S NULL

51 KEY GRANT X (c8000ae6426d) 1 -- data change here

51 KEY GRANT X (e905f8e58673) 4 -- old nonClusered key

51 KEY GRANT X (f701c05dbd43) 4 -- old nonClusered key

51 KEY GRANT X (e5042a644b4c) 4 -- old nonClusered key

51 KEY GRANT X (03000d8f0ecc) 1 -- data change here

51 KEY GRANT X (120282d2cee1) 4 -- new nonClusered key

51 KEY GRANT X (bc02c734f1e2) 4 -- new nonClusered key

51 KEY GRANT X (1e004f007d6e) 1 -- data change here

51 KEY GRANT X (e603119c695f) 4 -- new nonClusered key

51 OBJECT GRANT IX NULL --the table

51 PAGE GRANT IX 1:21089 1 -- the page for clustered

51 PAGE GRANT IX 1:21088 1 -- the page for clustered

51 PAGE GRANT IX 1:21101 4 -- the page for nonclustered

51 PAGE GRANT IX 1:21084 4 -- the page for NCL

--****Locks needed for DELETE in Read Committed****--

--Case 1: Delete rows from a table with clustered index

--Step 1: Rollback any uncommitted trans

ROLLBACK

--Step 2

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

--Step 3

BEGIN TRAN

DELETE [dbo].[Employee_Demo_BTree]

WHERE [LoginID]='adventure-works\kim1'

--Step 4: use the script to check the locks

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description],

-- OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Locks needed for deleting rows from a table with clustered index.

(1) S lock on the DB

(2) X lock on the clustered index values as it directly links to the data being modified

(3) X on the three nonclustered index values related to this row.

(4) IX on the table and the pages containing the row and the keys used in the DML.

The deletion starts with a clustered index scan to find the row to be deleted.

After the row is found, the row will be deleted. The clustered index and the three nonclustered for the row will be deleted as well, thus, 4 X locks. The IX locks are for the affected pages and table.

51 DATABASE GRANT S NULL

51 KEY GRANT X (20012bfb89f5) 3

51 KEY GRANT X (eb00dae6c5df) 1

51 KEY GRANT X (fb0084d718e7) 2

51 KEY GRANT X (e90558ba8911) 4

51 OBJECT GRANT IX NULL

51 PAGE GRANT IX 1:21041 2

51 PAGE GRANT IX 1:21101 4

51 PAGE GRANT IX 1:21097 1

51 PAGE GRANT IX 1:21081 3

-- What if we delete a row in a heap table?

--Case 2: Delete rows from a heap table when a table scan is used to locate the row in READ COMMITTED.

-- Step 1: let's first rollback the previous transaction

ROLLBACK

-- Step 2: Let's first see what happens in READ COMMITTED

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Step 3

BEGIN TRAN

DELETE [dbo].[Employee_Demo_Heap]

WHERE [LoginID]='adventure-works\tete0'

-- Step 4: use the script to check the locks

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description],

-- OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/**** Locks needed when deleting rows from a heap table when a table scan is used to locate the row in READ COMMITTED.

(1) X lock on the RID

(2) X lock on the non-clustered indexes

(3) IX on the pages for the row and NCL keys to be deleted.

51 DATABASE GRANT S NULL

51 KEY GRANT X (1f001d0d03d7) 2 --the nonclustered to be deleted

51 KEY GRANT X (6900c9a65654) 4 --the nonclustered to be deleted

51 KEY GRANT X (4000b0a57623) 3 --the nonclustered to be deleted

51 OBJECT GRANT IX NULL

51 PAGE GRANT IX 1:21092 3 --the page for the row

51 PAGE GRANT IX 1:21075 2 --the page for the row

51 PAGE GRANT IX 1:21044 4 --the page for the row

51 PAGE GRANT IX 1:21043 0 --the page for the row

51 RID GRANT X 1:21043:4 0 --the row to be deleted

--Now let's see what happens in Repeatable READ

--Case 3: Delete rows from a heap table when a table scan is used to locate the row in REPEATABLE READ.

--Step 1

ROLLBACK

--Step 2

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--Step 3

BEGIN TRAN

DELETE [dbo].[Employee_Demo_Heap]

WHERE [LoginID]='adventure-works\tete0'

-- Step 4: use the script to check the locks

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description],

-- OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Locks needed when deleting rows from a heap table when a table scan is used to locate the row in REPEATABLE READ.

(1) X lock on the RID

(2) X lock on the non-clustered keys

(3) IX on the pages for the row and NCL keys to be deleted

(4) IU on all of the other pages scanned

Notice SQL Server puts IU lock on all of the pages in the table that are scanned but not modified.

Implications:

--（1）To delete a row, you must first find it. Thus, you need to use appropriate index to find the row(s), avoid to use table scan.

（2）Delete - not only for the data, but for the keys as well. so fewer keys, the better

(3) So keys are good, but no more, no less.

(4) The higher of transaction isolation level, the more locks.

51 DATABASE GRANT S NULL

51 KEY GRANT X (6900c9a65654) 4 -- the nonclusered key

51 KEY GRANT X (4000b0a57623) 3 -- the nonclusered key

51 KEY GRANT X (1f001d0d03d7) 2 -- the nonclusered key

51 OBJECT GRANT IX NULL -- the table

51 PAGE GRANT IX 1:21044 4 -- the nonclusered key page

51 PAGE GRANT IX 1:21043 0 -- the page for the deleted row

51 PAGE GRANT IU 1:21042 0 -- the other row page 1

51 PAGE GRANT IX 1:21092 3 -- the nonclusered key page

51 PAGE GRANT IU 1:21091 0 -- the other row page 2

51 PAGE GRANT IU 1:21099 0 -- the other row page 3

51 PAGE GRANT IU 1:21098 0 -- the other row page 4

51 PAGE GRANT IX 1:21075 2 -- the nonclusered key page

51 PAGE GRANT IU 1:21074 0 -- the other row page 5

51 PAGE GRANT IU 1:21082 0 -- the other row page 6

51 RID GRANT X 1:21043:4 0 -- the row to be deleted

--****Locks needed for an Insert ****--

相对于select,update,delete，单条记录的insert操作对锁的申请比较简单。SQL会为新插入的数据本身申请一个X锁，在发生变化的页面上申请一个IX锁。由于这条记录是新插入的，被其他连接引用到的概率会相对小一些，所以出现阻塞的几率也要小

--Case 1: Insert data into the heap table in REPEATABLE READ

-- Step 1

ROLLBACK

-- Step 2

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

-- Step 3

BEGIN TRAN

INSERT INTO [dbo].[Employee_Demo_Heap]

VALUES ( 501, 480168528, 1009, 'adventure-works\thierry0', 263,

'Tool Desinger', '1949-08-29 00:00:00.000', 'M', 'M',

'1998-01-11 00:00:00.000', '2004-07-31 00:00:00.000' )

--Step 4

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description] ,

-- OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/***Locks needed when inserting data into a heap table in REPEATABLE READ

(1) S on the database

(2) IX on the table

(3) X on the keys

(4) IX on the key pages

(5) X on the RID in the heap table

51 DATABASE GRANT S NULL

51 KEY GRANT X (f50041acc16e) 2 -- the nonclustered key

51 KEY GRANT X (6900c5e9e3f8) 4 -- the nonclustered key

51 KEY GRANT X (3b00b24e51fe) 3 -- the nonclustered key

51 OBJECT GRANT IX NULL

51 PAGE GRANT IX 1:21044 4 -- the nonclustered key page

51 PAGE GRANT IX 1:21043 0 -- the row page

51 PAGE GRANT IX 1:21092 3 -- the nonclustered key page

51 PAGE GRANT IX 1:21075 2 -- the nonclustered key page

51 RID GRANT X 1:21043:8 0 --the row inserted

--Case 2: Insert data into a table with clustered index in REPEATABLE READ

--Step 1: rollback the previous transaction

ROLLBACK

--Step 2

USE [AdventureWorks]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

--Step 3

BEGIN TRAN

INSERT INTO [dbo].[Employee_Demo_BTree]

VALUES ( 501, 480168528, 1009, 'adventure-works\thierry0', 263,

'Tool Desinger', '1949-08-29 00:00:00.000', 'M', 'M',

'1998-01-11 00:00:00.000', '2004-07-31 00:00:00.000' )

-- Step 4: use the script to check the locks

SELECT [request_session_id] ,

--c.[program_name],

--DB_NAME(c.[dbid]) AS dbname,

[resource_type] ,

[request_status] ,

[request_mode] ,

[resource_description],

-- OBJECT_NAME(p.[object_id]) AS objectname ,

p.[index_id]

FROM sys.[dm_tran_locks] AS a

LEFT JOIN sys.[partitions] AS p ON a.[resource_associated_entity_id] = p.[hobt_id]

LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id] = c.[spid]

WHERE c.[dbid] = DB_ID('AdventureWorks')

ORDER BY [request_session_id] ,

[resource_type]

/****Locks needed when inserting data into a table with clustered in REPEATABLE READ

(1) S on the database

(2) IX on the table

(3) X on the keys

(4) IX on the key pages

51 DATABASE GRANT S NULL

51 KEY GRANT X (7f034d768a29) 4 --the non-clustered key

51 KEY GRANT X (f50041acc16e) 1 --the PK

51 KEY GRANT X (2a01b0b18d44) 3 --the non-clustered key

51 KEY GRANT X (fc0099ae1586) 2 --the non-clustered key

51 OBJECT GRANT IX NULL

51 PAGE GRANT IX 1:21041 2 --the non-clustered key page

51 PAGE GRANT IX 1:21030 1 --the non-clustered key page

51 PAGE GRANT IX 1:21077 4 --the non-clustered key page

51 PAGE GRANT IX 1:21081 3 --the non-clustered key page