SQL Server Insert操作中的锁

    首先我们建立一张表Table_1，它有两列Id(bigint)和Value(varchar)，其中Id建立了主键。

 CREATE   TABLE   [  dbo  ] . [  Table_2  ]  (
      [  Id  ]   [  bigint  ]   NOT   NULL  ,
      [  Value  ]   [  nchar  ] ( 10 )  NULL  ,
   CONSTRAINT   [  PK_Table_2  ]   PRIMARY   KEY   CLUSTERED   
(
      [  Id  ]   ASC  
)  WITH  (PAD_INDEX   =   OFF , STATISTICS_NORECOMPUTE   =   OFF , IGNORE_DUP_KEY  =   OFF , ALLOW_ROW_LOCKS   =   ON , ALLOW_PAGE_LOCKS   =   ON )  ON   [  PRIMARY  ]  
)   ON   [  PRIMARY  ] 

View Code

 

    然后插入两条数据。

 insert   into   dbo.table_2
(id, value)
  values  
(  1 ,  ‘  1  ‘  ),
(  2 ,  ‘  2  ‘ );

 

开始测试

    我们知道，在Transaction中共享锁在查询语句结束就释放了，而排它锁则在Transaction提交才释放。我们可以利用它来执行一个Insert，不提交Transaction，然后去查看锁的状态。注意，本文中查询窗口配置的Transaction隔离级别是默认值READ COMMITTED。

    首先执行以下SQL：

 begin   tran   t1

  insert   into   dbo.table_2
(id, value)
  values  
(  3 ,  ‘  3  ‘ );

    然后查看锁：

 SELECT   
    resource_type,
    request_mode,
    resource_description,
    request_session_id,
    request_status,
    resource_associated_entity_id,
      DB_NAME (resource_database_id) as   resource_database
  FROM  
    sys.dm_tran_locks
  WHERE  
    resource_type   <>   ‘  DATABASE  ‘ 
 ORDER   BY  
    request_session_id; 

    执行结果如下：

第一个是意向排他锁。它表示这个数据页下存在排他锁（就是第三个排他锁），我们发现它的resource_associated_entity_id和第三个锁一样。那么，这个数据页就是存放这行数据的这个主键的。 第二个也是意向排他。它的resource_type是OBJECT，此对象可以是数据表、视图、存储过程、扩展存储过程或任何具有对象 ID 的对象。它的resource_associated_entity_id这一列其实是object_id, 用函数object_name(object_id)看一下发现结果是Table_2。那么它下面存在的排他锁指的也是第三个锁了。 第三个是排他锁。resou_description指的是插入数据主键的哈希值。

补充1

    此时，我们在另外一个命令窗口中执行以下查询语句不会产生阻塞：

 SELECT   * 
 FROM   dbo.Table_2
  WHERE  id =  1  ; 

但另一条却会产生阻塞：

 SELECT   * 
 FROM   dbo.Table_2
  WHERE  id =  3 ;

来看看第一条SQL产生的锁。由于共享锁会在查询结束立即释放，因此我们加一个HOLDLOCK，让它在事务结束再释放：

 begin   tran   t2

  SELECT   * 
 FROM  dbo.Table_2  WITH ( HOLDLOCK  )
  WHERE  id =  1 ;

 

这是执行完以上语句锁的情况：

第二条SQL会产生阻塞，因此可以直接查询然后看锁的情况：

我们发现第9行的resource_description和第3行是相同的，这也说明了主键的锁只是锁住了某一个值而已。

补充2

这条SQL也会被Insert阻塞：

 SELECT  
    value
  FROM  
    dbo.Table_2
  WHERE  
    value  =  ‘  1  ‘ 

而且查看当前的锁可以发现，Key被锁的值正是Insert语句的Key值。这里有两个疑问：1. 为什么没用到主键列，却产生了主键锁。2.为什么Insert的数据还未commit，这里却会产生这一行主键的锁。

答：1. 我们查看查询计划，可以看到这条语句是用了聚集索引扫描，至于为什么不是表扫描，请看这里。 2. 由于事务隔离级别默认是Read Committed，所以这里会对已插入但未提交的数据主键加一个共享锁。

SQL Server Insert操作中的锁

标签：

查看更多关于SQL Server Insert操作中的锁的详细内容...