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

时间:2022-03-21 06:42:40

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

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

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

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

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

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

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

 

下表(1)列出了四种隔离

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

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

级别允许不同类型的行为。

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

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

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

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

先看下面这个例子:

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

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

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

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

(2)未确认的相关性(脏读)的再现
由表1可知,当事务的隔离级别为未提交读(READ UNCOMMITTED)的时候,允许脏读。
--在第一个连接中执行以下语句

 BEGIN TRAN
UPDATE 帐户表 SET 余额= WHERE 帐号='A'
WAITFOR DELAY '00:00:10' --等待10秒
UPDATE 帐户表 SET 余额= 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 余额= 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 余额= 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','')
COMMIT TRAN

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

转自 【朱二】(2006.3 转载请注明作者)

SQL Server中四类事务并发问题的实例再现(转)的更多相关文章

  1. SQL Server中锁与事务隔离级别

    SQL Server中的锁分为两类: 共享锁 排它锁 锁的兼容性:事务间锁的相互影响称为锁的兼容性. 锁模式 是否可以持有排它锁 是否可以持有共享锁 已持有排它锁 否 否 已持有共享锁 否 是 SQL ...

  2. sql server中的锁 事务锁 更新锁 保持锁 共享锁 你知道吗?

    锁定数据库的一个表 SELECT * FROM table WITH (HOLDLOCK) 注意: 锁定数据库的一个表的区别 SELECT * FROM table WITH (HOLDLOCK) 其 ...

  3. mysql,oracle,sql server中的默认事务隔离级别查看,更改

    未提交读(隔离事务的最低级别,只能保证不读取物理上损坏的数据) 已提交读(数据库引擎的默认级别) 可重复读 可序列化(隔离事务的*别,事务之间完全隔离) 可串行化比较严谨,级别高; MySQL m ...

  4. Sql Server中启用分布式事务小结

    1.web服务器与数据库服务器同时启动msdtc服务 2. 2台服务器做出如下配置: 控制面板->管理工具->组件服务->计算机->我的电脑->本地DTC .Net示例: ...

  5. Microsoft SQL Server中的事务与并发详解

    本篇索引: 1.事务 2.锁定和阻塞 3.隔离级别 4.死锁 一.事务 1.1 事务的概念 事务是作为单个工作单元而执行的一系列操作,比如查询和修改数据等. 事务是数据库并发控制的基本单位,一条或者一 ...

  6. SQL Server中事务、锁定和阻塞

    事务是什么 在SQL Server中事务是构成一个工作逻辑单元的一系列任务,也就说多个任务放在一起执行,这些任务要么全部执行成功,要么全部执行失败. 通过事务我们可以保证数据的完整性,例如:用户A给用 ...

  7. SQL Server中的锁 详解 nolock,rowlock,tablock,xlock,paglock

    摘自: http://www.myexception.cn/sql-server/385562.html 高手进 锁 nolock,rowlock,tablock,xlock,paglock 锁 no ...

  8. SQL Server优化技巧之SQL Server中的"MapReduce"

    日常的OLTP环境中,有时会涉及到一些统计方面的SQL语句,这些语句可能消耗巨大,进而影响整体运行环境,这里我为大家介绍如何利用SQL Server中的”类MapReduce”方式,在特定的统计情形中 ...

  9. SQL Server中 ldf 文件过大的解决方法

    在SQL Server中经常遇到事务日志变大的情况,除了将数据库设置为"自动收缩"外,还可以使用下面的SQL命令进行快速清除数据库中的事务日志,命令如下:  - 第一步:清空日志  ...

随机推荐

  1. 基于Fast Bilateral Filtering 算法的 High-Dynamic Range(HDR) 图像显示技术。

    一.引言 本人初次接触HDR方面的知识,有描述不正确的地方烦请见谅. 为方便文章描述,引用部分百度中的文章对HDR图像进行简单的描述. 高动态范围图像(High-Dynamic Range,简称HDR ...

  2. OC字符串NSString

    ========================== 面向对象编程进阶和字符串 ========================== Δ一.类的设计模式—单例 [单例]程序允许过程中,有且仅有一块内存 ...

  3. 今天是JQ 的slideUp 和 slideDown 的点击事件

    先贴代码,再讲详细事件 <!DOCTYPE html> <html> <head> <meta charset="utf-8"> & ...

  4. Linux C select函数详解

    select IO复用机制: http://www.cnblogs.com/hjslovewcl/archive/2011/03/16/2314330.html http://blog.csdn.ne ...

  5. Java集合的Stack、Queue、Map的遍历

    Java集合的Stack.Queue.Map的遍历   在集合操作中,常常离不开对集合的遍历,对集合遍历一般来说一个foreach就搞定了,但是,对于Stack.Queue.Map类型的遍历,还是有一 ...

  6. 使用proguard混淆android代码

    当前是有些工具比方apktool,dextojar等是能够对我们android安装包进行反编译,获得源代码的.为了降低被别人破解,导致源代码泄露,程序被别人盗代替码,等等.我们须要对代码进行混淆,an ...

  7. &lbrack;翻译&rsqb;在 &period;NET Core 中的并发编程

    原文地址:http://www.dotnetcurry.com/dotnet/1360/concurrent-programming-dotnet-core 今天我们购买的每台电脑都有一个多核心的 C ...

  8. ajax上传图片chrome报错net&colon;&colon;ERR&lowbar;CONNECTION&lowbar;RESET&sol;net&colon;&colon;ERR&lowbar;CONNECTION&lowbar;ABORTED

    网上搜了一下,base64图片太大,tomcat对post请求大小有默认限制,要在tomcat配置文件server.xml 加一个:maxPostSize="0",0表示无限制 & ...

  9. Ocelot-基于&period;NET Core的开源网关实现

    写在前面 API网关是系统内部服务暴露在外部的一个访问入口,类似于代理服务器,就像一个公司的门卫承担着寻址.限制进入.安全检查.位置引导等工作,我们可以形象的用下图来表示: 外部设备需要访问内部系统服 ...

  10. python学习笔记Day3

    set有点:1.访问速度快 2.天生解决了重复问题 tuple与set区别: 元组可重复,set不可重复创捷集合1 >>> s1.add('alex')>>> pr ...