SQL Server中四类事务并发问题[转]
1、SQL Server中四类事务并发问题的实例再现
朱二（2006.3 转载请注明作者）http://blog.csdn.net/netcoder/article/details/633153
本篇文章将用实例再现数据库访问中四类并发问题，希望能让初学者能对事务的并行性有进一步的理解。
首先，让我们先来了解一下并行问题以及事务隔离级别这两个概念。
在数据库中，假设如果没有锁定且多个用户同时访问一个数据库，则当他们的事务同时使用相同的数据时可能会发生问题。并发问题包括：
- 丢失或覆盖更新。
- 未确认的相关性（脏读）。
- 不一致的分析（非重复读）。
- 幻像读。
1、丢失更新
当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，会发生丢失更新问题。每个事务都不知道其它事务的存在。最后的更新将重写由其它事务所做的更新，这将导致数据丢失。
2、未确认的相关性（脏读）
当第二个事务选择其它事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能由更新此行的事务所更改。
3、不一致的分析（非重复读）
当第二个事务多次访问同一行而且每次读取不同的数据时，会发生不一致的分析问题。不一致的分析与未确认的相关性类似，因为其它事务也是正在更改第二个事务正在读取的数据。然而，在不一致的分析中，第二个事务读取的数据是由已进行了更改的事务提交的。而且，不一致的分析涉及多次（两次或更多）读取同一行，而且每次信息都由其它事务更改；因而该行被非重复读取。
4、幻像读
当对某行执行插入或删除操作，而该行属于某个事务正在读取的行的范围时，会发生幻像读问题。事务第一次读的行范围显示出其中一行已不复存在于第二次读或后续读中，因为该行已被其它事务删除。同样，由于其它事务的插入操作，事务的第二次或后续读显示有一行已不存在于原始读中。
上述四个问题都会引起数据的不一致性。我们把事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其它事务进行隔离的程度。较低的隔离级别可以增加并发，但代价是降低数据的正确性。相反，较高的隔离级别可以确保数据的正确性，但可能对并发产生负面影响。应用程序要求的隔离级别确定了 SQL Server 使用的锁定行为。
SQL-92 定义了下列四种隔离级别，SQL Server 支持所有这些隔离级别：
- READ UNCOMMITTED---未提交读（事务隔离的最低级别，仅可保证不读取物理损坏的数据）。
- READ COMMITTED---提交读（SQL Server 默认级别）。
- REPEATABLE READ---可重复读。
- SERIALIZABLE---可串行读（事务隔离的最高级别，事务之间完全隔离）。
| 隔离级别 | 脏读 | 不可重复读取 | 幻像 |
| 未提交读 | 是 | 是 | 是 |
| 提交读 | 否 | 是 | 是 |
| 可重复读 | 否 | 否 | 是 |
| 可串行读 | 否 | 否 | 否 |
为了再现以上四类问题，我们必须做一些准备工作：
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”。在实际应用的时候，采用何种隔离级别视具体情况而定，也可以采用显式“上锁”的方法控制事务隔离级别，具体方法请留意笔者的相关文章。
2、如何让多个用户同时调用同一个存储过程时依次执行，而不是同时执行
表中有一个"单号"字段，存放一个流水号(例如:0001,0002),现设计一个存储程来读这个表，生成一个最大流水号并返回这个生成的流水号，并将此流水号填入表中。在单用户操作时没有问题，一切正常，但现在发现如果有多个用户同时操作时此流水号有重复的。
2）共享锁
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran
若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒
3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran
在第二个连接中执行以下语句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran
同时执行，系统会检测出死锁，并中止进程
补充一点:
Sql Server2000支持的表级锁定提示
HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别
NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别
PAGLOCK 在使用一个表锁的地方用多个页锁
READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁
ROWLOCK 强制使用行锁
TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表
UPLOCK 强制在读表时使用更新而不用共享锁
应用程序锁:
应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁
处理应用程序锁的两个过程
sp_getapplock 锁定应用程序资源
sp_releaseapplock 为应用程序资源解锁
注意: 锁定数据库的一个表的区别
SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除
SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除
在第一个连接中执行以下语句
begin tran
select * from table1 holdlock -holdlock人为加锁
where B='b2'
waitfor delay '00:00:30' --等待30秒
commit tran
在第二个连接中执行以下语句
begin tran
select A,C from table1
where B='b2'
update table1
set A='aa'
where B='b2'
commit tran
若同时执行上述两个语句，则第二个连接中的select查询可以执行
而update必须等待第一个事务释放共享锁转为排它锁后才能执行 即要等待30秒
3）死锁
增设table2(D,E)
D E
d1 e1
d2 e2
在第一个连接中执行以下语句
begin tran
update table1
set A='aa'
where B='b2'
waitfor delay '00:00:30'
update table2
set D='d5'
where E='e1'
commit tran
在第二个连接中执行以下语句
begin tran
update table2
set D='d5'
where E='e1'
waitfor delay '00:00:10'
update table1
set A='aa'
where B='b2'
commit tran
同时执行，系统会检测出死锁，并中止进程
补充一点:
Sql Server2000支持的表级锁定提示
HOLDLOCK 持有共享锁，直到整个事务完成，应该在被锁对象不需要时立即释放，等于SERIALIZABLE事务隔离级别
NOLOCK 语句执行时不发出共享锁，允许脏读 ，等于 READ UNCOMMITTED事务隔离级别
PAGLOCK 在使用一个表锁的地方用多个页锁
READPAST 让sql server跳过任何锁定行，执行事务，适用于READ UNCOMMITTED事务隔离级别只跳过RID锁，不跳过页，区域和表锁
ROWLOCK 强制使用行锁
TABLOCKX 强制使用独占表级锁，这个锁在事务期间阻止任何其他事务使用这个表
UPLOCK 强制在读表时使用更新而不用共享锁
应用程序锁:
应用程序锁就是客户端代码生成的锁，而不是sql server本身生成的锁
处理应用程序锁的两个过程
sp_getapplock 锁定应用程序资源
sp_releaseapplock 为应用程序资源解锁
注意: 锁定数据库的一个表的区别
SELECT * FROM table WITH (HOLDLOCK) 其他事务可以读取表，但不能更新删除
SELECT * FROM table WITH (TABLOCKX) 其他事务不能读取表,更新和删除
