sqlserver 事务隔离级别介绍
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
--dbcc useroptions 也可以
通过锁实现的四种事务隔离级别
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' 该语句只会使当前事务暂停执行指定的时间，然后再继续执行后续的语句，但事务waitfor delay ... 等待期间，第一个select加的S锁早已释放。这意味着在 Read Committed 隔离级别下，其他事务可以在事务1第一次查询后对该行进行更新。
repeatable read：保障在一个事务内重复读取时，始终能够读取到相同的内容。（避免不一致读）
避免不一致读：修改前一个例子的事务1，设置隔离级别为：set transaction isolation level Repeatable Read，其他执行操作不变，直接查看两个事务的锁情况
通过查询发现，事务1给行加了一个S锁，这个隔离级别下S锁的生命周期在整个事务执行期间有效，此时事务2给该行加的X锁处于等待状态（wait）。等事务1执行完毕，才会执行事务2的更新操作。
上述的三个隔离级别，都是针对行数据进行加锁的。
serializable：是事务的最高隔离级别（避免幻读）
幻读：一个事务先后两次在同一个范围内查询数据，并且在两次查询时间间隔内，另一个事务这个范围内插入一条或多条新的数据，导致前一个事务两次读取到的数据不一致。
下图，在 Transaction 2 操作过程中，会对 查询范围内的行加锁，此时其他事务无法操作（插入、删除、修改）其中的数据，只能等待或者放弃
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 --或在查询语句中使用with(holdlock)，不需要设置事务隔离级别 begin tran --事务中第一行select hold住一个范围，给该范围内所有的行加上了RangeS-S范围共享锁 select * from jserp.somx with(holdlock) 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下，事务只能读取已经提交的数据，不会读取到未提交的数据（避免脏读）。当事务在读取数据时，如果遇到 X 锁锁定的行，则会进入等待状态，直到 X 锁释放。S 锁在事务读取完毕后立即释放，不会持有到事务结束（无法避免不一致读）。
3、在repeatable read下，事务读取数据时会对数据行加上 S 锁，并且会在整个事务期间保持这个锁。这样可以保证事务期间读取到的数据是一致的，不会受到其他事务的修改影响。S 锁会阻塞其他事务对数据行的写操作（避免不一致读）。
4、在serializable下，事务读取一个范围内的数据时，会对查询到的所有数据加上RangeS-S锁（范围共享锁），确保在事务期间范围内的数据不会被其他事务修改。如果其他事务在范围内插入数据，则会被阻塞。RangeS-S 锁会阻塞其他事务对范围内数据的修改。（避免幻读）
前面介绍的四种事务隔离级别是基于锁实现的，下面将详细介绍Snapshot 和 Read Committed Snapshot 事务隔离级别。
这两种事务隔离级别是通过Multi-Version Concurrency Control （MVCC）多版本并发控制机制实现。当然不是说这两种隔离没有锁，而是在读取数据的时候，极大地削弱了锁的作用。
简述数据库MVCC历史（了解）
MVCC（多版本并发控制）是数据库管理系统中的一种并发控制机制，并不局限于某个特定的年份。它的概念和实现在数据库系统的发展历史中逐渐演化和成熟。
MVCC 的概念最早可以追溯到20世纪70年代，随着并发控制和事务处理的研究和发展，MVCC 成为了解决并发访问问题的一种重要方法。
在关系数据库管理系统（RDBMS）中，MVCC 技术首次在商业数据库中得到广泛应用是在20世纪80年代末和90年代初。其中，一些数据库系统，如Oracle和PostgreSQL，引入了行级锁和版本控制机制，以支持并发事务的隔离。
针对 SQL Server，MVCC 的一种具体实现方式是通过快照隔离级别（Snapshot Isolation）和读提交快照隔离级别（Read Committed Snapshot）来实现的。这些隔离级别在 SQL Server 2005 版本中首次引入。
在 SQL Server 2005 中，引入了快照隔离级别（Snapshot Isolation）作为一种新的隔离级别。它使用行版本控制（Row Versioning）来实现事务的隔离。
而读提交快照隔离级别（Read Committed Snapshot）则是在 SQL Server 2005 Service Pack 1 中引入的。它是在读提交（Read Committed）隔离级别的基础上，使用了行版本控制，以提供更高的并发性和隔离级别。
随着时间的推移，数据库管理系统不断发展和改进，MVCC 技术也在不同的数据库系统中得到了广泛应用和优化。因此，MVCC 的实现和功能可能因不同数据库系统的版本和配置而有所差异。在选择和使用隔离级别时，建议参考具体数据库产品的文档和指南，以了解其支持的隔离级别和版本控制机制的详细信息。
开启快照：
在 SQL Server 中启用快照隔离级别（Snapshot Isolation），需要同时进行两个步骤
1：alter database mes set allow_snapshot_isolation on （off表示关闭），这个命令用于在指定的数据库上启用快照隔离级别
2：set transaction isolation level snapshot ，这个命令将当前事务的隔离级别设置为快照隔离级别
在 SQL Server 中启用快照隔离级别（Read Committed Snapshot），也需要同时进行两个步骤
1：alter database mes set read_committed_snapshot on （off表示关闭），这个命令用于在指定的数据库上启用RCSI隔离级别
2：set transaction isolation level Read Committed，这个命令将当前事务的隔离级别设置为快照隔离级别。sqlserver启用RCSI后，默认的事务隔离级别read committed被Read Committed Snapshot自动取代，且将不存在read committed这种隔离级别。通常步骤2省略。
查看快照隔离级别是否启动：
select name,snapshot_isolation_state,snapshot_isolation_state_desc,is_read_committed_snapshot_on from sys.databases where name='mes'
snapshot_isolation_state 值为1,或者snapshot_isolation_state_desc 值为on，表示数据库上启用快照隔离级别
is_read_committed_snapshot_on 值为1，表示数据库上启用RCSI隔离级别
注意点：
1.alter database mes set allow_snapshot_isolation/read_committed_snapshot on，只是启用快照隔离级别，就像家里通电了，但如果要开灯，还需要执行set transaction...命令
2.开启Read Committed Snapshot功能后，数据库由原来Read Committed自动变成Read Committed Snapshot，一个数据库中不存在两个事物隔离级别分别为RCI和RCSI
3.Snapshot和Read Committed Snapshot数据库可以同时开启，但是一个事务只有一种隔离级别。
4.开启数据库快照隔离级别前，要保证数据库资源不能被其他事务占用，通常设置单用户数据库，再修改数据库快照隔离级别，最后恢复多用户数据库
alter database mes set single_user with rollback immediate
alter database mes set allow_snapshot_isolation on/off
alter database mes set read_committed_snapshot on/off
alter database mes set multi_user
snapshot、read committed snapshot两者读取数据快照上有差异：
snapshot：在该快照隔离级别下，事务中读取的数据都使用事务开始前的数据快照——commit的版本（不会读取其他事务更新未提交数据），在事务执行期间一直使用该数据快照。这意味着事务不会受到其他并发事务对数据的修改的影响，从而避免了脏读、不可重复读和幻读的问题；
Read Committed Snapshot：在读提交快照隔离级别下，事务执行中的每个读取操作都会创建一个数据快照。事务中的每个查询读取的数据都是同一时间节点下的数据快照——commit的版本，而不受其他事务的更新加锁的影响。这可以避免脏读的问题，但仍可能遇到不可重复读和幻读的情况。
复现两种快照读差异：
先执行事务1，事务2和事务3都在事务1执行后的4秒内执行（三个事务应该分别放在三个查询窗口内执行）
--事务1 begin tran update jserp.somx set somx_wlid='1' where somx_soid='60548712' and somx_soxh = '99910' waitfor delay '00:00:04' update jserp.somx set somx_wlid='1' where somx_soid='60548712' and somx_soxh = '99920' commit --事务2两次查询都是事务1更新前的数据 set transaction isolation level snapshot begin tran select * from jserp.somx where somx_soid='60548712' and somx_soxh = '99910' waitfor delay '00:00:04' select * from jserp.somx where somx_soid='60548712' and somx_soxh = '99920' commit --事务3前一条查询是事务1更新前的数据，后一条查询是事务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:04' select * from jserp.somx where somx_soid='60548712' and somx_soxh = '99920' commit
事务2查询的结果是事务1提交前数据库commit数据的快照，这个快照一直保存到事务2执行结束，期间供事务2使用。
事务3查询执行期间发现第一条数据资源被锁，通过MVCC找到前一个版本（事务1提交前，其他事务commit过的快照（拷贝）），而第二条查询读取的数据是事务1刚更新commit的值。
参考资料：
https://www.jianshu.com/p/ba8de5bc51e2
