SQL Server 事务隔离级别详解

事务的隔离级别从低到高有：
Read Uncommitted(读未提交)：最低的隔离级别，什么都不需要做，一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。
Read Committed(读已提交)：只有在事务提交后，其更新结果才会被其他事务看见。可以解决脏读问题。
Repeated Read(可重复读)：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作，以及这个事务是否提交。可以解决脏读、不可重复读。
Serialization(可串行化)：事务串行化执行，隔离级别最高，牺牲了系统的并发性。可以解决并发事务的所有问题。
通常，在工程实践中，为了性能的考虑会对隔离性进行折中。

 

其中只有serialization实现隔离性所有要求，在真正实现事务的隔离性。

但考虑到实践，为了性能，数据库厂商做出了这方面的妥协，让使用者可以选择隔离的级别。

不同的隔离级别可以解决不同阶段的问题，是层层递进，逐渐增强的关系。

 

隔离性为了解决的问题主要有三个（将事务的隔离级别和问题联系在一起理解）：

        1、 脏读(Drity Read)：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。

　　　Read Committed可以解决脏读问题，但仍存在以下两种问题。

         2、不可重复读(Non-repeatable read) : 在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。不可重复读出现的原因就是事务并发修改记录，要避免这种情况，最简单的方法就是对要修改的记录加锁，这导致锁竞争加剧，影响性能。（另一种方法是通过MVCC可以在无锁的情况下，避免不可重复读。待了解。。）

　　　Repeated Read可以解决不可重复读问题和脏读问题，但仍无法解决下面的问题。

         3、幻读(Phantom Read) : 在同一个事务中，同一个查询多次返回的结果不一致。事务A新增了一条记录，事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。幻读仅指由于并发事务增加记录导致的问题，这个不能像不可重复读通过记录加锁解决，因为对于新增的记录根本无法加锁。需要将事务串行化，才能避免幻读。

　　　Serialization解决了以上所有问题，但是性能效率较低。
　　　通常来说，事务隔离级别越低，所需持有锁的时间也就越短，并发性能也就越好。

 

以下为SQL Server事务隔离级别的测试：

先新建表

USE [TestDB]
GO

/****** Object:  Table [dbo].[TranTestTable]    Script Date: 2018/4/17 15:53:18 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TranTestTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Age] [int] NULL,
 CONSTRAINT [PK_TranTestTable] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

　　插入测试数据

insert into TranTestTable(Name,Age) values('aa',11)
insert into TranTestTable(Name,Age) values('bb',22)
insert into TranTestTable(Name,Age) values('cc',33)

 

脏读测试

事务一

--脏读测试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL Repeatable Read
SET TRANSACTION ISOLATION LEVEL Serializable
--以上为设置事务隔离级别代码，执行其中一行
begin tran
	update TranTestTable set Age=100 where Id=1
 
	---这个时候事务二更新了id =1 的age
	waitfor delay '000:00:10'
rollback tran

事务二

--脏读测试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --出现脏读
SET TRANSACTION ISOLATION LEVEL READ COMMITTED	 --未出现脏读
SET TRANSACTION ISOLATION LEVEL Repeatable Read  --未出现脏读
SET TRANSACTION ISOLATION LEVEL Serializable     --未出现脏读
select * from  TranTestTable where Id=1

　　

可重复读测试

事务一

--不可重复读测试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --存在不可重复读
SET TRANSACTION ISOLATION LEVEL READ COMMITTED	 --存在不可重复读
SET TRANSACTION ISOLATION LEVEL Repeatable Read  --不存在不可重复读
SET TRANSACTION ISOLATION LEVEL Serializable    --不存在不可重复读
begin tran
declare @Age int
select @Age=age from TranTestTable where Id=1
print @Age
---这个时候事务二更新了id =1 的age
waitfor delay '000:00:10'
select @Age=age from TranTestTable where Id=1
print @Age
commit tran

事务二

--不可重复读测试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL Repeatable Read
SET TRANSACTION ISOLATION LEVEL Serializable
update TranTestTable set Age=Age+1 where Id=1

　　

幻读测试

事务一

--幻读测试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --出现幻读
SET TRANSACTION ISOLATION LEVEL READ COMMITTED   --出现幻读
SET TRANSACTION ISOLATION LEVEL Repeatable Read  --出现幻读
SET TRANSACTION ISOLATION LEVEL Serializable    --没出现幻读
begin tran
declare @num int
select @num=count(1) from TranTestTable where Age>30
print @num
---这个时候事务二更新了id =1 的age
waitfor delay '000:00:10'
select @num=count(1) from TranTestTable where Age>30
print @num
commit tran

事务二

--幻读测试
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET TRANSACTION ISOLATION LEVEL Repeatable Read
SET TRANSACTION ISOLATION LEVEL Serializable
insert into TranTestTable(Name,Age) values('dd',44)

　　

 

 

 

转载于:https://www.cnblogs.com/Charlie-Gou/p/8867824.html