MSSQL-SELECT&UPDATE动作要申请的锁

最近在学习【MySQL事务&锁】这块知识，一不留神和MSSQL乱窜了~.~

文章最初是想查看MySQL vs MSSQL在下面环境产生的阻塞现象
会话1开启事务更新数据尚未提交->会话2读取会话1中修改的行->会话3读取会话1中修改的行
MySQL：会话1阻塞会话2，会话1阻塞会话3
MSSQL：会话1阻塞会话2，会话2阻塞会话3
MySQL同学对MSSQL的这种阻塞链表示很不理解！我们查看MSSQL中的锁等待信息，会话2和会话3等待的是同一资源，为什么sys.sysprocesses中显示会话2阻塞会话3呢？
我也不知道，也不想去深挖。还是先把MSSQL中简单SELECT/UPDATE/DELETE操作要申请的锁梳理清楚~
【Microsoft SQL Server企业级平台管理实践】的第9章和第10章详细介绍了阻塞与死锁，建议阅读原文

使用SQL Server Profiler跟踪Lock:Acquired、Lock:Released事件，查看语句执行过程中锁的申请和释放过程
使用sp_lock或者sys.dm_tran_locks罗列连接持有的锁信息
下面的分析基于REPEATABLE READ隔离级别，显式开启事务，执行SELECT/UPDATE操作，且不提交事务
一、堆表
从第一行开始，依次扫描表中的每一行数据，然后检查这一行是不是用户需要的数据，直到最后一行
1.1、SELECT
Profiler跟踪看到的过程如下：
OBJECT申请IS锁->PAGE申请IS锁->RID申请S锁->不是用户所需数据/是用户所需数据->释放当前RID上的S锁/不释放当前RID上的S锁->下一个RID...->下一个PAGE...
注意：扫描过程会对读到的每一条记录加上一个S锁(若记录不返回，在读完这条记录就释放)。只要有任何一条记录上的锁没申请到，查询就会被阻塞
加锁情况(sp_lock)：表级别加IS锁；所有PAGE加IS锁；返回行加S锁
1.2、UPDATE
Profiler跟踪看到的过程如下：
OBJECT申请IX锁->PAGE申请IU锁->RID申请U锁->不是用户所需数据/是用户所需数据->释放当前RID上的U锁/PAGE升级成IX锁，RID升级成X锁->下一个RID...->下一个PAGE...
对于UPDATE语句，可简单理解先做查询，把需要修改的记录给找到，然后在这个记录上做修改。找记录的动作要加S锁，找到要修改的记录后会先加U锁，再将U锁升级成X锁
加锁情况(sp_lock)：表级别加IX锁；修改行加X锁、修改行所在PAGE加IX锁；其他PAGE加IU
二、非聚集索引
这里是指执行计划使用了非聚集索引
2.1、SELECT
• 返回列在索引中
索引覆盖了请求所包含的列，不需要访问表仅需要访问索引本身
加锁情况(sp_lock)：表级别加IS锁；返回行对应索引键值加S锁、返回行对应索引键值所在PAGE加IS锁
• 返回列不在索引中
使用索引键值去访问非聚集索引，然后使用书签访问非聚集索引所在表获取额外的信息
加锁情况(sp_lock)：表级别加IS锁；返回行对应索引键值加S锁、返回行对应索引键值所在PAGE加IS锁；返回行加S锁、返回行所在PAGE加IS锁
2.2、UPDATE
• 更新非索引列
加锁情况(sp_lock)：表级别加IX锁；修改行加X锁、修改行所在PAGE加IX锁；修改行对应索引键值加U锁、修改行对应索引键值所在PAGE加IU锁
• 更新索引列
加锁情况(sp_lock)：表级别加IX锁；修改行加X锁、修改行所在PAGE加IX锁；原索引键值要删除，新索引键值要插入，对应两个KEY加X锁、索引键值所在PAGE加IX锁
三、聚集索引
3.1、SELECT
加锁情况(sp_lock)：表级别加IS锁；返回行加S锁；返回行所在PAGE加IS锁
3.2、UPDATE
• 更新非索引列
加锁情况(sp_lock)：表级别加IX锁；修改行加X锁；修改行所在PAGE加IX锁
• 更新索引列
加锁情况(sp_lock)：表级别加IX锁；修改行原键值要删除，新键值要插入，对应两个KEY加X锁；修改行所在PAGE加IX锁

--隔离级别
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

--PAGE
DBCC IND(AdventureWorks2008R2,'Employee_Demo',-1)
DBCC IND(7,981578535,1)
DBCC TRACEON (3604)
DBCC PAGE(7,1,3792,3) WITH TABLERESULTS
--KeyHashValue
SELECT * FROM Employee_Demo WHERE %%lockres%% ='(a0c936a3c965)'

--有没有阻塞发生？是什么时候发生的？在哪个数据库上？
SELECT spid,kpid,blocked,waittime,s.waitresource,dbid,s.last_batch,status FROM sys.sysprocesses s WHERE spid>50
--现在阻塞发生在哪个或哪些资源上
SELECT request_session_id
      ,resource_type
      ,request_status
      ,request_mode
      ,resource_description
      ,OBJECT_NAME(p.object_id) AS OBJECT_NAME
      ,i.name index_name
FROM   sys.dm_tran_locks
       LEFT JOIN sys.partitions p
            ON  sys.dm_tran_locks.resource_associated_entity_id = p.hobt_id
       LEFT JOIN sys.indexes i
            ON  p.object_id = i.[object_id]
                AND p.index_id = i.index_id
ORDER BY request_session_id,resource_type

View Code