最近在学习【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锁
![](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
--隔离级别 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