如何监视锁的申请、持有和释放:

  在着手分析、处理阻塞、死锁之前,首先要进行“监控”和“信息收集”

  1、检查一个连接当前所持有的锁:

  可以使用sp_lock来查看所有连接持有的锁的内容。

  在2005以后引入的DMV,还能用过sys.dm_tran_locks来查看:


SELECT  request_session_id,
        resource_type ,
        resource_associated_entity_id ,
        request_status ,
        request_mode ,
        resource_description
FROM    sys.dm_tran_locks


  也可以组合其他DMV查看更详细的信息:


SELECT  request_session_id,
        resource_type ,
        resource_associated_entity_id ,
        request_status ,
        request_mode ,
        resource_description ,
        p.object_id ,
        OBJECT_NAME(p.object_id) AS OBJECT_NAME ,
        p.*
FROM    sys.dm_tran_locks a
LEFT JOIN sys.partitions p ON a.resource_associated_entity_id = p.hobt_id
WHERE   resource_database_id= DB_ID('数据库名')
ORDER BY request_session_id,resource_type ,
resource_associated_entity_id


  2、监视语句执行过程中SQL Server对锁的申请和释放行为:

  由于有很多锁是在语句运行过程中申请和释放的。运行之后锁会消失,所以使用上面方式很难查询。此时使用SQL Server Profiler 来跟踪是比较好的方式:

  打开SQL Server Profiler→定义一个跟踪(trace),选取以下的Events(事件):Lock:Accquired,Lock:Released

  由于实际过程中申请锁的情况可能会非常复杂,所以建议只在测试环境针对特定语句进行。

  一般来说,使用DMV和sp_lock基本上能解决大部分问题。

  锁的数量和数据库调优的关系:

  一个常见的SELECT动作要申请的锁:

  (1)在连接A中,将事务隔离级别设成【可重复读】(repeatable read)

  (2)在运行查询前开启一个事务

  (3)运行查询语句,但不提交

  (4)在第二个连接里,查询sys.dm_tran_locks中分析查询结束以后连接A还持有的锁。

  一个常见的UPDATE动作要申请的锁:

  对于一个UPDATE操作,可以理解为先查询再修改。查询的过程先要添加S锁,找到数据后再添加U锁。后才把U锁升级到X锁。

  如果update操作借助了哪个索引,会在这个索引的键值上有U锁。没有用到的索引不加锁。真正修改的地方会有X锁。对于查询涉及的页面。SQL SERVER加了IU锁。修改发生的页面,加了IX锁。