SQL Server中四类事务并发问题的实例再现

SQL Server中四类事务并发问题的实例再现

朱二（2006.3 转载请注明作者）

　　本篇文章将用实例再现数据库访问中四类并发问题，希望能让初学者能对事务的并行性有进一步的理解。
　　首先，让我们先来了解一下并行问题以及事务隔离级别这两个概念。
在数据库中，假设如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括： 

• 丢失或覆盖更新。
• 未确认的相关性（脏读）。
• 不一致的分析（非重复读）。
• 幻像读。 

下面让我们稍花点时间来解释一下这四类问题：
1、丢失更新
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。


2、未确认的相关性（脏读）
当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。

3、不一致的分析（非重复读）
当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。

4、幻像读
当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。

　　上述四个问题都会引起数据的不一致性。我们把事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。应用程序要求的隔离级别确定了  SQL Server  使用的锁定行为。

　　SQL-92 定义了下列四种隔离级别，SQL Server 支持所有这些隔离级别：

• READ UNCOMMITTED---未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。
  
• READ COMMITTED---提交读（SQL Server 默认级别）。
• REPEATABLE READ---可重复读。
• SERIALIZABLE---可串行读（事务隔离的最高级别，事务之间完全隔离）。

下表(1)列出了四种隔离级别允许不同类型的行为。

隔离级别	脏读	不可重复读取	幻像
未提交读	是	是	是
提交读	否	是	是
可重复读	否	否	是
可串行读	否	否	否

为了再现以上四类问题，我们必须做一些准备工作：
1、请用下面的脚本创建测试用的表。

--创建测试用数据库test
CREATE DATABASE test
GO
--创建测试用表
USE test
GO
CREATE TABLE 帐户表
(
帐号 CHAR(4),
余额 INT
)
GO
INSERT 帐户表
SELECT 'A',100
UNION ALL
SELECT 'B',200

2、请开启两个查询分析器程序，意在开启两个连接，模拟两个并行的事务。以下简称连接一和连接二。
测试正式开始：
（1）丢失更新的再现

先看下面这个例子：

--在第一个连接中执行以下语句

BEGIN TRAN
UPDATE 帐户表 SET 余额=101 WHERE 帐号='A' 
WAITFOR DELAY '00:00:10' --等待10秒
COMMIT TRAN

--接着马上使用第二连接执行下面的语句

BEGIN TRAN
UPDATE 帐户表 SET 余额=102 WHERE 帐号='A' 
COMMIT TRAN

我们会发现第二个连接里面的事务不能立刻执行，必须等待第一连接的事务完成之后才能执行下去。
这样就避免了“丢失更新”的问题，否则的话就会产生“丢失更新”的问题了。

丢失更新的问题是最为严重的一类问题，由表一可知，无论使用哪一种事务隔离级别，都不允许丢失更新的问题，因此该类问题无法再现。

（2）未确认的相关性（脏读）的再现
由表1可知，当事务的隔离级别为未提交读（READ UNCOMMITTED）的时候，允许脏读。

--在第一个连接中执行以下语句

BEGIN TRAN
UPDATE 帐户表 SET 余额=103 WHERE 帐号='A' 
WAITFOR DELAY '00:00:10' --等待10秒
UPDATE 帐户表 SET 余额=104 WHERE 帐号='A'
COMMIT TRAN

--接着马上使用第二连接执行下面的语句
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余额 FROM 帐户表 WHERE 帐号='A' 
COMMIT TRAN

我们会发现第二个连接的语句会立即返回，结果是103,但遗憾的是它读取的是脏数据。
如果我们把第二个连接的事务隔离级别设置为 READ COMMITTED、REPEATABLE READ 或者SERIALIZABLE，都可以避免“脏读”的发生。

（3）不一致的分析（非重复读）的再现
由表1可知，当事务的隔离级别为未提交读（READ UNCOMMITTED）或者READ COMMITTED的时候，便可在现此问题。
请测试下面这个例子（假设帐号A的余额为100）：

--在第一个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余额 FROM 帐户表 WHERE 帐号='A'
WAITFOR DELAY '00:00:10' --等待10秒
SELECT 余额 FROM 帐户表 WHERE 帐号='A'
COMMIT TRAN

--接着马上使用第二连接执行下面的语句
BEGIN TRAN
UPDATE 帐户表 SET 余额=10 WHERE 帐号='A'
COMMIT TRAN

我们会发现第一个连接中两次返回帐号A的余额不一样，第一次是100，第二次返回的是10，这是典型的“非重复读”问题。
如果把连接一的事务隔离级别设置为REPEATABLE READ 或者SERIALIZABLE，可防止此类问题。


（3）不一致的分析（非重复读）的再现
由表1可知，当事务的隔离级别为未提交读（READ UNCOMMITTED）或者READ COMMITTED的时候，便可在现此问题。
先看下面这个例子（假设帐号A的余额为100）：

--在第一个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
SELECT 余额 FROM 帐户表 WHERE 帐号='A'
WAITFOR DELAY '00:00:10' --等待10秒
SELECT 余额 FROM 帐户表 WHERE 帐号='A'
COMMIT TRAN

--接着马上使用第二连接执行下面的语句
BEGIN TRAN
UPDATE 帐户表 SET 余额=10 WHERE 帐号='A'
COMMIT TRAN

我们会发现第一个连接中两次返回帐号A的余额不一样，第一次是100，第二次返回的是10，这是典型的“非重复读”问题。

如果把连接一的事务隔离级别设置为REPEATABLE READ 或者SERIALIZABLE，可防止此类问题。

（4）幻像读的再现
由表1可知，当事务的隔离级别为READ UNCOMMITTED或者READ COMMITTED或者REPEATABLE READ的时候，便可再现此问题。
先看下面这个例子（假设帐号A的余额为100）：

--在第一个连接中执行以下语句

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--或者 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--或者 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM 帐户表 
WAITFOR DELAY '00:00:10' --等待10秒
SELECT * FROM 帐户表 
COMMIT TRAN

--接着马上使用第二连接执行下面的语句
BEGIN TRAN
INSERT INTO 帐户表 VALUES('C','300')
COMMIT TRAN

我们会发现第一个连接中在同一个事务中，同样的查询语句两次返回的结果集不一样，第二次返回的结果集中多了一条帐号为C的帐号，这是典型的“幻像读”问题。只有将连接一的事务隔离级别设置为SERIALIZABLE，才可防止此类问题。
　　总结：为了避免事务并发带来的问题，可采用较高的事务隔离级别，但因此会降低事务的并行性；反过来如果追求高的并行性而使用较低的事务隔离级别，又容易带来并发的问题。因此SQL Server采用默认隔离级别是相对比较低的“READ COMMITTED”。在实际应用的时候，采用何种隔离级别视具体情况而定，也可以采用显式“上锁”的方法控制事务隔离级别，具体方法请留意笔者的相关文章。