AI 助理
备案控制台
开发者社区 数据库 文章 正文

SQL Server 事务隔离级别详解

2016-05-08 916
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，基础系列 2核4GB
推荐场景：
SQL Server性能诊断案例分析
RDS SQL Server Serverless，2-4RCU 50GB 3个月
推荐场景：
简介：

SQL 事务隔离级别

概述

     隔离级别用于决定如果控制并发用户如何读写数据的操作，同时对性能也有一定的影响作用。

步骤

事务隔离级别通过影响读操作来间接地影响写操作；可以在回话级别上设置事务隔离级别也可以在查询（表级别）级别上设置事务隔离级别。
事务隔离级别总共有6个隔离级别：
READ UNCOMMITTED(未提交读,读脏),相当于(NOLOCK)
READ COMMITTED(已提交读,默认级别)
REPEATABLE READ(可以重复读),相当于(HOLDLOCK)
SERIALIZABLE(可序列化)
SNAPSHOT(快照)
READ COMMITTED SNAPSHOT(已经提交读隔离)
对于前四个隔离级别：READ UNCOMMITTED<READ COMMITTED<REPEATABLE READ<SERIALIZABLE
隔离级别越高,读操作的请求锁定就越严格,锁的持有时间久越长;所以隔离级别越高,一致性就越高,并发性就越低,同时性能也相对影响越大.

获取事务隔离级别(isolation level)

DBCC USEROPTIONS

设置隔离

复制代码 
设置回话隔离
SET TRANSACTION ISOLATION LEVEL <ISOLATION NAME>
--注意:在设置回话隔离时(REPEATABLE READ)两个单词需要用空格间隔开,但是在表隔离中可以粘在一起(REPEATABLEREAD)

设置查询表隔离
SELECT ....FROM <TABLE> WITH (<ISOLATION NAME>)
复制代码

1.READ UNCOMMITTED

READ UNCOMMITTED:未提交读，读脏数据
默认的读操作:需要请求共享锁,允许其他事物读锁定的数据但不允许修改.
READ UNCOMMITTED:读操作不申请锁,运行读取未提交的修改,也就是允许读脏数据,读操作不会影响写操作请求排他锁.

 创建测试数据

复制代码 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL,
Price FLOAT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00),(11,11.00),(12,12.00),(13,13.00),(14,14.00);
GO
SELECT ID,Price FROM Orders
复制代码

新建回话1将订单10的价格加1

复制代码 
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
复制代码

在另一个回话2中执行查询操作

复制代码 
首先不添加隔离级别,默认是READ COMMITTED,由于数据之前的更新操作使用了排他锁，所以查询一直在等待锁释放*/
SELECT ID,Price FROM Orders 
WHERE ID=10
---将查询的隔离级别设置为READ UNCOMMITTED允许未提交读，读操作之前不请求共享锁。
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10;
--当然也可以使用表隔离,效果是一样的
SELECT ID,Price FROM Orders WITH (NOLOCK)
WHERE ID=10
复制代码

假设在回话1中对操作执行回滚操作,这样价格还是之前的10,但是回话2中则读取到的是回滚前的价格11,这样就属于一个读脏操作

ROLLBACK TRANSACTION

2.READ COMMITTED

READ COMMITTED（已提交读）是SQL SERVER默认的隔离级别，可以避免读取未提交的数据，隔离级别比READ UNCOMMITTED未提交读的级别更高;
该隔离级别读操作之前首先申请并获得共享锁，允许其他读操作读取该锁定的数据，但是写操作必须等待锁释放，一般读操作读取完就会立刻释放共享锁。

新建回话1将订单10的价格加1,此时回话1的排他锁锁住了订单10的值

复制代码 
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

SELECT ID,Price FROM Orders 
WHERE ID=10
复制代码

在回话2中执行查询，将隔离级别设置为READ COMMITTED

复制代码 
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT ID,Price FROM Orders 
WHERE ID=10
---由于READ COMMITTED需要申请获得共享锁，而锁与回话1的排他锁冲突，回话被堵塞，

----在回话1中执行事务提交
COMMIT TRANSACTION
/*由于回话1事务提交，释放了订单10的排他锁，此时回话2申请共享锁成功查到到订单10的价格为修改后的价格11，READ COMMITTED由于是已提交读隔离级别，所以不会读脏数据.
*/
复制代码

重置数据

UPDATE Orders 
SET Price=10
WHERE ID=10

注意：但是由于READ COMMITTED读操作一完成就立即释放共享锁,读操作不会在一个事务过程中保持共享锁,也就是说在一个事务的的两个查询过程之间有另一个回话对数据资源进行了更改,会导致一个事务的两次查询得到的结果不一致,这种现象称之为不可重复读.

3.REPEATABLE READ

REPEATABLE READ(可重复读)：保证在一个事务中的两个读操作之间，其他的事务不能修改当前事务读取的数据，该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成，所以此隔离级别查询完并提交事务很重要。

在回话1中执行查询订单10,将回话级别设置为REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT ID,Price FROM Orders 
WHERE ID=10

新建回话2修改订单10的价格

UPDATE Orders 
SET Price=Price+1
WHERE ID=10
---由于回话1的隔离级别REPEATABLE READ申请的共享锁一直要保持到事务结束，所以回话2无法获取排他锁，处于等待状态

在回话1中执行下面语句，然后提交事务

SELECT ID,Price FROM Orders 
WHERE ID=10
COMMIT TRANSACTION

回话1的两次查询得到的结果一致，前面的两个隔离级别无法得到一致的数据，此时事务已提交同时释放共享锁，回话2申请排他锁成功，对行执行更新

REPEATABLE READ隔离级别保证一个事务中的两次查询到的结果一致，同时保证了丢失更新
丢失更新：两个事务同时读取了同一个值然后基于最初的值进行计算，接着再更新，就会导致两个事务的更新相互覆盖。
例如酒店订房例子，两个人同时预定同一酒店的房间，首先两个人同时查询到还有一间房间可以预定，然后两个人同时提交预定操作，事务1执行number=1-0，同时事务2也执行number=1-0最后修改number=0,这就导致两个人其中一个人的操作被另一个人所覆盖，REPEATABLE READ隔离级别就能避免这种丢失更新的现象，当事务1查询房间时事务就一直保持共享锁直到事务提交，而不是像前面的几个隔离级别查询完就是否共享锁，就能避免其他事务获取排他锁。

 4.SERIALIZABLE

SERIALIZABLE(可序列化)，对于前面的REPEATABLE READ能保证事务可重复读，但是事务只锁定查询第一次运行时获取的数据资源（数据行），而不能锁定查询结果之外的行，就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间，有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时，那么在第二次查询的结果中就会存在这些新插入的数据，使两次查询结果不一致，这种读操作称之为幻读。
为了避免幻读需要将隔离级别设置为SERIALIZABLE

复制代码 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
复制代码

在回话1中执行查询操作,并将事务隔离级别设置为REPEATABLE READ(先测试一下前面更低级别的隔离)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

在回话2中执行插入操作

INSERT INTO Orders VALUES(15,15.00,1)

返回回话1重新执行查询操作并提交事务

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

结果回话1中第二次查询到的数据包含了回话2新插入的数据，两次查询结果不一致（验证之前的隔离级别不能保证幻读）

重新插入测试数据

复制代码 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
复制代码

接下来将回话级别设置为SERIALIZABLE,在回话1中执行查询操作,并将事务隔离级别设置为SERIALIZABLE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 
SELECT ID,Price,type FROM Orders
WHERE TYPE=1

在回话2中执行插入操作

INSERT INTO Orders VALUES(15,15.00,1)

返回回话1重新执行查询操作并提交事务

SELECT ID,Price,type FROM Orders
WHERE TYPE=1
COMMIT TRANSACTION

两次执行的查询结果相同

 

重置所有打开回话的默认隔离级别

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

5.SNAPSHOT

SNAPSHOT快照：SNAPSHOT和READ COMMITTED SNAPSHOT两种隔离（可以把事务已经提交的行的上一版本保存在TEMPDB数据库中）
SNAPSHOT隔离级别在逻辑上与SERIALIZABLE类似
READ COMMITTED SNAPSHOT隔离级别在逻辑上与 READ COMMITTED类似
不过在快照隔离级别下读操作不需要申请获得共享锁，所以即便是数据已经存在排他锁也不影响读操作。而且仍然可以得到和SERIALIZABLE与READ COMMITTED隔离级别类似的一致性；如果目前版本与预期的版本不一致，读操作可以从TEMPDB中获取预期的版本。

如果启用任何一种基于快照的隔离级别，DELETE和UPDATE语句在做出修改前都会把行的当前版本复制到TEMPDB中，而INSERT语句不需要在TEMPDB中进行版本控制，因为此时还没有行的旧数据

无论启用哪种基于快照的隔离级别都会对更新和删除操作产生性能的负面影响，但是有利于提高读操作的性能因为读操作不需要获取共享锁；

5.1SNAPSHOT

SNAPSHOT 在SNAPSHOT隔离级别下,当读取数据时可以保证操作读取的行是事务开始时可用的最后提交版本
同时SNAPSHOT隔离级别也满足前面的已提交读,可重复读,不幻读;该隔离级别实用的不是共享锁,而是行版本控制
使用SNAPSHOT隔离级别首先需要在数据库级别上设置相关选项

在打开的所有查询窗口中执行以下操作

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION ON;

重置测试数据

复制代码 
IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO
复制代码
复制代码 
在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

SELECT ID,Price,type FROM Orders
WHERE ID=10
---查询到更新后的价格为11

---在回话2中将隔离级别设置为SNAPSHOT,并打开事务(此时查询也不会因为回话1的排他锁而等待,依然可以查询到数据)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

---查询到的结果还是回话1修改前的价格,由于回话1在默认的READ COMMITTED隔离级别下运行,SQL SERVER必须在更新前把行的一个副本复制到TEMPDB数据库中
--在SNAPSHOT级别启动事务会请求行版本

---现在在回话1中执行提交事务,此时订单10的价格为11
COMMIT TRANSACTION

---再次在回话二中查询订单10的价格并提交事务,结果还是10,因为事务要保证两次查询的结果相同

SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

---此时如果在回话2中重新打开一个事务,查询到的订单10的价格则是11
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10

COMMIT TRANSACTION

/*SNAPSHOT隔离级别保证操作读取的行是事务开始时可用的最后已提交版本,由于回话1的事务未提交,所以订单10的最后提交版本还是修改前的价格10,所以回话2读取到的价格是回话2事务开始前的已提交版本价格10,当回话1提交事务后,回话2重新新建一个事务此时事务开启前的价格已经是11了，所以查询到的价格是11，同时SNAPSHOT隔离级别还能保证SERIALIZABLE的隔离级别*/
复制代码

5.2READ COMMITTED SNAPSHOT

READ COMMITTED SNAPSHOT也是基于行版本控制,但是READ COMMITTED SNAPSHOT的隔离级别是读操作之前的最后已提交版本,而不是事务前的已提交版本，有点类似前面的READ COMMITTED能保证已提交读,但是不能保证可重复读,不能避免幻读,但是又比 READ COMMITTED隔离级别多出了不需要获取共享锁就可以读取数据

要启用READ COMMITTED SNAPSHOT隔离级别同样需要修改数据库选项，在回话1，回话2中执行以下操作(执行下面的操作当前连接必须是数据库的唯一连接，可以通过查询已连接当前数据库的进程,然后KILL掉那些进程,然后再执行该操作,否则可能无法执行成功)

复制代码 
ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT ON

IF OBJECT_ID('Orders','U') IS NOT NULL DROP TABLE Orders 
GO
CREATE TABLE Orders
(ID INT NOT NULL PRIMARY KEY,
Price FLOAT NOT NULL,
type INT NOT NULL
);
INSERT INTO Orders VALUES(10,10.00,1),(11,11.00,1),(12,12.00,1),(13,13.00,1),(14,14.00,1);
GO

-----在回话1中打开事务,将订单10的价格加1,并查询跟新后的价格,并保持事务一直处于打开状态
BEGIN TRANSACTION
UPDATE Orders 
SET Price=Price+1
WHERE ID=10

--查询到的价格是11
SELECT ID,Price,type FROM Orders
WHERE ID=10

---在回话2中打开事务查询订单10并一直保持事务处于打开状态(此时由于回话1还未提交事务,所以回话2中查询到的还是回话1执行事务之前保存的行版本)
BEGIN TRANSACTION
SELECT ID,Price,type FROM Orders
WHERE ID=10
--查询到的价格还是10

---在回话1中提交事务
COMMIT TRANSACTION 

---在回话2中再次执行查询订单10的价格,并提交事务
SELECT ID,Price,type FROM Orders
WHERE ID=10
COMMIT TRANSACTION 
--此时的价格为回话1修改后的价格11,而不是事务之前已提交版本的价格,也就是READ COMMITTED SNAPSHOT隔离级别在同一事务中两次查询的结果不一致.
复制代码

关闭所有连接,然后打开一个新的连接,禁用之前设置的数据库快照隔离级别选项

ALTER DATABASE TEST SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE TEST SET READ_COMMITTED_SNAPSHOT OFF;

 

总结

   理解了事务隔离级别有助于理解事务的死锁。








本文转自pursuer.chen(陈敏华)博客园博客，原文链接：www.cnblogs.com/chenmh/p/3998614.html，如需转载请自行联系原作者

文章标签：
云数据库 RDS SQL Server 版
SQL
数据库
Go
关键词：
SQL事务
云数据库RDS SQLServer版事务
云数据库RDS SQLServer版事务隔离级别
SQL事务隔离级别
SQL隔离级别
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
长征6号
目录
相关文章
小王老师呀
|
3月前
|
SQL 数据库 数据安全/隐私保护
SQL Server数据库Owner导致事务复制log reader job无法启动的解决办法
【8月更文挑战第14天】解决SQL Server事务复制Log Reader作业因数据库所有者问题无法启动的方法：首先验证数据库所有者是否有效并具足够权限；若非，使用`ALTER AUTHORIZATION`更改为有效登录名。其次，确认Log Reader使用的登录名拥有读取事务日志所需的角色权限。还需检查复制配置是否准确无误，并验证Log Reader代理的连接信息及参数。重启SQL Server Agent服务或手动启动Log Reader作业亦可能解决问题。最后，审查SQL Server错误日志及Windows事件查看器以获取更多线索。
小王老师呀
38 0
进步`于辰
|
6天前
|
SQL Oracle 关系型数据库
[SQL]事务
本文介绍了事务处理的基本概念，包括事务的四大特性（原子性、一致性、隔离性、持久性）及生命周期。文章还详细解释了事务的保存点、四种事务隔离级别及其异常读现象，并提供了设置事务隔离级别的方法。最后，作者建议读者深入学习相关理论以更好地理解事务隔离级别。
进步`于辰
27 0
游客j4mujezz7vm7y
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
游客j4mujezz7vm7y
36 0
小王老师呀
|
2月前
|
SQL 存储 监控
SQLServer事务复制延迟优化之并行（多线程）复制
【9月更文挑战第12天】在SQL Server中，事务复制延迟会影响数据同步性。并行复制可通过多线程处理优化这一问题，提高复制效率。主要优化方法包括：配置分发代理参数、优化网络带宽、调整系统资源、优化数据库设计及定期监控维护。合理实施这些措施可提升数据同步的及时性和可靠性。
小王老师呀
55 0
蓝易云
|
2月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能，它能够帮助数据库管理员在数据丢失或损坏的情况下，有效地恢复数据。通过定期备份数据库和事务日志，并在需要时按照正确的步骤恢复，可以最大限度地减少数据丢失的风险。需要注意的是，恢复数据是一个需要谨慎操作的过程，建议在执行恢复操作之前，详细了解相关的操作步骤和注意事项，以确保数据的安全和完整。
蓝易云
105 0
wljslmz
|
3月前
|
SQL 监控 供应链
深入解析 SQL Server 如何处理事务及 SERIALIZABLE 隔离级别的意义
【8月更文挑战第31天】
wljslmz
91 0
小王老师呀
|
3月前
|
SQL 数据库连接 网络安全
SQLServer非默认端口下事务复制代理作业服务无法启动的处理
【8月更文挑战第14天】若SQL Server非默认端口下的事务复制代理作业无法启动，可按以下步骤解决：1) 确认服务器连接字符串正确无误，包括非默认端口号；2) 检查防火墙设置，确保允许非默认端口的连接；3) 核实SQL Server配置已启用非默认端口；4) 查阅代理作业日志寻找错误详情；5) 重启SQL Server与Agent服务；6) 使用工具测试非默认端口的连接性。如问题持续，请寻求专业支持。
小王老师呀
38 0
VipSoft
|
3月前
|
SQL 安全 Java
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
驱动程序无法通过使用安全套接字层(SSL)加密与 SQL Server 建立安全连接。错误:“The server selected protocol version TLS10 is not accepted by client
VipSoft
444 0
VipSoft
|
3月前
|
SQL 关系型数据库 MySQL
SQL Server 事务执行、回滚
SQL Server 事务执行、回滚
VipSoft
44 0
sunrr
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化？
【7月更文挑战第23天】SQL Server的并行实施如何优化？
sunrr
103 13

热门文章

最新文章

  • 1
    Oracle SQL总结 - 2
  • 2
    Oracle PL/sql 主要特点
  • 3
    SQL Server-聚焦事务对本地变量、临时表、表变量影响以及日志文件存满时如何收缩（三十一）
  • 4
    SQL*Loader导入记录报错ORA-01688
  • 5
    SQL进阶知识(七)——ALTER TABLE 命令
  • 6
    让SQL优化器更准确！AnalyticDB PG版发布统计信息自动收集功能
  • 7
    SQL SERVER中LIKE使用变量类型不同输出结果不一致解惑
  • 8
    SQL 通过触发器实现delete数据备份
  • 9
    数据字典到SQL语句的转换(使用word与VBA)
  • 10
    [转自雨痕]LINQ to SQL - Delay Loaded
  • 1
    数据安全无忧，SQL Server 2014数据库定时备份解密
    76
  • 2
    破解SQL Server迷局，彻底解决“管道的另一端无任何进程错误233”
    699
  • 3
    SQLSERVER 2014 删除数据库定时备份任务提示失败DELETE 语句与 REFERENCE 约束“FK_subplan_job_id“冲突
    68
  • 4
    Sql Server数据库Sa密码如何修改
    383
  • 5
    Flink CDC产品常见问题之SQLserver cdc 开启 cdc表没有记录如何解决
    609
  • 6
    IDEA+Mysql+Sqlserver安装步骤_kaic
    61
  • 7
    如何把高版本的sqlserver 还原到低版本的 sqlserver_kaic
    489
  • 8
    SQL Server的安装步骤_kaic
    52
  • 9
    解决navicat无法连接sqlserver数据库的问题
    137
  • 10
    sqlserver 常用操作
    59

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • SQL的增删改查及函数应用
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群
  • 使用DAS实现数据库自动SQL优化
  • 使用DAS实现数据库自动SQL限流
  • 一小时快速掌握 SQL 语法
    • 下一篇
    无影云桌面