526互联

sqlserver 事务隔离级别与脏读、不一致读、幻读

发布时间 2023-10-24 14:07:24作者: pandora2050

sqlserver查询窗口的默认事务隔离级别为：read committed，可以通过下面SQL语句查看：

SELECT CASE transaction_isolation_level
       WHEN 0
         THEN 'Unspecified'
       WHEN 1
         THEN 'ReadUncommitted'
       WHEN 2
         THEN 'ReadCommitted'
       WHEN 3
         THEN 'Repeatable'
       WHEN 4
         THEN 'Serializable'
       WHEN 5
         THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

 

 

 

 

read uncommitted： 这是事务最低粒度的隔离级别，它允许在操作过程中不会锁，从而让当前事务读取到其他事务的数据。（存在脏读、不一致读、幻读）

 复现脏读（sqlserver2016取消了read uncommitted），set transaction isolation level Read unCommitted

在sqlserver新建两个查询窗口，分别执行下面的事务（先执行事务1）

--事务1
--不用在会话中设置事务的隔离级别，事务不要写commit或rollback，确保当前事务没有提交或者被取消
begin tran
    update jserp.somx set somx_wlid='888888' where somx_soid='60548712' and somx_soxh='99910'
--事务2
--设置会话事务级别
set transaction isolation level Read unCommitted
select * from jserp.somx where somx_soid='60548712' and somx_soxh='99910'

--或在查询语句中使用with(nolock)
select * from jserp.somx with(nolock) where somx_soid='60548712' and somx_soxh='99910'

 此时，事务1被更新的行加了X锁（排它锁），但事务2仍读取到了事务1修改尚未提交的数据，导致脏读（dirty read）的问题。因为事务2的隔离级别为Read unCommitted，事务1的X锁不会阻止事务2读取。

 

read committed：资源（行）被当前事务更新锁定，但是没有commit或rollback，此时其他事务无法访问被锁定的行。（避免脏读）

避免脏读：修改前一个例子的事务2，设置隔离级别为：set transaction isolation level Read Committed，其他执行操作不变，直接查看两个事务的锁情况

--查看当前会话的隔离级别   select transaction_isolation_level,* from sys.dm_exec_sessions where session_id=@@spid 
--select request_session_id spid,object_name(resource_associated_entity_id) table_name from sys.dm_tran_locks where resource_type='object'
select request_session_id,* from sys.dm_tran_locks where resource_type<>'database' order by 1

 观察发现，事务2给要获取的资源（行）加了S锁，但状态是wait，也就是说事务2处于等待状态。

 

 read committed：只能避免脏读，不能避免不一致读

 复现不一致读：在sqlserver新建两个查询窗口，分别执行下面的事务（先执行事务1，在20s内执行事务2），发现事务1两次select查询的结果不同。

--事务1
set transaction isolation level Read Committed
begin tran
    select * from jserp.somx where somx_soid='60548712' and somx_soxh='99910'
    waitfor delay '00:00:20'
    select * from jserp.somx where somx_soid='60548712' and somx_soxh='99910'
    commit
--事务2
update jserp.somx set somx_wlid='222222' where somx_soid='60548712' and somx_soxh='99910'

可能有人会想不通，为什么事务1的第一个select给要查询的行加了S锁，事务2仍然可以更新改行，我也想了好久。事务1中第一条select语句会给要查询的行加一个共享锁（S锁），它确实会阻止其他事务对该行数据进行排他性操作，如更新或删除。

但是需要注意的是，共享锁（S锁）是一种短暂的锁，当事务读取完毕后会立即释放。waitfor delay '00:00:20' 该语句只会使当前事务暂停执行指定的时间，然后再继续执行后续的语句，但第一个select加的S锁已释放。这意味着在 Read Committed 隔离级别下，其他事务仍有机会在事务1查询某行数据时对该行进行更新。

 

repeatable read：保障在一个事务内重复读取时，始终能够读取到相同的内容。（避免不一致读） 

避免不一致读：修改前一个例子的事务1，设置隔离级别为：set transaction isolation level Repeatable Read，其他执行操作不变，直接查看两个事务的锁情况

通过查询发现，事务1给行加了一个S锁，事务2给行加的X锁（wait）。等事务1执行完毕，才会执行事务2的更新操作

 

 上述的三个隔离级别，都是针对行数据进行加锁的。

 

serializable：事务的最高隔离级别（避免幻读）
幻读：一个事务先后两次在同一个范围内查询数据，并且在两次查询间隔内，另一个事务这个范围内插入一条或多条新的数据，导致前一个事务两次读取到的数据不一致。

 下图，在 Transaction 2 操作过程中，会对 Range 进行加锁，此时其他事务无法操作其中的数据，只能等待或者放弃

--事务1
set transaction isolation level serializable
begin tran
    select * from jserp.somx where somx_soid='60548712' and somx_soxh between '99910' and '999100'
    waitfor delay '00:00:20'
    select * from jserp.somx where somx_soid='60548712' and somx_soxh between '99910' and '999100'
    commit
 --事务2
 insert into jserp.somx ...99920

查询发现当前范围内的所有行都加上了RangeS-S范围共享锁（Range Shared Lock）。另一个事务要在这个范围内插入两条记录，并加了一个RangeI-N
范围间隙锁（Range In-Range Gap Lock），此时范围间隙锁处于wait状态,确保事务1执行完毕。
设置事务隔离级别serializable会对where条件范围内的记录全部加锁。

 

总结：事务的隔离级别是通过锁权限和锁生命周期实现的。

1、在read uncommitted下，S锁可以访问X锁锁定的行；

2、在read committed下，S锁访问X锁锁定的行时，处于wait状态，被阻塞。S锁在事务生命周期内短暂存在，当事务读取完毕后会立即释放，事务继续执行后面的语句；（加S锁在读取某一行的那一刻，X锁需要等待）

3、在repeatable read下，S锁在整个事务生命周期内保持，直到事务提交或者撤回，这期间会阻塞X锁。

4、在serializable下，RangeS-S范围共享锁与repeatable read情况相似，不同的是不再作用数据库某一行，而是一个范围内的行都将被加上RangeS-S锁，如果其他事务insert一条语句正好在前面这个范围，将被阻塞。



参考资料： 


https://www.jianshu.com/p/ba8de5bc51e2