mssql连接mysql update_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) WITHTABLERESULTS--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

--现在阻塞发生在哪个或哪些资源上

SELECTrequest_session_id

,resource_type

,request_status

,request_mode

,resource_description

,OBJECT_NAME(p.object_id) AS OBJECT_NAME,i.name index_nameFROMsys.dm_tran_locksLEFT JOINsys.partitions pON sys.dm_tran_locks.resource_associated_entity_id =p.hobt_idLEFT JOINsys.indexes iON p.object_id = i.[object_id]

AND p.index_id =i.index_idORDER BY request_session_id,resource_type

View Code