SQL事物与事物隔离级别

转载自：http://blog.csdn.net/maotongbin/article/details/50600780

SQL Server 数据库中事物的概念

数据库中的事物是数据库并发控制的基本单位。一条或一组语句要么全部成功，对数据库中的某些数据成功修改；要么全部不成功，数据库中的数据还原到这些语句执行之前的样子。

比如订票系统，订票成功，余票显示就减少一张；订票失败，余票数量不变。购票和余票减少这两个不同的操作必须放在一起，称为一个完整的逻辑链，这样就构成了一个事物。

数据库中事物的ACID原则

原子性（Atomictiy）：事物的原子性是指一个事物中包含的一条或多条语句，构成了一个完整的逻辑单元，这个逻辑单元具有不可再分的原子性。这个逻辑单元一起提交执行，要么全部成功，要么全部失败。

XACT_ABORT为OFF状态下（SQL Server默认），SQL Server只会回滚执行失败的语句。如下语句中，1001记录行违反约束被回滚；1002记录行插入成功。可以通过try...catch语句控制commit和rollback。

if OBJECT_ID('Account') is not null
	drop table Account
go

create table Account
(
	ID int primary key,
	AccountBalance money check(AccountBalance>=0)
)

begin transaction
insert into Account values(1001,-1)--执行失败
insert into Account values(1002,500)--执行成功，插入数据行
commit transaction



select * from Account

一致性（Consistency）：可理解为数据的完整性。事物的提交要确保在数据库上的操作不会破坏数据的完整性。破坏了数据的完整性，SQL Server会回滚这个事物来确保数据库中的数据是一致的。

隔离性（Isolation）：与数据库中的事物隔离级别以及锁相关。多个用户可以对同一数据并发访问而又不破坏数据的正确性和完整性。并行事物的修改必须与其他并行事物的修改相互对立、隔离。在不同隔离级别下，事物的读取操作可能得到的结果是不同的。

持久性（Durability）：事物一旦对数据库的操作完成并提交后，数据修改就已经完成，即使服务重启，这些数据也不会改变。相反，如果事物的执行过程中，系统服务崩溃或者重启，那么事物所有的操作就会被回滚，回到事物操作之前的状态。

事物中的问题

脏读（Dirty Reads）：事物A正在访问并修改数据库中的数据但是没有提交，事物B可能读取到这些已经做出修改但未提交的数据。这样可能导致的结果就是所有的操作都可能回滚，比如事物A对数据做出的修改可能违背了数据表的某些约束、破坏了完整性，恰巧事物B读取到了这些不正确的数据造成事物B自身操作也发生失败回滚。

事物A读取到事物B未提交的数据。如事物B更改某行数据但并未提交，此时事物A读取到该行数据，但事物B最终回滚，导致事物A读取到的数据不准确。（在事物中，增删改会实时生效，直到提交或回滚才持久保存。根据隔离级别而有所不同）

不可重复读（Non-Repeatable Reads）：A事物两次读取同一数据，B事物也同时读取这同一数据。A事物在第二次读取前，B事物已经更新了这一数据。对于事物A来说，第一次和第二次读取到的数据可能不一致。

幻读（Phantom Reads）：与不可重复读有点类似，都是两次读取，不同的是A事物第一次操作的比如说是全表的数据，此时B事物并不只是修改某一具体数据而是插入了一条新数据，而后A事物第二次读取这全表的时候就发现比上一次多了一条数据，发生幻觉。

更新丢失（Lost Update）：两个事物同时更新，但由于某一事物更新失败发生回滚操作，这样有可能的结果就是第二个事物已更新的数据因第一个事物发生回滚而导致数据最终没有发生更新，两个事物的更新都 丢失了 。

事物A、B同时获得数据副本，并先后提交，后提交的会覆盖先提交所做的修改。

如：User1和User2同时获得一条员工数据，User1只修改了姓名并提交，User2修改了其他列并提交。这就造成User1对姓名的修改丢失了。原因是程序员更新时总是更新整行数据而不是单独的列，因为这样更方便。

SQL Server中事物的隔离级别

SET TRANSACTION ISOLATION LEVEL

READ UNCOMMITTED

|READ COMMITTED

|REPEATABLE READ

|SNAPSHOT

|SERIALIZABLE

Read UnCommitted（未提交读）

隔离级别最低，容易产生的问题就是脏读，可以读取其他事物修改了但没提交的数据。

它的作用跟在事务中select语句对象表上设置NOLOCK相同。

set transaction isolation level read uncommitted --设置隔离级别为未提交读
dbcc useroptions --验证隔离级别
go

事务A中update后，数据更改为500，延时10秒后回滚，数据恢复为以前状态（100）

begin transaction
update Account set AccountBalance=500
where ID=1001
waitfor delay '00:00:10'
rollback transaction

新建查询窗口，在事务A执行过程中提交，会看到数据更改为500，事务A回滚后又变为100。（不新建查询窗口执行不了）

set transaction isolation level read uncommitted
select * from Account

Read Committed（已提交读）

SQL Server的默认设置，可避免脏读，满足大多数要求。

如将上例中事务隔离级别修改为Read Committed，事务B必须等待事务A提交或回滚后才能读取。

事务中的语句不能读取已由其他事务做出修改但还未提交的数据。但是能够读取由其他事务做出修改且提交了 的数据。

有可能出现不可重复读和幻读。
A事务

set transaction isolation level read committed
dbcc useroptions

begin transaction
--第一次查询，发生在 B 事务开始之前
select * from Account where ID=1001

waitfor delay '00:00:10'

--第二次查询，发生在 B 事务提交之后
select * from Account where ID=1001

commit transaction

B事务

set transaction isolation level read committed
dbcc useroptions

begin transaction
update Account
set AccountBalance=500
where ID=1001
commit transaction

结果事务A中两次读取的数据不一致，出现不可重复读。

Repeatable Read（可重复读）

不能读取已由其他事务修改但还未提交的行。

当前事务读取的数据范围内的数据，不允许被其他所修改，但其他事务可以插入新行数据。

即Repeatable Read可避免脏读、不可重复读，但不能避免幻读。

A事务

set transaction isolation level repeatable read
dbcc useroptions

begin transaction
--第一次查询，发生在 B 事务开始之前
select * from Account where ID=1001

waitfor delay '00:00:10'

--第二次查询，发生在 B 事务提交之后
select * from Account where ID=1001

commit transaction

--第三次查询，发生在A、B事务结束之后
select * from Account where ID=1001

B事务

set transaction isolation level repeatable read
dbcc useroptions

begin transaction

update Account
set AccountBalance=500
where ID=1001

--第一次查询，发生在B事务更新之后
select * from Account where ID=1001
commit transaction

waitfor delay '00:00:10'

--第二次查询，发生在B事务提交之后
select * from Account where ID=1001

事务A两次读取到的是一样的值，可以理解为在事务A查询期间是不允许B事务修改A事务查询范围内的值。但实际上事务B已经完成了更新操作，只是因为A事务中隔离级别设置为Repeatable read,所以A事务中两次读取的结果始终保持一致。

如果将A事务A中的where去掉，B事务中的update改为insert一条新数据，那么在A事务中的两次查询结果不一致，第二次比第一次多了一条数据。

SNAPSHOT（快照隔离）

可以解决幻读问题。当前事务中读取的数据在整个事务开始到事务提交结束之前，这个数据版本是一致的。其他事务可能对这些数据做出修改，但是对当前事务来说是看不到这些变化的。可以理解为当前事务开始时会获得一个数据快照。

默认情况下，数据库不允许设置snapshot隔离级别。

需要使用如下语句开启

ALTER DATABASE 数据库名

SET ALLOW_SNAPSHOT_ISOLATION ON

A事务

set transaction isolation level snapshop

begin transaction
--第一次查询，发生在 B 事务开始之前
select * from Account

waitfor delay '00:00:10'

--第二次查询，发生在 B 事务提交之后
select * from Account

commit transaction

B事务

set transaction isolation level repeatable read

begin transaction

insert into Account values(1002,1500)

commit transaction

select * from Account

SERIALIZABLE（序列化）

以上示例中无论事务A设置为Repeatable read还是snapshot都不能阻止事务B对共享资源的修改，尽管这个修改在事务A中无法发现，但实际上事务B还是做出了修改。只要事务A一提交结束，马上就可以看到事务B做出的修改已经生效了。

性能最低，隔离级别最高，对资源的操作形成了独占，其他事务进不来。

如果并发级别很高，使用这个隔离级别，性能瓶颈将非常严重。