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

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

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

4Design and use proper indexes for the DMLsMake 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]
GO

IF EXISTS ( SELECT  *
            FROM    sys.tables
            WHERE   name = 'Employee_Demo_BTree'
                    AND type = 'U' )
    DROP TABLE Employee_Demo_BTree
GO

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 )
    )
GO

 --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]
 GO

IF EXISTS ( SELECT  *
            FROM    sys.tables
            WHERE   name = 'Employee_Demo_Heap'
                    AND type = 'U' )
    DROP TABLE Employee_Demo_Heap
GO

--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 )
    )
GO

 --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]
 GO

--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
GO

--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
GO

USE [AdventureWorks]
GO

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
GO
SET STATISTICS PROFILE ON
GO

-- 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]
GO

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
GO

--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
GO

SET STATISTICS PROFILE ON
GO

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
GO
SET STATISTICS PROFILE ON
GO

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
GO

--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_Heapindex_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
GO

--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_BTreeindex_id=1)聚集索引,也是数据存放的地方。刚才做的update语句没有改到它的索引列,它只需把Title这个列的值改掉。所以在index1上,它只申请个X锁,每条记录一个。
 
  但是表格在Title上面有一个非聚集索引IX_Employee_ManagerID_Demo_BTreeindex_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
GO

--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
 GO

-- 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
 GO

--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:
 --1To 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.
   2Delete - 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
 GO

 -- 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
 GO

--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
*/