526互联

SQLServer 隔离级别的简单学习

发布时间 2023-04-17 23:23:29作者: 济南小老虎

SQLServer 隔离级别的简单学习

背景

上周北京一个项目出现了卡顿的现象。
周末开发测试加紧制作测试发布了补丁，但是并没有好转。
上周四时跟研发訾总简单沟通过， 怀疑是隔离级别有关系。但是不敢确认。
因为现场是SQLServer数据库。前期出现过一些问题。
同部门的杨老师也一直问我要不要SQLServer修改默认的隔离级别。 
研发戴老师找MicroSoft开Case使用dbcc等工具进行了研判。
我这边在群里一直看大家的反馈， 不太想直接反馈自己的建议。担心出现误导。

周一下午再次出现问题后，没有忍住，在群里咨询了隔离级别的问题。
事业部性能负责人肖总给出问题计划进行隔离级别的切换。
我这里想简单总结与复盘一下这次事故。也是对自己的之前一直比较不太关注的一个总结。

修改隔离级别的语法

这里直接复制公司的文档：
这里希望增加一点的事，建议有条件的项目进行一次数据库的重启，释放部分连接
并且进行一次数据库的全库备份操作，避免出现数据丢失和异常。

对于MSSQL数据库默认是不启用快照隔离的，一旦事务没有及时提交会影响其他进程的读操作，
造成数据库阻塞。
 
--查询数据库状态
select name,user_access,user_access_desc,
   snapshot_isolation_state,snapshot_isolation_state_desc,
   is_read_committed_snapshot_on
from sys.databases
GO
 
--如果对应的 snapshot_isolation_state_desc =OFF ，需要使用以下命令启用
---sa登录启用指定数据库的快照隔离将 [dbName]  替换为具体的cwbase001 数据库
 
ALTER DATABASE [dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [dbName] SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE [dbName] SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE [dbName] SET MULTI_USER
GO

关于此快照级别的理解-这一段的理解不一定对

Oracle和SQLSERVER的默认隔离级别都是 Read Commited 
但是两者有一个区别。 
Oracle数据库是通过undo段，对每一个事务提交之前做一个记录信息。
Oracle的DML并不会阻塞select， 他可以根据事务的时间，自动从undo段中截取合适的数据版本
所以理论上Oracle第一次查询和第二次查询的结果可能不一致，会出现不可可重复读的问题。 

SQLSERVER 默认情况下是不开机快照隔离的，DML语句的提交之前可能会阻塞其他事务的查询语句。
因为很多查询语句是下一步处理的必备条件，阻塞到了查询语句，就会导致后面的操作无法有效进行
出现比较严重的性能问题。 

SQLSERVER打开快照隔离， 在SQLSERVER2019之前的版本，会在tempDB里面创建类似于Oracle undo表空间
的效果。能够实现在tempdb 中读取未提交事务之前的行版本信息。 
通过这种方式来避免LCK_M_U

关于缺点

Oracle的undo和SQLServer的tempdb都是数据库核心的关键存储。
理论上应该尽量少去使用，避免出现性能瓶颈。
必须开启的情况下。可能会导致出现大量的读写，导致性能下降。
理论上tempdb和undo 以及 redo 应该都存储于硬件性能良好的存储上面。
提高数据库的性能。 

太多的使用和频繁的读写可能会导致TPS下降，以及数据库的损坏，需要重点注意。

关于扩展思路

十年前曾经看过阿里的Oracle的DBA的一个文章。
讲述一个系统每天早上登陆时总是特别卡顿。
原因在于。这个系统的登陆日志信息表示是按日期进行分表的。
每当刚上班时新创建的表刚开始使用，因为他的表数据比较少，所以占用的区块或者是block比较少。
oracle的insert的并发是基于区块来的， insert的并发度可能不会大于数据库已有的区块数
因为不允许同时修改同一个区块， 可能会导致数据错乱。 

随着时间的流逝，这个表越来越大区块越来越多，就能够支撑更多的并发。性能就会变好。

感觉这个问题和SQLSERVER的隔离级别问题是相似的。
虽然理论上在不同区块上面应该不会互相锁掉，但是在某些极端情况下依旧存在风险。
数据库调优的核心就是查看等待时间， 查看CBO， 查看事务的处理。
通过耗时较长的操作的定位发现问题原因进行优化。

一些资料

Oracle中的隔离级别及实现机制:
　　在Oracle中，没有Read Uncommitted及Repeatable Read隔离级别，这样在Oracle中不允许一
个会话读取其他事务未提交的数据修改结果，从而避免了由于事务回滚发生的读取错误。Oracle中的
Read Committed和Serializable级别，其含义与SQL Server类似，但是实现方式却大不一样。

　　在Oracle中，存在所谓的回滚段(Oracle9i之前版本)或撤销段(Oracle9i版本)，Oracle在修改
数据记录时，会把这些记录被修改之前的结果存入回滚段或撤销段中，就是因为这种机制，Oracle对
于事务隔离级别的实现与SQL Server截然不同。在Oracle中，读取操作不会阻碍更新操作，更新操作
也不会阻碍读取操作，这样在Oracle中的各种隔离级别下，读取操作都不会等待更新事务结束，更新
操作也不会因为另一个事务中的读取操作而发生等待，这也是Oracle事务处理的一个优势所在。

　　Oracle缺省的设置是Read Committed隔离级别(也称为语句级别的隔离)，在这种隔离级别下，
如果一个事务正在对某个表进行DML操作，而这时另外一个会话对这个表的记录进行读取操作，则
Oracle会去读取回滚段或撤销段中存放的更新之前的记录，而不会象SQL Server一样等待更新事务的
结束。

　　在Serializable隔离级别(也称为事务级别的隔离)，事务中的读取操作只能读取这个事务开始之
前已经提交的数据结果。如果在读取时，其他事务正在对记录进行修改，则Oracle就会在回滚段或撤
销段中去寻找对应的原来未经更改的记录(而且是在读取操作所在的事务开始之前存放于回滚段或撤销
段的记录)，这时读取操作也不会因为相应记录被更新而等待。

一些资料2

SQLSERVER的隔离级别以及实现机制：
    Read Committed

　　这是SQL Server的缺省隔离级别，设置为这种隔离级别的事务只能读取其他事务已经提交的更新
结果，否则，发生等待，但是其他会话可以修改这个事务中被读取的记录，而不必等待事务结束，显
然，在这种隔离级别下，一个事务中的两个相同的读取操作，其结果可能不同。

　　Read Repeatable

　　在一个事务中，如果在两次相同条件的读取操作之间没有添加记录的操作，也没有其他更新操作导
致在这个查询条件下记录数增多，则两次读取结果相同。换句话说，就是在一个事务中第一次读取的记
录保证不会在这个事务期间发生改变。SQL Server是通过在整个事务期间给读取的记录加锁实现这种
隔离级别的，这样，在这个事务结束前，其他会话不能修改事务中读取的记录，而只能等待事务结束，
但是SQL Server不会阻碍其他会话向表中添加记录，也不阻碍其他会话修改其他记录。

　　Serializable

　　在一个事务中，读取操作的结果是在这个事务开始之前其他事务就已经提交的记录，SQL Server
通过在整个事务期间给表加锁实现这种隔离级别。在这种隔离级别下，对这个表的所有DML操作都是不
允许的，即要等待事务结束，这样就保证了在一个事务中的两次读取操作的结果肯定是相同的。