SQLServer陷阱(四) 隐性锁

最新推荐文章于 2023-10-09 15:07:09 发布

hisi

最新推荐文章于 2023-10-09 15:07:09 发布

阅读量941

点赞数

本文链接：https://blog.csdn.net/hisi/article/details/2534805

版权

默认情况下select会使用共享锁, 在数据读取完后会立即释放; insert, update, delete会对更改的数据使用排它锁并且一直保持到事务结束. 共享锁可以与共享锁,更新锁共存. 排它锁与任何锁都不能共存.

例1: 新建一张表 create table table1 (id int), 加入一条记录 insert into table1 values(1) 在查询分析器中开两个连接连接1:

连接2:

先执行连接1, 再执行连接2, 都是立即完成. 然后连接1改为:

   
   
    
    
    
    

    
    while
    
     
    
    @@trancount
    
     
    
    >
    
     
    
    0
    
    
    
    
    rollback
    
     
    
    tran
    
     
    
    --
    
    如果有未提交的事务就回滚
    
    

    
    begin
    
     
    
    tran
    
    

    
    update
    
     table1 
    
    set
    
     id 
    
    =
    
     
    
    1

连接2不变先执行连接1, 再执行连接2, 会发现连接2被阻塞. 这时在连接1中单独执行commit tran, 连接2会立即结束. 注:在测试过程中可以用select @@spid查看当前连接的spid, 用sp_lock查看锁信息. 如果要改变隐性锁, 可以使用with关键字.

例2:

连接1:

   
   
    
    
    
    

    
    while
    
     
    
    @@trancount
    
     
    
    >
    
     
    
    0
    
    
    
    
    rollback
    
     
    
    tran
    
    

    
    begin
    
     
    
    tran
    
    

    
    select
    
     
    
    *
    
     
    
    from
    
     table1 
    
    with
    
    (
    
    holdlock
    
    )

连接2:

先执行连接1, 再执行连接2, 连接2会被阻塞. 因为holdlock改变了共享锁的生存期, 让共享锁保持到事务结束, 而共享锁与排它锁是不能共存的.

连接1改为:

   
   
    
    
    
    

    
    while
    
     
    
    @@trancount
    
     
    
    >
    
     
    
    0
    
    
    
    
    rollback
    
     
    
    tran
    
    

    
    begin
    
     
    
    tran
    
    

    
    select
    
     
    
    *
    
     
    
    from
    
     table1 
    
    with
    
    (tablockx)

先执行连接1, 再执行连接2, 连接2仍会被阻塞. 这里指定select使用表级排它锁, 该锁会保持到事务结束, 所以这里不用加holdlock.

例3:

连接1:

   
   
    
    
    
    

    
    while
    
     
    
    @@trancount
    
     
    
    >
    
     
    
    0
    
    
    
    
    rollback
    
     
    
    tran
    
    


    
    insert
    
     
    
    into
    
     table1 
    
    values
    
    (
    
    2
    
    )

    
    begin
    
     
    
    tran
    
    

    
    update
    
     table1 
    
    set
    
     id 
    
    =
    
     
    
    3
    
     
    
    where
    
     id 
    
    =
    
     
    
    2

连接2:

先执行连接1, 再执行连接2, 连接2会被阻塞. 连接2:

select top 1 * from table1

再执行连接2, 连接2会立即结束. 为了使锁定的成本减至最少,SQL Server 自动将资源锁定在适合任务的级别. 这里会使用行级锁. update的过程应该先是查询id=2的记录, 查询时使用共享锁, 不满足id=2的记录共享锁立即释放, 满足id=2的记录把共享锁升级为排它锁并保持到事务结束. 所以第一次执行连接2会因为无法获取id=2的记录的共享锁而被阻塞, 而第二次执行不会被阻塞.