Sql Server事务+隔离级别+阻塞+死锁

背景

分析事务的执行过程

写在前面

摘录自https://www.cnblogs.com/jackson0714/p/TSQLFundamentals_08.html
link

分析当前查询中的锁

sys.dm_tran_locks 视图

SELECT  request_session_id AS 会话id ,
        resource_type AS 请求锁定的资源类型 ,
        resource_description AS 描述 ,
        request_mode AS 模式 ,
        request_status AS 状态
FROM    sys.dm_tran_locks

分析阻塞
1.sys.dm_tran_locks 视图
（1）该动态视图可以查询出哪些资源被哪个进程ID锁了
（2）查询出对资源授予或正在等待的锁模式
（3）查询出被锁定资源的类型

sys.dm_exec_connections 视图

SELECT  session_id ,
        connect_time ,
        last_read ,
        last_write ,
        most_recent_sql_handle
FROM    sys.dm_exec_connections
 
WHERE   session_id IN ( 55 )

2.sys.dm_exec_connections 视图
（1）查询出该动态视图可以查询出进程相关的信息
（2）查询出最后一次发生读操作和写操作的时间last_read,last_write
（3）查询出进程执行的最后一个SQL批处理的二进制标记most_recent_sql_handle

sys.dm_exec_sql_text 表函数

SELECT  session_id ,
        text
FROM    sys.dm_exec_connections
        CROSS APPLY sys.dm_exec_sql_text
        (most_recent_sql_handle) AS ST
WHERE   session_id IN ( 55 )

3.sys.dm_exec_sql_text 表函数
（1）该函数可以将二进制标记most_recent_sql_handle作为参数，然后返回SQL代码。
（2）阻塞进程在不断地运行，所以在代码中看到的最后一个操作不一定是导致问题的语句。在本例中最后一条执行语句是导致阻塞的语句。

sys.dm_exec_sessions 视图

SELECT * FROM sys.dm_exec_sessions where session_id = 55

4.sys.dm_exec_sessions 视图
（1）会话建立的时间login_time
（2）特定于会话的客户端工作站名称host_name
（3）初始化会话的客户端程序的名称program_name
（4）会话所使用的SQL Server登录名login_name
（5）最近一次会话请求的开始时间last_request_start_time
（6）最近一次会话请求的完成时间last_request_end_time

sys.dm_exec_requests 视图

SELECT * FROM sys.dm_exec_requests where session_id = 55

5.sys.dm_exec_requests 视图
（1）识别出阻塞链涉及到的会话、争用的资源、被阻塞会话等待了多长时间

Lock_TIMEOUT 选项

6.Lock_TIMEOUT 选项
（1）设置会话等待锁释放的超时期限
（2）默认情况下会话不会设置等待锁释放的超时期限
（3）设置会话超时期限为5秒， SET Lock_TIMEOUT 5000
（4）锁定如果超时，不会引发事务回滚
（5）取消会话超时锁定的设置，SET LOCK_TIMEOUT -1

KILL 命令

（1）杀掉会话52，KILL 52
（2）杀掉会话，会引起事务回滚，同时释放排他锁

隔离级别

已提交读（READ COMMITTED）（默认值）

（1）未提交读 （READ UNCOMMITTED）
（2）已提交读（READ COMMITTED）（默认值）
（3）可重复读（REPEATABLE READ）
（4）可序列化（SERIALIZABLE）
（5）快照（SNAPSHOT）
（6）已经提交读快照（READ_COMMITTED_SNAPSHOT）

设置整个会话的隔离级别

SET TRANSACTION ISOLATION LEVEL <isolation name>;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

用表提示设置查询的隔离级别

SELECT ... FROM <table> WITH (<isolation name>);<br>
SELECT * FROM dbo.myProduct WITH (READCOMMITTED);

注意：
1.设置会话选项的隔离级别时，隔离级别中的每个单词之间需要用空格分隔
2.用表提示的隔离级别时，隔离级别中的每个单词之间不需要用空格分隔
3.表提示的隔离级别有同义词，如：NOLOCK->READUNCOMMITTED,HOLDLOCK->REPEATABLEREAD
4.隔离级别的严格性：1.未提交读<2.已提交读<3.可重复读<4.可序列化
5.隔离级别越高，一致性越高，并发性越低
6.基于快照的隔离级别，SQL Server将提交过的行保存到tempdb数据库中，当读操作发现行的当前版本和它们预期的不一致时，可以立即得到行的以前版本，从而不用请求共享锁也能取得预期的一致性。

表提示的隔离级别有同义词，如：NOLOCK->READUNCOMMITTED,HOLDLOCK->REPEATABLEREAD

1. 未提交读 （READ UNCOMMITTED）,隔离级别的含义：
（1）读操作可以读取未提交的修改（也称为脏读）。
（2）读操作不会妨碍写操作请求排他锁，其他事务正在进行读操作时，
	写操作可以同时对这些数据进行修改。
（3）事务A进行了多次修改，事务B在不同阶段进行查询时可能会有不同的结果。

2.已提交读（READ COMMITTED）（默认值）
（1）必须获得共享锁才能进行读操作，其他事务如果对该资源持有排他锁，则共享锁必须等待排他锁释放。
（2）读操作不能读取未提交的修改，读操作读取到的数据是提交过的修改。
（3）读操作不会在事务持续期间内保留共享锁，
	其他事务可以在两个读操作之间更改数据资源，
	读操作因而可能每次得到不同的取值。这种现象称为“不可重复读”

3.可重复读 （REPEATABLE READ）,隔离级别的含义：
（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。
（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，
	这样可以保证实现可重复的读取。
（3）两个事务在第一次读操作之后都将保留它们获得的共享锁，
	所以任何一个事务都不能获得为了更新数据而需要的排他锁，
	这种情况将会导致死锁（deadlock），不过却避免了更新冲突。

可重复读 （REPEATABLE READ）,将会导致死锁（deadlock），不过却避免了更新冲突。

4.可序列化（SERIALIZABLE）,隔离级别的含义：
（1）必须获得共享锁才能进行读操作，获得的共享锁将一直保持直到事务完成之止。
（2）在获得共享锁的事务完成之前，没有其他事务能够获得排他锁修改这一数据资源，
	且当其他事务增加能够满足当前事务的读操作的查询搜索条件的新行时，其他事务将会被阻塞，
	直到当前事务完成然后释放共享锁，其他事务才能获得排他锁进行插入操作。
（3）事务中的读操作在任何情况下读取到的数据是一致的，不会出现幻影行（幻读）。
（4）范围锁：读操作锁定满足查询搜索条件范围的锁

隔离级别总结

脏读：读取未提交的更改。

不可重复读：读操作不会在事务持续期间内保留共享锁，其他事务可以在两个读操作之间更改数据资源，读操作因而可能每次得到不同的取值。

丢失更新：两个事务进行读操作，获得资源上的共享锁，读取完数据后，不再持有资源上的任何锁，两个事务都能更新这个值，
最后进行更新的事务将会覆盖其他事务做的更改，导致其他事务更改的数据丢失。

幻读：第一次和第二次读取到的数据行数不一致。

范围锁：读操作锁定满足查询搜索条件范围的锁

摘录自https://www.cnblogs.com/jackson0714/p/TSQLFundamentals_08.html(推荐阅读)

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​