SQL Server 2000之事务隔离

---

平时使用SQL Server 2000,但是对事务隔离并没有统一总结过,当猛然被问起时,还真的不好回答,所以总结一下.

首先要,明白事务隔离的级别:

未提交读

提交读

可重复读

可串行读

此处有一篇好文章,供参考: http://untitled.spaces.live.com/blog/cns!86B82838704E0D5A!819.entry

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

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

一、准备工作：

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

4.waitfor

在达到指定时间或时间间隔之前，或者指定语句至少修改或返回一行之前，阻止执行批处理、存储过程或事务。

如:

A. 使用 WAITFOR TIME
以下示例在晚上 10:20 (22:20) 执行存储过程 sp_update_job。
USE msdb;
EXECUTE sp_add_job @job_name = 'TestJob';
BEGIN
    WAITFOR TIME '22:20';
    EXECUTE sp_update_job @job_name = 'TestJob',
        @new_name = 'UpdatedJob';
END;
GO
B. 使用 WAITFOR DELAY
以下示例在两小时的延迟后执行存储过程。
BEGIN
    WAITFOR DELAY '02:00';
    EXECUTE sp_helpdb;
END;
GO

 

二、应用场景

假设张三在招商银行开设了两个账号，一个是招商银行北京分行，一个是招商银行深圳分行，两个账号的余额分别是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了，所以事务二可以返回正确的结果。

 

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

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

 

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

 

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

 

三、总结

一个事务读到另外一个事务还没有提交的数据，我们称之为脏读。
解决方法：把事务隔离级别调整到READ COMMITTED，即把右上图中的SET TRAN ISOLATION LEVEL READ

UNCOMMITTED更改成下图中的SET TRAN ISOLATION LEVEL READ COMMITTED。这时我们重复上面的动作会发现事

务二会一直等到事务一执行完毕再返回结果，因为此时事务以已经把自己的更改ROLLBACK了，所以事务二可以

返回正确的结果。

---

一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。
解决方法：把事务隔离级别调整到REPEATABLE READ。在下图中使用SET TRAN ISOLATION LEVEL REPEATABLE

READ。这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果。

---

一个事务先后读取一个范围的记录，但两次读取的纪录数不同，我们称之为幻象读。

解决方法：把事务隔离级别调整到SERIALIZABLE。在下图中使用SET TRAN ISOLATION LEVEL SERIALIZABLE。

这时我们重复上面的动作会发现事务二会一直等到事务一执行完毕再返回结果。

---

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

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。
不可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
幻觉读是指当事务不是独立执行时发生的一种现象，例如 第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

此外，每一个锁都有一个特定的锁类型定义锁的行为。例如，如果事务希望防止其他事务更新数据但允许其他事务读取数据，那么在有些情况下可能为写操作锁住数据。在其他情况下，要求排他地锁定数据以防止其他事务对数据的任何访问。这种行为通过锁的兼容性来实现。每一种锁类型的定义都在同样的资源上与一些来自其他事务的特定锁兼容。由于一个特定的锁类型必须在SQL Server中授权所有数据访问操作，因此可以使用锁的兼容性来管理两个或两个以上的操作是否可以在同一时间用于同样的数据。SQL Server中最常用的锁类型为：

共享 (S) 锁 共享锁用于为读访问锁住数据。它们会防止其他事务更改数据，但不阻止读数据。共享锁与其他共享锁相兼容，这就允许多个事务在同一个被锁的资源上拥有一个共享锁。因此，事务可以并行地读同一个数据。
排他(X) 锁 排他锁用于每一次数据的更新。它们会阻止其他事务访问数据，因此一个排他锁与其他锁都不兼容。
更新 (U) 锁 更新锁是共享锁的一种特例。它们主要用于对UPDATE语句的支持。在UPDATE语句中，数据必须在它被更新前读取。因此，这需要一种锁类型在它读自己的数据时不阻止其他事务读数据。然而，当SQL Server开始更新数据的时候，它必须提升锁类型为排他锁。对于这种读操作，SQL Server使用与共享锁兼容但与其他更新锁不兼容的更新锁。因此，其他事务在数据由于UPDATE语句而被读取的时候可以读取，但其他UPDATE语句必须等待直到更新锁被释放。
意向(I)锁 意向锁是前面几种锁类型的变体，包括意向共享锁、意向排他锁等等。它们用于在低层次的锁上保护高层次的不接受的锁。考虑一下这种情况：一个事务在表中的行上有一个排他锁。此时不允许其他事务在整张表上获取排他锁。为了管理这种情况，会在高层次应用意向锁使其他事务知道一些资源已经在低层次上被锁定了。在这种情况下，事务会在行上保持一个排他锁，同时使用一个排他意向锁锁定页和表。