数据库脏读、不可重读、幻读

最近忙着开发拎图网，图片社区 喜欢的朋友常来逛逛。谢谢.

数据库的事务隔离级别（TRANSACTION ISOLATION LEVEL）是一个数据库上很基本的一个概念。为什么会有事务隔离级别，SQL Server上实现了哪些事务隔离级别？事务隔离级别的前提是一个多用户、多进程、多线程的并发系统，在这个系统中为了保证数据的一致性和完整性，我们引入了事务隔离级别这个概念，对一个单用户、单线程的应用来说则不存在这个问题。

 

事务隔离五种级别：
        TRANSACTION_NONE  不使用事务。
        TRANSACTION_READ_UNCOMMITTED  允许脏读。
        TRANSACTION_READ_COMMITTED  防止脏读，最常用的隔离级别,并且是大多数数据库的默认隔离级别
        TRANSACTION_REPEATABLE_READ  可以防止脏读和不可重复读，
        TRANSACTION_SERIALIZABLE  可以防止脏读，不可重复读取和幻读，（事务串行化）会降低数据库的效率

  以上的五个事务隔离级别都是在Connection接口中定义的静态常量，

  使用setTransactionIsolation(int level) 方法可以设置事务隔离级别。
        如:con.setTransactionIsolation(Connection.REPEATABLE_READ);

  注意：事务的隔离级别受到数据库的限制，不同的数据库支持的的隔离级别不一定相同

 

  首先，我们来看一下高并发的系统中会存在哪些问题，为了便于理解我们以张三在招商银行的账号和存款为例。

一、准备工作：

1. 创建一个银行账号Table（只是为了说明问题，不考虑表的设计范式）
CREATE TABLE dbo.BankAccount
(
BankAccountId CHAR(16) NOT NULL, -- 银行账号
UserName NVARCHAR(32) NOT NULL, -- 用户
Balance DECIMAL(19, 2) NOT NULL, -- 余额
LastUpdate SMALLDATETIME NOT NULL
)
GO

2. 准备数据
INSERT INTO dbo.BankAccount
VALUES ('9555500100071120', N'张三', 10000.00, GETDATE()) -- 北京分行账号
INSERT INTO dbo.BankAccount
VALUES ('9555507551227787', N'张三', 20000.00, GETDATE()) -- 深圳分行账号
GO

3. 查看数据
SELECT * FROM dbo.BankAccount

二、应用场景

假设张三在招商银行开设了两个账号，一个是招商银行北京分行，一个是招商银行深圳分行，两个账号的余额分别是10,000和20,000。

1. 张三在网上做了一笔交易，交易额100，买方小王通过银行汇款100到张三的北京分行的账号（见下面左图），柜台操作人员向张三账号存入100（事务一），然后系统些操作日志（假设需要10秒，WAITFOR DELAY '00:00:10'）正在此时张三在ATM查了一下账号上余额（事务二），发现已经是10100，于是回去准备发货，但是事务一在写操作日志时超时，这是事务回滚，存款交易被取消，钱退给了小王，这样张三查到的账号余额事实上是事务一还没有提交的数据，导致张三错误的认为已经收到交易款项。

一个事务读到另外一个事务还没有提交的数据，我们称之为脏读。
解决方法：把事务隔离级别调整到READ COMMITTED，即把右上图中的SET TRAN ISOLATION LEVEL READ UNCOMMITTED更改成下图中的SET TRAN ISOLATION LEVEL READ COMMITTED。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果，因为此时事务以已经把自己的更改ROLLBACK了，所以事务二可以返回正确的结果。

再如更简单的脏读例子： e.g.
 
        1.Mary的原工资为1000, 财务人员将Mary的工资改为了8000(但未提交事务)        
        2.Mary读取自己的工资,发现自己的工资变为了8000，欢天喜地！
        3.而财务发现操作有误，回滚了事务,Mary的工资又变为了1000
          像这样,Mary记取的工资数8000是一个脏数据。

解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可  以避免该问题。

 

2. 张三先后两次查询某一账号的余额，在两次查询期间，小王完成了银行转账，导致两次的查询结果不同。

一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。
解决方法：把事务隔离级别调整到REPEATABLE READ。在下图中使用SET TRAN ISOLATION LEVEL REPEATABLE READ。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果。

再如更简单不可重读的例子： e.g.
 
        1.在事务1中，Mary 读取了自己的工资为1000,操作并没有完成
        2.在事务2中，这时财务人员修改了Mary的工资为2000,并提交了事务.
        3.在事务1中，Mary 再次读取自己的工资时，工资变为了2000

解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。

 

3. 张三妻子先后两次查询张三招商银行所有账号的总余额，而在此期间张三在广州招商银行分行成功开设了一个账号，并存入5000，导致张三妻子两次查询的总余额不同，在此期间张三原有两个账号的余额均未发生改变。

一个事务先后读取一个范围的记录，但两次读取的纪录数不同，我们称之为幻象读。
解决方法：把事务隔离级别调整到SERIALIZABLE。在下图中使用SET TRAN ISOLATION LEVEL SERIALIZABLE。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果。

 

再如更简单幻读的例子e.g.

         目前工资为1000的员工有10人。
         1.事务1,读取所有工资为1000的员工。
         2.这时事务2向employee表插入了一条员工记录，工资也为1000
         3.事务1再次读取所有工资为1000的员工 共读取到了11条记录，

解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。

3.1丢失更新(也属于幻像读中一种)
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。
          e.g.事务A和事务B同时修改某行的值，
 
                           1.事务A将数值改为1并提交
                                               2.事务B将数值改为2并提交。
                                                           这时数据的值为2，事务A所做的更新将会丢失。

 解决办法：对行加锁，只允许并发一个更新事务。

 

 

三、总结

事务隔离级别是通过数据库的锁机制来控制的，在不同的应用场景需要应用不同的事务隔离级别，SQL Server默认的事务隔离级别是READ COMMITTED，默认的隔离级别，已经可以满足我们大部分应用的需求。