第9章 事物处理

于 2019-07-31 11:00:12 发布
阅读量906 收藏 3
点赞数
分类专栏： SQL 文章标签： SQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_15788231/article/details/97897003
版权

第9章 事物处理

事务是单个的工作单元。如果某一事务成功，则在该事务中进行的所有数据修改均会提交，成为数据库中的永久组成部分；如果事务遇到错误且必须取消或回滚，则所有数据修改均被清除。

9.1 事务概述
事务是包含了一组数据库操作命令的一个操作序列，事物中所有的命令作为一个整体一起向系统提交或撤销操作请求，即数据库命令执行成功、或者执行不成功。

9.1.1 事务介绍
在数据库系统上执行并发操作时事务是作为最小的控制单元来使用的。对于数据库管理系统而言，事务是一个不可分割的工作逻辑单元。如果一次最多允许一个用户使用，则该系统为单用户系统；如果允许多个用户同时使用，则该系统为多用户系统。在多用户系统中，多个用户执行并发操作是经常碰到的情况，事务可以作为执行这种并发操作的最小控制单元。例如，保险公司、证券交易以及银行的系统都是允许用户同时提交事务操作的多用户系统。

使用事务时应该尽量使事务短些，因为比较长的事务不仅增加了事务占用数据的时间，同时也延长了其他访问者要等待的时间。例如，在事物中使用循环语句WHILE时，一定要先确认循环的长度和占用的时间，使这种循环在完成相应的功能之前，一定要确保循环尽可能地短。在开始事务之前，一定要了解需要用户交互式操作才能得到的信息。这样，在事务的进行过程中，就可以避免进行一些耗费时间的交互式操作，缩短事务进程的时间。

在定义事务时，应该尽可能地使用如INSERT、UPDATE和DELETE等一些数据操纵语句，因为这些数据操纵语句相对而言占用的数据处理时间会短些，同时也可以使用一些数据操纵语言，但是在使用这些数据操纵语言时一定要在这些语句中使用条件判断语句，使得这些数据操纵语言涉及尽可能少的记录，从而缩短事务处理的时间。

9.1.2 事务控制的必要性
在介绍事务控制之前，首先通过一个简单的实例讲解事务控制的必要性。
比如，用户经常用到的银行转账操作。一般而言，转账程序可表示为如图9.1所示的几个步骤。
在这里插入图片描述
图9.1 银行转账程序流程

假设上述操作每执行一步，均对数据库完成进行操作。那么如果转账程序执行到第③步时，B1>B，则银行数据库中，将B1的值设置为B1-B，即将金额B转出。而后执行第④步，假设在执行第④步时，由于硬件故障而突然中断了与银行数据库的连接，那么第④步就无法执行，也就是说，账户A2的金额并没有改变，这种情况显然是不允许出现的。

解决上述问题的方法就是将这些数据库操作放在一个事务（Transaction）中，所有这些操作步骤，要么都执行，要么都不执行。

9.1.3 事务的特性
事务是用户对数据库进行的一系列操作的集合。事务具有ATOMIC（原子性）、CONSESTENT（一致性）、ISOLATED（隔离性）和DURABLE（永久性）4个特性。其具体含义如下所示：
原子性：指事务全有或全无的性质。也就是说，事务是不可分割的操作序列或工作单元，事物由事务管理子系统完成。事务中的所有元素必须作为一个整体提交或回滚。例如，在银行系统中，通过一个事务来更新两个账户余额，如果该事务提交了，则这两个账户的数据就会更新，如果事务在成功更新这两个账户之前终止，则不会更新这两个账户余额，并且会撤销对任何账户余额的修改，如图9.2所示。
在这里插入图片描述
图9.2 转账程序流程

  • 一致性：事务中任何数据的变化都必须符合数据定义的规则。当事务完成时，数据必须处于一致状态。事务不能使数据存储处于不稳定的状态，即通过事务对数据所做的修改不能损坏数据。在关系型数据库中，所有的规则必须应用到事务的修改上，以便维护所有数据的完整性。例如，在银行系统中，事务开始前，两个账户余额的总额处于一致的状态，事务进行过程中，一个账户余额改变，另一个账户余额未改变，则这两个账户余额的总额处于不一致状态，当事务完成后，账户余额的总额就恢复为一致的状态。
  • 隔离性：多个事务同时进行，它们之间应该互不干扰。当事务执行修改数据操作时，如果任何其他进程正在同时使用相同的数据，则直到该事务成功提交后，对数据的修改才能生效。
  • 永久性：—旦由事务引发了变化，事务提交以后，所做的工作就被永久保存下来，即使硬件和应用程序发生错误，也必须保证对数据所做的修改不受影响。

9.1.4 事务的状态
一般将事务的执行划分为下面的几种状态：

  • 活动状态：事务执行时所处于的状态。
  • 部分提交状态：最后一条语句被执行后。
  • 失败状态：发现正常的执行不能继续后。
  • 中止状态：事务回滚并且数据库已被恢复到事务开始执行前的状态后。
  • 提交状态：事务成功完成后。

提交的或中止的事务被称为已经结束的事务。事务执行时，各种状态的关系可表示为图9.3所示。
在这里插入图片描述
图9.3 事务的各种状态的关系

9.1.5 事务的分类
在SQL中，将事务分为3个种类，即显式事务、隐式事务和自动提交事务。下面分别介绍一下这3种类型的事务。

  • 显式事务又称为用户定义事务，是指显式地定义其开始和结束的事务。当使用BEGING TRAN和COMMIT语句时发生显式事务。
  • 隐式事务是指在当前事务提交或回滚后自动开始的事务，该事务需要用COMMIT语句和ROLLBACK语句回滚事务或结束事务，在回滚后又自动开始一个新的事务。
  • 自动事务是指能够自动执行并且能够自动回滚的事务，该事务中，当一个语句成功执行后，事务被自动提交，当执行过程中产生错误时，则事务被回滚。

9.1.6 SQL中事务的执行
在SQL中，与事务处理相关的控制语句及其功能如表9.1所示。
表9.1 SQL中与事务处理相关的控制语句及其功能

Transact-SQL语句	功    能
SET TRANSACTION ISOLATION LEVEL	设置事务的隔离级别
BEGIN TRANSACTION	开始事务
SAVE TRANSACTION	在事务中建立保存点。保存点用来标识重新开始时事务的位置，相当于断点的作用
COMMIT TRANSACTION	提交事务
COMMIT WORK	等同于COMMIT TRANSACTION
ROLLBACK TRANSACTION	终止事务，取消事务进行的任何操作，返回事务开始的状态或者返回保存点
ROLLBACK WORK	等同于ROLLBACK TRANSACTION

说明：在SQL Server中，事务执行的一般过程为：首先通过SET TRANSACTION ISOLATION LEVEL设置事务的属性，再由BEGIN TRANSACTION语句开始执行事务。

事务的结束可以有两种方式：由COMMIT TRANSACTION语句提交事务和由ROLLBACK TRANSACTION语句回滚事务。

  • COMMIT提交事务：该语句将成功地结束当前事务，并永久地保存事务中对于数据库的所有改变。而在未提交事务以前，这些改变是暂时的。一般情况下，在其他事务中不能看到本事务中对数据库所做的修改。
  • ROLLBACK回滚事务：该语句也结束当前事务，但它将撤销该事务所做的全部工作，即事务中对于数据库的任何改变都将无效。回滚一般是回到事务开始时的状态，但也可以在事务中设置保存点，然后回滚到事务指定的保存点。

在SQL Server中，典型事务的执行流程如图9.4所示。
在这里插入图片描述
图9.4 SQL Server中典型事务的执行流程
当事务开始时，数据库处于初始状态。然后开始执行SQL操作语句，如果执行成功，则执行COMMIT语句提交事务，这时才更新数据库数据，并终止事务。如果SQL操作语句执行失败，则执行ROLLBACK语句，数据库恢复到初始状态。

9.2 使用事务
事务控制是指为了确保数据库数据的完整性和有效性，而将一系列不可分割的数据库操作作为整体来执行的操作。事务控制主要包括开始事务和终止事务2个操作，本节中主要讲解有关事务控制与锁定方面的相关知识。

9.2.1 开始事务—BEGIN TRANSACTION
在SQL中使用START TRANSACTION语句实现开始事务。START TRANSACTION语句用来设置事务的属性，主要用于对事务的并发控制。
对于不同的数据库产品，事务的开始方式并不完全相同。下面分别介绍一下在SQL Server数据库和Oracle数据库中如何实现开始事物。

1．SQL Server
在SQL Server中有隐式事务、显式开始和自动提交3种开始事务的方式，在默认的情况下，事物的开始方式为自动提交的方式。
在SQL Server中通过BEGIN TRANSACTION命令实现开始事物。
【语法说明】
在SQL Server中实现开始事务的语法结构表示如下。

BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable]
[WITH MARK['description']]]

代码中的transaction_name表示事物的名称，@tran_name_variable表示用变量来指定事物的名称，并且变量只能声明为CHAR、NCHAR、VARCHAR和NVARCHAR这几种数据类型，WITH MARK表示指定在日志中的标记事务，而description则表示描述该标记字符串。

注意：也可是使用START TRANSACTION命令实现开始事务，其用法和功能与BEGIN TRANSACTION命令相类似。

下面通过一个示例来说明在SQL Server中如何实现开始事务。
【上机实战】
定义一个事务Update_Age，实现通过事务将学生信息表中（student）所有学生的年龄增长一岁。为了便于读者进行分析比较，现将学生信息表（student）中所有学生的信息显示出来。

SELECT * FROM student
ORDER BY stNo

代码执行以后，运行结果如图9.5所示。
在这里插入图片描述
图9.5 student表中的数据信息
在SQL SERVER中实现开始事务的代码如下所示。

BEGIN TRANSACTION Update_Age
 WITH MARK '将所有学生的年龄增加一岁'
 UPDATE student
 set SAge=SAge+1

代码执行以后，事务被执行，此时查看学生信息表（student）中的学生信息。
注意：该事务只被执行，没有被提交，其事务执行的结果并没有真正地保存到数据库当中。

SELECT * FROM student
ORDER BY stNo

代码执行以后，运行结果如图9.6所示。
在这里插入图片描述
图9.6 执行事务后student表中的数据信息

2．Oracle数据库
在Oracle中，缺省情况下，任何一个DML语句都会开始一个事务，直到用户发出COMMIT或ROLLBACK操作，这个事务才会结束，这与SQL Server的隐式事务模式相似。当执行到下一条DDL语句时，即使用户没有发出COMMIT或ROLLBACK操作，Oracle系统也会提交当前事务，并开始一个新的事务，如下面的示例所示。
【上机实战】
如在ORACLE中执行下面的SQL语句。在执行下面的SQL语句之前，现看一些员工信息表（T_EMPLOY）中的数据信息。

SELECT * FROM T_EMPLOY ORDER BY E_ID

语句执行以后，其执行结果如图9.7所示。
在这里插入图片描述
图9.7 员工信息表（T_EMPLOY）中的数据信息
如在ORACLE中演示开始事务的实现代码如下所示。

INSERT INTO T_EMPLOY(E_ID,E_NAME,E_BM,E_GZ) VALUES('1011','赵丹','人事部'，1800);
DELETE FROM T_EMPLOY WHERE E_ID='1006';
CREATE TABLE TABLE1(COL INT);
INSERT INTO T_EMPLOY(E_ID,E_NAME,E_BM,E_GZ) VALUES('1012','李丹','信息部'，1800);
ROLLBACK;

代码执行以后，查询员工信息表（T_EMPLOY）中的数据信息如图9.8所示。
在这里插入图片描述
图9.8 执行开始事务之后T_EMPLOY数据表中的数据信息
从本示例中可以看出，从DML语句INSERT INTO和DELETE语句开始，Oracle开始一个事务，而当Oracle执行CREATE TABLE语句之前，由于即将开始一个DDL语句，所有就提交了事务，而在CREATE TABLE执行后，会自动发出COMMIT命令，所有只有插入编号（E_ID）为“1012”的数据记录被回滚。

9.2.2 提交事务—COMMIT
使用COMMIT TRANSACTION语句，指示一个成功的隐性事务或显式事务的结束。如果@@TRANCOUNT为1，COMMIT TRANSACTION使得自从事务开始以来所执行的所有数据修改成为数据库的永久部分，释放事务所占用的资源，并将@@TRANCOUNT减少到 0。如果@@TRANCOUNT大于1，则COMMIT TRANSACTION使@@TRANCOUNT按1递减，并且事务将保持活动状态。
【语法说明】
提交事务的语法结构如下所示。

COMMIT { TRAN | TRANSACTION } [ transaction_name | @tran_name_variable ] ]

其中，transaction_name只是帮助用户阅读，没有任何意义，SQL Server在执行时，将忽略该参数。

注意：仅当事务被引用所有数据的逻辑都正确时，Transact-SQL程序员才应发出COMMIT TRANSACTION命令。

【上机实战】
删除员工信息表（T_EMPLOY）中员工编号为“1011”的员工信息，其实现的代码如下所示。

DELETE FROM T_EMPLOY WHERE E_ID='1011';
COMMIT

代码执行以后，事务被提交，符合条件的数据信息被删除，提交事务之后员工信息表（T_EMPLOY）中的数据信息如图9.9所示。
在这里插入图片描述
图9.9 提交事务后T_EMPLOY数据表中的数据信息
当在嵌套事务中使用时，内部事务的提交并不释放资源或使其修改成为永久修改。只有在提交了外部事务时，数据修改才具有永久性，而且资源才会被释放。即当@@TRANCOUNT大于1时，每发出一个COMMIT TRANSACTION命令只会使 @@TRANCOUNT按1递减。当@@TRANCOUNT最终递减为0时，才将提交整个外部事务。

下面通过一个实例来说明嵌套事务中，COMMIT TRANSACTION命令与@@TRANCOUNT值的关系。
在SQL SERVER数据库中，创建一个表TestTran，生成三个级别的嵌套事务，然后提交该嵌套事务。在创建和提交每个事务时，观察@@TRANCOUNT的值的变化。

USE Library
CREATE TABLE TestTran (Cola INT PRIMARY KEY, Colb CHAR(5))	--创建表
BEGIN TRANSACTION OuterTran			--开始事务，此时@@TRANCOUNT值为1
PRINT '执行事务OuterTran后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran VALUES (1, 'aaaaa')	--向表中添加数据
BEGIN TRANSACTION Inner1--开始事务，此时@@TRANCOUNT值为2
PRINT '执行事务Inner1后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran VALUES (2, 'bbbbb')	--向表中添加数据
BEGIN TRANSACTION Inner2--开始事务，此时@@TRANCOUNT值为3
PRINT '执行事务Inner2后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran VALUES (3, 'ccccc')	--向表中添加数据
COMMIT TRANSACTION Inner2					--提交事务Inner2
PRINT '提交事务Inner2后，@@TRANCOUNT的值为： '
    + CAST(@@TRANCOUNT AS NVARCHAR(10)); 
COMMIT TRANSACTION Inner1					--提交事务Inner1
PRINT '提交事务Inner1后，@@TRANCOUNT的值为： '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
COMMIT TRANSACTION OuterTran				--提交事务OuterTran
PRINT '提交事务OuterTran后，@@TRANCOUNT的值为： '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
SELECT * FROM TestTran						--查询表中的数据

代码执行以后，其执行结果如图9.10所示。
在这里插入图片描述
图9.10 事务的执行结果
从本示例中可以看出，每执行一个BEGIN TRANSACTION语句，@@TRANCOUNT的值加1，而每执行一个COMMIT TRANSACTION语句，@@TRANCOUNT的值减1。
说明：COMMIT WORK语句的使用与COMMIT TRANSACTION完全相同，只是其后不能带有任何标识名称。

9.2.3 回滚事务—ROLLBACK
使用ROLLBACK TRANSACTION语句，可以将显式事务或隐性事务回滚到事务的起点，或事务内的某个保存点。执行ROLLBACK TRANSACTION语句，系统清除自事务的起点或到某个保存点所做的所有数据修改，并释放由事务控制的资源。
【语法说明】
ROLLBACK TRANSACTION语句的使用语法如下所示。

ROLLBACK { TRAN | TRANSACTION } 
     [ transaction_name | @tran_name_variable
     | savepoint_name | @savepoint_variable ]

其中，参数savepoint_name是保存点，即SAVE TRANSACTION语句中的savepoint_name，在下节将详细介绍。

说明：在嵌套事务中，如果使用ROLLBACK TRANSACTION语句，指定的transaction_ name值必须是最外层BEGIN
TRANSACTION语句指定的transaction_name值。

嵌套事务时，该语句将所有内层事务回滚到最外面的BEGIN TRANSACTION语句。无论在哪种情况下，ROLLBACK TRANSACTION都将@@TRANCOUNT系统函数的值减小为0。ROLLBACK TRANSACTION savepoint_name不减小@@TRANCOUNT。

注意：在执行 COMMIT TRANSACTION语句后不能回滚事务。

【上机实战】
在ORACLE数据库中，删除图书信息表（T_BOOKINFO）中所有的数据信息，然后使用ROLLBACK语句回滚事务，其实现的代码如下所示。

DELETE FROM T_BOOKINFO;
ROLLBACK

代码执行以后，查询图书信息表（T_BOOKINFO）中的数据信息。

SELECT * FROM T_BOOKINFO

代码执行以后，由于执行了事务回滚的操作，因此图书信息表（T_BOOKINFO）中的数据信息没有被删除，如图9.11所示。
在这里插入图片描述
图9.11 回滚事务的应用示例
下面再通过一个实例讲解在嵌套事务中，回滚操作及@@TRANCOUNT值的变化。
在SQL SERVER数据库中，创建一个表TestTran2，生成三个级别的嵌套事务，使用一个COMMIT TRANSACTION语句后，回滚事务，同步观察@@TRANCOUNT的值的变化以及最终的操作结果，其实现的代码如下所示。

CREATE TABLE TestTran2 (Cola INT PRIMARY KEY, Colb CHAR(5))	--创建表
BEGIN TRANSACTION OuterTran		--开始事务，此时@@TRANCOUNT值为1
PRINT '执行事务OuterTran后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran2 VALUES (1, 'aaaaa')	--向表中添加数据
BEGIN TRANSACTION Inner1			--开始事务，此时@@TRANCOUNT值为2
PRINT '执行事务Inner1后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran2 VALUES (2, 'bbbbb')	--向表中添加数据
BEGIN TRANSACTION Inner2			--开始事务，此时@@TRANCOUNT值为3
PRINT '执行事务Inner2后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran2 VALUES (3, 'ccccc')	--向表中添加数据
COMMIT TRANSACTION Inner2					--提交事务Inner2
PRINT '提交事务Inner2后，@@TRANCOUNT的值为： '
    + CAST(@@TRANCOUNT AS NVARCHAR(10)); 
ROLLBACK TRANSACTION 						--回滚事务
PRINT '回滚事务后，@@TRANCOUNT的值为： '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
SELECT * FROM TestTran2						--查询表中的数据

代码执行以后，其执行结果如图9.12所示。
在这里插入图片描述
图9.12 事务的执行结果
从本示例中可以看出，使用ROLLBACK TRANSACTION语句回滚事务后，@@TRANCOUNT的值变为0，且回滚到最外面的BEGIN TRANSACTION语句，即对表中的所有添加数据操作均被取消，所以最后查询TestTran2表中没有任何数据。
实例代码的执行过程如图9.13所示。
在这里插入图片描述
图9.13 实例代码的执行过程
9.2.4 设置保存点—SAVEPOINT
用户可以使用SAVE TRANSACTION语句在事务内设置保存点或标记。保存点可以定义在按条件取消某个事务的一部分后，该事务可以返回的一个位置。
【语法说明】
SAVE TRANSACTION语句的语法格式如下所示。

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }

其中，参数savepoint_name为分配给保存点的名称，保存点名称必须符合标识符的规则，但长度不能超过32个字符。
在事务中允许有重复的保存点名称，但指定保存点名称的ROLLBACK TRANSACTION语句只将事务回滚到使用该名称的最近的SAVE TRANSACTION。

注意：当事务开始后，事务处理期间使用的资源将一直保留，直到事务完成。当将事务的一部分回滚到保存点时，将继续保留资源直到事务完成（或者回滚整个事务）。

如果将事务回滚到保存点，则根据需要必须完成其他剩余的Transact-SQL语句和COMMIT TRANSACTION语句，或者必须通过将事务回滚到起始点完全取消事务。
【上机实战】
在SQL SERVER数据库中，创建一个表TestTran3，而后开始一个事务，通过INSERT语句向表中添加数据，在添加的过程中，设置两个保存点。在事务的执行过程中，进行回滚到保存点和提交事务等操作，并实时观察@@TRANCOUNT的值，其实现的代码如下所示。

CREATE TABLE TestTran3 (Cola INT PRIMARY KEY, Colb CHAR(5))--创建表
BEGIN TRANSACTION 						--开始事务，此时@@TRANCOUNT值为1
PRINT '开始事务后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran3 VALUES (1, 'aaaaa')	--向表中添加数据
SAVE TRANSACTION  save1					--设置保存点save1
INSERT INTO TestTran3 VALUES (2, 'bbbbb')	--向表中添加数据
SAVE TRANSACTION  save2					--设置保存点save2
INSERT INTO TestTran3 VALUES (3, 'ccccc')	--向表中添加数据
ROLLBACK TRANSACTION save2					--回滚到保存点save2
PRINT '回滚到保存点save2后，@@TRANCOUNT的值为：'
    + CAST(@@TRANCOUNT AS NVARCHAR(10))
INSERT INTO TestTran3 VALUES (4, 'ddddd')	--向表中添加数据
COMMIT TRANSACTION						--提交事务
PRINT '提交事务后，@@TRANCOUNT的值为： '
    + CAST(@@TRANCOUNT AS NVARCHAR(10));
SELECT * FROM TestTran3					--查询TestTran3表中的数据

代码执行以后，其执行的结果如图9.14所示。
在这里插入图片描述
图9.14 事务的执行结果
在本示例中，当事务开始后，事务处理期间使用的资源将一直保留，对本实例而言，在提交事务之前，save1和save2保存点处的数据库中的数据一直保存在系统资源中，任何时候均可回滚到保存点处的数据状态。
本示例代码的执行过程可表示为如图9.15所示。
在这里插入图片描述
图9.15 实例代码的执行过程
9.2.5 回滚到保存点–ROLLBACK TO SAVEPOINT
使用ROLLBACK TO SAVEPOINT语句可以将事务回滚到指定的还原点。
【语法说明】
ROLLBACK TO SAVEPOINT语句的语法格式如下所示。

ROLLBACK TO SAVEPOINT savepoint_name

其中，参数savepoint_name为分配给保存点的名称，保存点名称必须符合标识符的规则，但长度不能超过32个字符。
【上机实战】
删除图书信息表（T_BOOKINFO）中图书编号为“1005”的数据信息，然后设置数据还原点T1，接下来向图书信息表（T_BOOKINFO）中插入一条数据信息。为了便于比较，现将图书信息表（T_BOOKINFO）中的数据信息显示出来。

SELECT * FROM T_BOOKINFO

代码执行以后，其执行结果如图9.16所示。
在这里插入图片描述
图9.16 图书信息表（T_BOOKINFO）中的数据信息
演示回滚到事务保存点的代码如下所示。

DELETE FROM T_BOOKINFO WHERE B_ID='1005';
SAVEPOINT T1;
INSERT INTO T_BOOKINFO(B_ID,B_NAME,B_PUBLISH,B_AUTHOR) 
VALUES('1011','SQL基础','清华大学出版社','赵丽');
ROLLBACK TO SAVEPOINT T1

代码执行以后，查询图书信息表（T_BOOKINFO）中的数据信息如图9.17所示。
在这里插入图片描述
图9.17 回滚到事务保存点的演示示例
从本示例中可以看出，由于事务只回滚到事务保存点T1，因此在事务保存点T1之前的删除数据记录的操作没有被回滚（数据表中图书编号为“1005”的数据记录被真正地删除了）；而事务保存点T1之后插入数据记录的操作被回滚了，因此在数据表中看不到被插入的数据记录。

9.3 并发控制
前面讲解的对数据库的读写操作都是单用户的操作，即同一时刻只能完成一个操作。实际上，可以实现多个用户同时访问一个数据库。当多个事务同时对数据库中的同一数据进行操作时，就需要对该操作进行并发控制。在本节中主要讲解有关并发控制方面的相关知识。
9.3.1 并发操作的问题
在执行并发操作时，通常情况下可能会发生下面几个方面的问题：

  • 丢失更新：是指当多个事务同时操作同一行数据，并且基于最初选定的值被更改时，每个事务都不知道其他事务的存在。最后的更新将重写由其他事务所做的更新。这将导致数据丢失现象的发生。
  • 脏读：是指当第二个事务选择其他事务正在更新的行时，会发生未确认的相关性问题。第二个事务正在读取的数据还没有确认并且可能被更新此行的事务所更改。
  • 非重复读：非重复读和脏读类似，是指当第二个事务多次访问同一行数据而且每次读取不同的数据时，会发生不一致的问题。
  • 虚读：是指当对某行执行插入或删除操作时，而该行属于某个事务正在读取行的范围时，会发生虚读的问题。

9.3.2 事务隔离级别
事务的隔离可以理解为锁定该事务正在使用的数据以减少其他事务对这些数据的影响，保证数据的一致性。在多用户系统中，经常出现多个事务同时需要操作某一个数据库中的数据的情况，为了防止事务之间的相互影响，提高数据库数据的安全性和完整性，数据库系统提供了隔离的机制。
事务准备接受不一致数据的级别称为隔离级别。隔离级别是一个事务必须与其他事务进行隔离的程度。事务的隔离等级可分为以下4种：

  • 未提交读（Read Uncommitted）：未提交读是最低等级的事务隔离，仅可保证不读取物理损坏的数据。
  • 提交读（Read
    Committed）：提交读隔离级别比未提交读隔离级别高一层。提交读隔离级别是大多数主流数据库的默认事务隔离等级，其保证了一个事务不会读到另一个并行事务已修改但未提交的数据，避免了“脏读取”情况的发生。
  • 可重复读（Repeatable
    Read）：可重复读隔离级别保证了一个事务不会修改已经由另一个事务读取但回滚的数据。避免了“脏读取”和“不可重复读取”情况的发生。
  • 可串行化（Serializable）：可串行化隔离级别是最高等级的事务隔离，是指将事务以一种顺序的方式连接起来，以防止一个事务影响其他事务。

9.3.3 设置事务的隔离级别
设置事务的隔离级别是通过SET TRANSATION语句实现的。
【语法说明】
在SQL SERVER数据库中，SET TRANSATION语句的语法格式如下所示。

SET TRANSACTION ISOLATION LEVEL
    { READ UNCOMMITTED
    | READ COMMITTED
    | REPEATABLE READ
    | SNAPSHOT
    | SERIALIZABLE
    }

上述语句中，SNAPSHOT（快照）为SQL Server提供的隔离级别，在该隔离级别下，事务只能识别在其开始之前提交的数据修改，其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。

说明：必须将ALLOW_SNAPSHOT_ISOLATION数据库选项设置为ON，才能开始一个使用SNAPSHOT隔离级别的事务。如果使用SNAPSHOT隔离级别的事务访问多个数据库中的数据，则必须在每个数据库中将ALLOW_SNAPSHOT_ISOLATION都设置为ON。

设置隔离级别的目的就是实现并发控制，各隔离级别可能存在的并发访问异常如表9.1所示。
表9.1 各隔离级别下可能存在的并发访问异常

隔离级别	丢失更新	读脏数据	非重复读	幻像读
READ UNCOMMITTED	可能	可能	可能	可能
READ COMMITTED	防止	防止	可能	可能
REPEATABLE READ	防止	防止	防止	可能
SNAPSHOT	防止	防止	防止	防止
SERIALIZABLE	防止	防止	防止	防止

一次只能设置一个隔离级别选项，而且设置的选项将一直对那个连接始终有效，直到显式更改该选项为止。事务中执行的所有读取操作都会在指定的隔离级别的规则下运行，除非语句的FROM子句中的表提示为表指定了其他锁定行为。
当事务进行时，用户可以随时将事务从一个隔离级别更改为另一个隔离级别。将事务从一个隔离级别更改为另一个隔离级别之后，便会根据新级别的规则对更改后读取的资源执行保护，而更改前读取的资源将继续根据先前级别的规则进行保护。
【上机实战】
在SQL Server中，通过指定事务的不同隔离级别实现并发控制，观察不同隔离下，并发访问的实现过程。
为了便于实例讲解，首先在数据库中创建4个表：Test_Table1、Test_Table2、Test_Table3和Test_Table4，为其添加相同的数据，其实现的代码如下所示。

CREATE TABLE Test_Table1 (Col1 INT PRIMARY KEY, Col2 CHAR(5))--创建表
INSERT INTO Test_Table1 VALUES (1, 'aaaaa')		--向表中添加数据
INSERT INTO Test_Table1 VALUES (2, 'bbbbb')		--向表中添加数据
INSERT INTO Test_Table1 VALUES (3, 'ccccc')		--向表中添加数据
SELECT * INTO Test_Table2 FROM Test_Table1			--复制到表Test_Table2
SELECT * INTO Test_Table3 FROM Test_Table1			--复制到表Test_Table3
SELECT * INTO Test_Table4 FROM Test_Table1			--复制到表Test_Table4

代码执行以后，Test_Table1、Test_Table2、Test_Table3和Test_Table4表中的数据如图9.18所示。
在这里插入图片描述
图9.18 数据表中的数据信息
上面的示例简单地讲解了事务隔离级别并发控制的实现，下面再分别讲解一下不同事务隔离级别并发控制的实现。
1．设置和使用READ UNCOMMITTED隔离级别
在READ UNCOMMITTED隔离级别下，可以读取已由其他事务修改但尚未提交的行。实际上，该隔离级别与没有锁定数据库完全相同，因此并发访问可能出现的4个问题都可能发生。
【上机实战】
假设有用户M和用户N使用READ UNCOMMITTED隔离级别，通过事务访问数据库中Test_Table1表中的数据。
用户M首先访问，其操作为：首先开启事务M1，更新Test_Table1表中的数据，20秒后回滚事务M1。而后开启事务M2，执行SELECT语句查询Test_Table1表中的数据，20秒后，再执行一次SELECT语句，查询Test_Table1表中的数据，而后提交事务M2，其实现的代码如下所示。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	--设置隔离级别
USE Library
Declare @now_time VARCHAR(8)						--定义变量
BEGIN TRANSACTION M1								--开始事务M1
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M1的开始时刻为：'+ @now_time
UPDATE Test_Table1 set Col2='MMMMM' 				--更新操作
WAITFOR  DELAY '00:00:20' 							--等待20秒
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M1的UPDATE语句执行时刻为：'+ @now_time
ROLLBACK TRANSACTION M1								--回滚结束事务M1
BEGIN TRANSACTION M2								--开始事务M2
PRINT '事务M2第一个SELECT语句的查询结果为：'
SELECT  * FROM Test_Table1 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M2第一个SELECT语句的执行时刻为：'+ @now_time
WAITFOR  DELAY '00:00:20' 							--等待20秒
PRINT '事务M2第二个SELECT语句的查询结果为：'
SELECT  * FROM Test_Table1 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M2第二个SELECT语句的执行时刻为：'+ @now_time
COMMIT TRANSACTION M2								--提交结束事务M2

在用户M访问后（5~20秒之间），用户N也开始访问数据库中的Test_Table1表，其操作为：首先开启事务N，使用SELECT语句查询Test_Table1表中的数据，18秒后，使用UPDATE语句更新Test_Table1表中的数据，而后提交事务N，其实现的代码如下所示。

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED		--设置隔离级别
    USE Library
    Declare @now_time VARCHAR(8)						--定义变量
    WAITFOR  DELAY '00:00:5' 							--等待5秒
    BEGIN TRANSACTION N								--开始事务N
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT '事务N的开始执行时刻为：'+ @now_time
    PRINT '事务N的SELECT语句的查询结果为：'
    SELECT  * FROM Test_Table1 
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT '事务N的SELECT语句的执行时刻为：'+ @now_time
    WAITFOR  DELAY '00:00:18' 							--等待18秒
    UPDATE Test_Table1 set Col2='NNNNN' 				--更新操作
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT '事务N的UPDATE语句的执行时刻为：'+ @now_time
    COMMIT TRANSACTION N								--提交结束事务N

同样，在SQL Server 2005的Management Studio中，开启两个查询窗口，分别模拟用户M和用户N对数据库的并发访问。分别键入上面的代码，而后先后执行（间隔2s）用户M和用户N的代码，最终得到的查询结果分别如图9.19和图9.20所示。
在这里插入图片描述在这里插入图片描述
图9.19 用户M的执行结果 图9.20 用户N的执行结果
从结果分析，用户M和用户N对Test_Table1表中数据的访问过程可表示为图9.21所示。
在这里插入图片描述
图9.21 实例的执行过程
可见，使用READ UNCOMMITTED隔离级别时，用户M和用户N对数据库的并发访问，用户N造成了数据的“脏读”，而用户M在事务M2中，形成了非重复读的并发问题。
2．设置和使用READ COMMITTED隔离级别
在READ COMMITTED隔离级别下，指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据，从而产生非重复读取和幻像数据。
【上机实战】
假设有用户M和用户N使用READ COMMITTED隔离级别，通过事务访问数据库中Test_Table2表中的数据。其执行的操作与实例7中的完全相同，其实现的代码如下所示。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED--设置隔离级别
USE Library
Declare @now_time VARCHAR(8)						--定义变量
BEGIN TRANSACTION M1								--开始事务M1
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M1的开始时刻为：'+ @now_time
UPDATE Test_Table2 set Col2='MMMMM' 				--更新操作
WAITFOR  DELAY '00:00:20' 							--等待20秒
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M1的UPDATE语句执行时刻为：'+ @now_time
ROLLBACK TRANSACTION M1							--回滚结束事务M1

BEGIN TRANSACTION M2								--开始事务M2
PRINT '事务M2第一个SELECT语句的查询结果为：'
SELECT  * FROM Test_Table2 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M2第一个SELECT语句的执行时刻为：'+ @now_time
WAITFOR  DELAY '00:00:20' --等待20秒
PRINT '事务M2第二个SELECT语句的查询结果为：'
SELECT  * FROM Test_Table2 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务M2第二个SELECT语句的执行时刻为：'+ @now_time
COMMIT TRANSACTION M2								--提交结束事务M2

用户N的访问代码如下所示。

SET TRANSACTION ISOLATION LEVEL READ COMMITTED		--设置隔离级别
USE Library
Declare @now_time VARCHAR(8)						--定义变量
WAITFOR  DELAY '00:00:5'							--等待5秒
BEGIN TRANSACTION N									--开始事务N
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的开始执行时刻为：'+ @now_time
PRINT '事务N的SELECT语句的查询结果为：'
SELECT  * FROM Test_Table2 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的SELECT语句的执行时刻为：'+ @now_time
WAITFOR  DELAY '00:00:18' 							--等待18秒
UPDATE Test_Table2 set Col2='NNNNN' 				--更新操作
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的UPDATE语句的执行时刻为：'+ @now_time
COMMIT TRANSACTION N								--提交结束事务N

实际上，其代码与实例7基本相同，只是将隔离级别设置为READ COMMITTED，同时将访问的表设置为Test_Table2。
在SQL Server 2005的Management Studio中，开启两个查询窗口，分别键入上面的代码，而后先后执行（间隔1s）用户M和用户N的代码，最终得到的查询结果分别如图9.22和图9.23所示。
在这里插入图片描述在这里插入图片描述
图9.22 用户M的执行结果 图9.23 用户N的执行结果
从示例中可以看出，在READ COMMITTED隔离级别下，用户N避免了“脏读”并发问题，但用户M同样存在“非重复读”并发问题。结合执行结果中显示的执行时刻，用户M和用户N对Test_Table2表中数据库的访问过程可表示为图9.24所示。
在这里插入图片描述
图9.24 实例的执行过程
简单来讲，在READ COMMITTED隔离级别下，当一个事务（如事务M1）在对表中数据进行修改（UPDATE）操作时，其他事务（如事务N）就不能对该表进行访问，这就避免了“脏读”和“丢失更新”的并发访问问题。但当一个事务（如事务M2）对表中的数据进行查询（SELECT）时，其他事务（如事务N）还可以对该表数据进行修改（UPDATE），这样就会造成“非重复读”的并发访问问题。

  1. 设置和使用REPEATABLE READ隔离级别
    在REPEATABLE READ隔离级别下，不能读取已由其他事务修改但尚未提交的行，这样就避免了“丢失更新”和“脏读”；并且指定，其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据，即对事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行，就避免了“非重复读”的问题。
    然而，在REPEATABLE READ隔离级别下，在事务读取数据时，其他事务可以插入与当前事务所发出语句的搜索条件相匹配的新行，如果当前事务随后重试执行该语句，它会检索新行，从而产生“幻像读”。下面通过实例说明。
    【上机实战】
    假设有用户M和用户N使用REPEATABLE READ隔离级别，通过事务访问数据库中Test_Table3表中的数据。
    用户M启动事务M，通过SELECT语句查询Test_Table3表中的数据，等待20秒（这期间用户N访问了表Test_Table3），而后再通过SELECT语句查询Test_Table3表中的数据，并提交事务，其实现的代码如下所示。

    SET TRANSACTION ISOLATION LEVEL REPEATABLE READ --设置隔离级别
    USE Library
    Declare @now_time VARCHAR(8) --定义变量
    BEGIN TRANSACTION M --开始事务M
    PRINT ‘事务M第一个SELECT语句的查询结果为：’
    SELECT * FROM Test_Table3
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT ‘事务M第一个SELECT语句的执行时刻为：’+ @now_time
    WAITFOR DELAY ‘00:00:20’ --等待20秒
    PRINT ‘事务M第二个SELECT语句的查询结果为：’
    SELECT * FROM Test_Table3
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT ‘事务M第二个SELECT语句的执行时刻为：’+ @now_time
    COMMIT TRANSACTION M --提交结束事务M

而用户N，在用户M访问开启事务M后不久（小于20s），开启事务N，通过INSERT语句向Test_Table3表中添加数据，而后提交事务，其实现的代码如下所示。

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ			--设置隔离级别
USE Library
Declare @now_time VARCHAR(8)							--定义变量
WAITFOR  DELAY '00:00:5'								--等待5秒
BEGIN TRANSACTION N									--开始事务N
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的开始执行时刻为：'+ @now_time
INSERT INTO Test_Table3 VALUES (4,'ddddd') 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的INSERT语句的执行时刻为：'+ @now_time
COMMIT TRANSACTION N									--提交结束事务N

在SQL Server 2005的Management Studio中，开启两个查询窗口，分别键入上面的代码，而后先后执行（几乎同时）用户M和用户N的代码，最终得到的查询结果分别如图9.25和图9.26所示。
在这里插入图片描述在这里插入图片描述
图9.25 用户M的执行结果 图9.26 用户N的执行结果
可见，在用户M进行了第一个SELECT操作之后，在第二个SELECT操作之前，用户N向Test_Table3表中插入了记录，所以造成了用户M两次执行同样的查询操作得到了不同的结果，即造成了幻像读。

  1. 设置和使用SERIALIZABLE隔离级别
    SERIALIZABLE隔离级别将在事务中其用到的数据集上放置一个范围锁，在事务完成之前将一直保持范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是限制最多的隔离级别，因为它锁定了键的整个范围。这是4个隔离级别中限制最大的级别。
    【上机实战】
    假设有用户M和用户N使用SERIALIZABLE隔离级别，通过事务访问数据库中Test_Table4表中的数据，其操作过程与实例9完全相同。用户M的操作代码如下所示。

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE–设置隔离级别
    USE Library
    Declare @now_time VARCHAR(8) --定义变量
    BEGIN TRANSACTION M --开始事务M
    PRINT ‘事务M第一个SELECT语句的查询结果为：’
    SELECT * FROM Test_Table4
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT ‘事务M第一个SELECT语句的执行时刻为：’+ @now_time
    WAITFOR DELAY ‘00:00:20’ --等待20秒
    PRINT ‘事务M第二个SELECT语句的查询结果为：’
    SELECT * FROM Test_Table4
    SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
    PRINT ‘事务M第二个SELECT语句的执行时刻为：’+ @now_time
    COMMIT TRANSACTION M --提交结束事务M

用户N的操作代码如下所示。

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE--设置隔离级别
USE Library
Declare @now_time VARCHAR(8)						--定义变量
WAITFOR  DELAY '00:00:5'							--等待5秒
BEGIN TRANSACTION N								--开始事务N
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的开始执行时刻为：'+ @now_time
INSERT INTO Test_Table4 VALUES (4,'ddddd') 
SELECT @now_time = CONVERT(VARCHAR, GETDATE (),8)
PRINT '事务N的INSERT语句的执行时刻为：'+ @now_time
COMMIT TRANSACTION N								--提交结束事务N

在SQL Server 2005的Management Studio中，开启两个查询窗口，分别键入上面的代码，而后先后执行（几乎同时）用户M和用户N的代码，最终得到的查询结果分别如图9.27和图9.28所示。
在这里插入图片描述在这里插入图片描述
图9.27 用户M的执行结果 图9.28 用户N的执行结果
可见，当用户M的事务结束后，用户N的INSERT语句才得以执行，这样，用户M就不会出现“幻像读”了。
在实际应用中，一般都采用系统默认的事务隔离级别READ COMMITTED，即当一个事务正在修改数据时，在事务结束前，其他事务不能再访问该数据。除非有其他特别的需求，一般不要使用REPEATABLE READ和SERIALIZABLE事务隔离级别。

HeartBest丶
关注
  • 0
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

HeartBest丶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值