MSSQL-并发控制-2-Isolation
如果转载，请注明博文来源： www.cnblogs.com/xinysu/ ，版权归 博客园 苏家小萝卜 所有。望各位支持！
MySQL通过MVCC和锁来实现并发控制，在4个隔离级别中，读写数据方式及加锁方式有所不同，以满足不同的业务需求。
而在MSSQL中，也是通过锁和MVCC的行版本来实现并发控制。
每个事务中，锁的类型、级别、加锁、释放的情况，由事务的隔离级别控制，在MSSQL中，有6个隔离级别，不同的隔离级别对锁的应用不一样。而这两个隔离级别中，有2个应用 MVCC的机制，也就是 快照类的隔离级别：Read Commmitted Snapshot 跟 Snapshot。
1 并发控制理论
在MSSQL中，经常用到的并发控制理论是 悲观并发控制跟乐观并发控制。
1.1 悲观并发控制
悲观并发，默认在事务操作过程中，一定会有其他事务跟它争夺资源，所以在事务操作过程中，会根据不同的情况对数据添加锁，避免操作期间其他事务对该数据的修改或读取，保证数据的一致性。
悲观并发控制，由于纳入了锁机制，很大程度会影响到并发规模。主要应用于数据频繁修改、并且回滚事务的成本要大于锁数据的成本 的系统中。
1.2 乐观并发控制
乐观控制，默认事务在读取数据的时候，其他事务并没有在操作这些数据，所以不会加锁，直接修改数据，修改后查看读取数据期间是否有其他用户也修改了数据，如果有，则回滚本身的修改事务。
乐观并发控制，应用于数据修改不频繁、并且 回滚事务成本要小于锁数据成本 的系统中。
2 隔离级别
在每一个事务中，都指定了一个隔离级别，该隔离级别定义了这个事务跟其他事务之间的隔离程度。
在MSSQL中，有6种隔离级别，4个常规隔离级别跟2个快照隔离级别：Read UnCommitted、Read Committed、Read Commmitted (行版本)、Read Repeattable、Snapshot跟Read Serializeble。Read Commmitted (行版本)跟Snapshot 可能接触情况比较少，不过仍会说明。
在MySQL中，默认的隔离级别是RR，而在SQL SERVER中，默认的隔离级别是RC，读已提交。
2.1 隔离级别说明
如何设置整个数据库的默认隔离级别？
数据不一致的说明详见之前博文：http://www.cnblogs.com/xinysu/p/7260227.html 中的第四章：数据不一致情况。
下文中说S锁，并不是全部加锁过程（MSSQL中还是IS锁的申请）。
- Read UnCommitted
- 简称 RU，读未提交记录，始终是读最新记录
- 可能存在脏读、不可重复读、幻读等问题
- 读的过程不加S锁，等同于 SELECT * FROM tbname with(nolock)
- Read Committed
- 简称 RC ，读已提交记录
- 可能存在不可重复读、幻读等问题
- 读的过程加 S锁，无论事务是否结束，SELECT 语句一旦结束，立马释放S锁，不会等到事务结束才释放锁，遵循的是 Strict 2-PL
- Read Commmitted (行版本)
- 简称 RCSI
- 应用MVCC原理，版本读，读已提交记录，但是读取到的不一定是最新的记录
- 同个事务中，读取数据都是同一个版本
- 不存在脏读、不可重复读问题，可能存在幻读问题
- 行版本控制隔离级别 中的版本数据，不存在与数据库本身，而是存在 tempdb ，下文会详细描述这一隔离级别
- Read Repeattable
- 简称 RR ，可重复读记录
- 可能存在幻读等问题
- 读的过程加S锁，直到事务结束，才释放S锁，遵循的是 Stong Strict 2-PL
- Snapshot
- 简称 SI
- 下文会详细描述这一隔离级别
- Read Serializeble
- 简称 RS，序列化读记录
- 不存在 脏读、不可重复读、幻读等问题
- 读的过程中除了添加S锁，还添加范围锁；修改数据的过程中，除了添加 X 锁，也会添加范围锁，避免在符合条件的数据在操作过程中，有其他符合条件的数据INSERT进来
- 并发度最差，除非明确业务需求及性能影响才使用，曾经遇到过某个短信业务的框架默认使用这个隔离级别，上线后爆发死锁上K个，马上分析紧急修复....
2.2 Read Commmitted Snapshot Isolation 与 Snapshot Isolation
Read Commmitted Snapshot Isolation 使用行版本控制语句级的快照，在事务中当数据发生修改或者删除时，调用写入复制机制，保证写入的行数据的旧版本满足事务操作前的一致性。 RCSI 保证的是语句级的 读一致性。
Snapshot Isolation 使用行版本控制事务级的快照，当事务开始的时候，调用写入复制机制。 SI 保证的是事务级 的读取一致性。
如何管理行版本信息呢？
两者的行版本的信息均存储在tempdb数据库内，并非存储在本身的数据库，这就要求tempdb要有足够的空间存储版本信息，如果tempdb空间不足，则行版本写入失败，造成该隔离级别无法正常使用。
存储引擎对使用 RCSI 或者 SI 隔离级别的事务，在 SI事务开始的时候，分配一个事务序列号 XLN，每次分配递增1，以此实现事务级的一致性，这里注意 RCSI的 事务序列号 并不是一个事务一个序列号，而是事务内每条SQL一个事务序列号，以此来实现语句级别的快照。这两个隔离级别下，需要维护所有执行过数据修改的逻辑副本（即行版本），这些逻辑副本存储在tempdb内，每个逻辑副本（行版本）都有标记本次的事务的事务序列号XLN。即 最新的行值存储在当前的数据库中，而历史行版本信息包括最新版本，存储在tempdb中。这里注意一下，事务内的修改数据写行版本信息的时候，先写入到缓存池中，在刷新到tempdb文件，避免性能造成太大的影响。
这个时候，可能会问？那岂不是tempdb要存储非常多的历史版本数据，有没有删除机制呢？
这个是有的，一方面，行版本信息不会即时删除，因为要保证基于行版本控制隔离级别下运行的事务要求，保证并行的事务如果正在使用tempdb的行版本信息 不会受到影响。另一方面，数据库的存储引擎 会跟踪最早可用的事务序列号，然后定期删除比序列号更小的 XLN的所有行版本。
如何读取行版本信息呢？
两个快照隔离级别下的 的事务读数据的时候，不会获取正在读取数据上的共享锁，因此不会堵塞正在修改的事务，由于减少了锁的申请及数量，可以提供其DB并发能力。不过会获取所在表格的架构锁，如果表格正在发现架构修改（如列增加修改等），则会被堵塞。
如何读取合适的行版本，RCSI 跟 SI 之间是有区别的。
RCSI：每次启动语句时，提交所有数据，同时读取tempdb中的最新事务序列，这使 RCSI 下事务内的每个语句 都可以查看每个语句启动时存在的最新数据的快照，也就是 事务内多个SQL查询间隙中有其他事务修改了数据，那么同个事务的多次相同SQL查询结果就会出现不一致的情况。
SI：每次启动事务时，提交所有数据，读取 最接近但低于 本身的 快照事务序列号，也就是 事务内的多个SQL 查询，读到的数据都是同一个版本，即使多次查询间隙有其他事务修改数据，读到的结果也是一致的。
如何修改行版本信息呢 ？
在使用 RCSI 事务中，使用阻塞性扫描（其中读取数据值时将在数据行上采用更新锁（U 锁）完成选择要更新的行，满足条件的行记录将升级更新锁到排它锁，注意，这里扫描的不是tempdb里边的行版本信息，而是实际数据库里边的最新行记录，修改数据的机制跟 RC 相同。 如果数据行不符合更新条件，则在该行上将释放更新锁，同时锁定下一行并对其进行扫描。持有锁之后，则进行数据更新，事务结束后，释放锁。
在使用 SI 事务中，对数据修改采用乐观方法：使用行版本的数据，进行数据修改，直到数据修改完成是，才获取实际数据上的锁， 当数据行符合更新标准时，则提交修改的数据行。 如果数据行已在快照事务以外修改，则将出现更新冲突，同时快照事务也将终止。 更新冲突由数据库引擎处理，无法禁用更新冲突检测。
从简单的SQL来分析，WHERE条件均为主键（仅为个人测试推测）：
- 同个事务，多次 SELECT * FROM tbname WHERE id=2
- RCSI，在同个事务中，每个SQL启动的时候，提交数据到tempdb表格（个人推测，应该是会分配一个类似hash字符串之类的，如果同个事务中的多次查询结果一致，应该不用在每个SQL开始的时候，重复提交行版本到tempdb），从tempdb中读取最新版本信息，如果tempdb没有版本信息，则从 数据库中读取，并把读取到的记录存储在 tempdb。会存在同个事务中，多次读取数据结果不一致的情况。
- SI，在同个事务中，同个事务内的相同SQL 从tempdb中读取距离当前事务最新的版本，整个事务内部的SQL都是用这个版本数据，如果tempdb没有版本信息，则从 数据库中读取，并把读取到的记录存储在 tempdb。同个事务中，不会存在 多次读取数据结果不一致的情况。
- UPDATE tbname SET colname='xinysu' WHERE id=18
- RCSI，直接读取数据库中的数据，根据主键加上X锁，更新数据，这个操作跟 RC 隔离级别是一样的。
- SI，读取 行版本 数据，在行版本上选择需要更新的行，修改成功后把数据 修改到实际的数据库中去，如果 实际数据库中的数据在这段操作期间已被其他事务修改了数值，则会出现更新冲突，该事务将报错停止。即，SI 在 UPDATE 的时候，有更新冲突检测。
- 为啥要先在行版本上更新，最后在更新到实际数据上？
- 假设一个UPDATE运行需要3s，但是只更新了1条行记录，如果直接在实际数据上更新，则需要锁定扫描记录3s，最后更新，中间会堵塞到其他事务对该数据的查询，但是如果在行版本上更新，则不需要锁住 实际数据，最后更新1行记录的时候，非常快，避免长时间的堵塞，提高并发能力。
|
属性
|
使用行版本控制的已提交读隔离级别
|
快照隔离级别
|
|
数据库级选项启动
|
READ_COMMITTED_SNAPSHOT
|
ALLOW_SNAPSHOT_ISOLATION
|
|
事务设置
|
使用默认的已提交读隔离级别，或运行 SET TRANSACTION ISOLATION LEVEL 语句来指定 READ COMMITTED 隔离级别
|
SET TRANSACTION ISOLATION LEVEL 来在事务启动前指定 SNAPSHOT 隔离级别
|
|
行版本处理
|
在每条语句启动前提交的所有数据。
|
在每个事务启动前提交的所有数据。
|
|
更新处理
|
从行版本恢复到实际的数据，以选择要更新的行并使用选择的数据行上的更新锁。 获取要修改的实际数据行上的排他锁。 没有更新冲突检测。
|
使用行版本选择要更新的行。 尝试获取要修改的实际数据行上的排他锁，如果数据已被其他事务修改，则出现更新冲突，同时快照事务也将终止。
|
|
更新冲突检测
|
无
|
集成支持。 无法禁用。
|
3 隔离级别测试
查看当前会话的数据库隔离级别：DBCC USEROPTIONS ，查看[set options] = 'isolation level'，即可查看当前事务的隔离级别。
数据不一致的说明详见之前博文：http://www.cnblogs.com/xinysu/p/7260227.html 中的第四章：数据不一致情况。
2-PL锁申请释放的说明详见之前博文：http://www.cnblogs.com/xinysu/p/7260227.html 中的第3章：数据不一致情况。
设置数据库隔离级别：
- RU，事务开始的时候，设置 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- RC，事务开始的时候，设置 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
- RCSI，整个数据库级设置 READ_COMMITTED_SNAPSHOT 为ON，注意，设置的这个的时候需要获取数据库的独占权，也就是当前不允许有用户线程连接数据库，否者这个设置SQL会一直处于堵塞情况。如果当前数据库的默认隔离级别是 RC，则设置后，默认为RCSI，否者，需要在事务开始的时候，设置 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
- 数据库设置：当前数据库下，执行 ALTER DATABASE dbname SET READ_COMMITTED_SNAPSHOT ON
- 事务设置：SET TRANSACTION ISOLATION LEVEL READ COMMITTED
- RR，事务开始的时候，设置 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
- RS，事务开始的时候，设置 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
- SI，整个数据库级设置 ALLOW_SNAPSHOT_ISOLATION 为ON，同时设置事务的隔离级别为 SNAPSHOT。注意，这里的 ALLOW_SNAPSHOT_ISOLATION 设置也是需要获取数据的独占锁。
- 数据库设置：当前数据库下，执行 ALTER DATABASE dbname SET ALLOW_SNAPSHOT_ISOLATION ON
- 事务设置：SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
测试过程中，分为3个表格：无索引、有索引、有唯一索引。
CREATE TABLE tb_no_index ( id int primary key not null identity(1,1), age int not null, name varchar(100) ); CREATE TABLE tb_index ( id int primary key not null identity(1,1), age int not null, name varchar(100) ); CREATE TABLE tb_unique_index ( id int primary key not null identity(1,1), age int not null,name varchar(100) ); CREATE INDEX IX_age ON tb_index(age) CREATE INDEX IX_unique_age ON tb_index(age) INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25); INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25); INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25);
3.1 Read Uncommitted
- 数据不一致情况测试截图
- RU测试结论
- 在RU隔离级别下
- 不会出现更新丢失情况（锁机制），但是会出现 脏读、不可重复读及幻读的情况。
- 读不加行锁，可以读未提交数据
3.2 Read Committed
- 数据不一致情况测试截图
- 读情况测试
- RC测试结论
- 在RC隔离级别下
- 不会出现更新丢失情况（锁机制）、脏读现象，但是会出现 不可重复读及幻读的情况
- 读需要申请锁，故不会出现脏读情况
- 遵循 强2-PL模式，事务内的读锁读完即刻释放，写锁等到事务提交的时候才释放。
3.3 Read Commit Snapshot Isolation
- 测试环境设置
- 实现设置数据库隔离级别为：
- 检查当前会话的默认隔离级别：
- 数据不一致情况测试截图
- 更新冲突测试
- RCSI 测试结论
- 读不加锁，但申请表格的架构锁，读行版本数据
- 不存在丢失更新、脏读情况，但是存在不可重复读及幻读情况
- 没有更新冲突检测，RCSI跟RC的更新处理方式一样
3.4 Read Reaptable
- 数据不一致情况测试截图
- RR测试结论
- 读加S锁，事务结束后才释放S锁
- 不存在丢失更新、脏读及不可重复读情况，但是存在幻读情况
3.5 Read Serializable
- 数据不一致情况测试截图
- RS 测试结论
- 读加S锁，事务结束后才释放S锁
- 增加了范围锁
- 不存在丢失更新、脏读、不可重复读、幻读情况
- 并发能力最差
3.6 Snapshot Isolation
- 数据不一致情况测试截图
- 更新冲突测试
- SI 测试结论
- 不存在 丢失更新、脏读、幻读等数据不一致情况
- 读不加锁，为读行版本数据
- 具有冲突监测，无法禁用，如果使用这个隔离级别，程序要做更新冲突的回滚处理
4 总结
|
隔离级别
|
说明
|
脏读
|
不可重复读
|
幻影
|
并发控制模型
|
|
Read UnCommitted
|
未提交读
|
YES
|
YES
|
YES
|
悲观
|
|
Read Committed
|
已提交读
|
NO
|
YES
|
YES
|
悲观
|
|
Read Commmitted (行版本)
|
已提交读（快照）
|
NO
|
YES
|
YES
|
乐观
|
|
Read Repeattable
|
可重复读
|
NO
|
NO
|
YES
|
悲观
|
|
Snapshot
|
快照
|
NO
|
NO
|
NO
|
乐观
|
|
Read Serializeble
|
可串行化
|
NO
|
NO
|
NO
|
悲观
|
如果转载，请注明博文来源： www.cnblogs.com/xinysu/ ，版权归 博客园 苏家小萝卜 所有。望各位支持！
