SQL Server数据库第十二课：事务处理

「爱情、让人受尽委屈。」 2022-09-07 09:55 180阅读 0赞

**知识点：**事务概念、事务处理

# 1、事务简介 #

## 1.1  什么是事务 ##

**事务（Transaction）**是一个由多条SQL语句组成的工作逻辑单元，这些语句要么全部执行成功，要么全部不执行，只要有一条SQL语句执行失败，已执行的SQL语句会全部回滚到执行之前的状态，这样就保证了数据库数据的一致性。

**举例：**比如银行的转账业务，该业务至少可以分为 A 账户的转出 和 B 账户的转入。也就是从 A 账户的余额中减掉一定的数额，然后再将 B 账户的余额增加一定的数额。整个过程等于是修改了两条记录，这两个操作就可以认为组成了一个事务。它们应该是一个整体，一个单个的逻辑工作单元，要么都执行成功，要么都不执行。

## 1.2  为什么要使用事务 ##

事务的作用：用于保持数据库数据的一致性。

## 1.3  事务的特点 ##

当使用事务修改多个数据表时，如果在处理的过程中出现了某种错误，例如系统死机或突然断电等情况，则返回结果是全部数据均没有被保存。因为事务处理的结果只有两种：一种是在事务处理的过程中，如果发生了某种错误则整个事务全部回滚，使所有对数据的修改全部撤销，事务对数据库的操作是单步执行的，当遇到错误时可以随时地回滚；另一种是如果没有发生任何错误且每一步的执行都成功，则整个事务全部被提交。从而可以看出，有效地使用事务不但可以提高数据的安全性，而且还可以增强数据的处理效率。

事务包含 4 种重要的属性，被统称为 ACID（原子性、一致性、隔离性和持久性）特性，对一组 SQL 语句操作构成的事务,数据库操作系统必须确保这些操作的原子性、一致性、隔离性、持久性。

（1）原子性(Atomicity)

事务的原子性是指事务中包含的所有操作要么全做，要么不做，也就是说所有的活动在数据库中要么全部反映，要么全部不反映,以保证数据库的一致性。

（2）一致性(Consistency)

事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用事务的修改，以保持所有数据的完整性。如果事务成功，则所有数据将变为一个新的状态； 如果事务失败，则所有数据将处于开始之前的状态。拿转账来说，假设用户A和用户B两者的钱加起来一共是5000，那么不管A和B之间如何转账，转几次账，事务结束后两个用户的钱相加起来应该还得是5000，这就是事务的一致性。

（3）隔离性(Isolation)

隔离性是指由事务所做的修改必须与其他事务所做的修改隔离。事务查看数据时，数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事物修改它之后的状态，事务不会查看中间状态的数据。

（4）持久性(durability)

事务的持久性是指在事务处理结束后，它对数据的修改应该是永久的。即便是系统在遇到故障的情况下也不会丢失，这是数据的重要性决定的。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70][]

**注意：保证事务的 ACID 特性，是事务处理的重要任务**。事务的 ACID 特性可能遭到破坏的因素有：

*  多个事务并行运行时，不同事务的操作有交叉情况。（利用并发控制解决）
 *  事务在运行过程中被强迫停止。（利用数据恢复解决）

## 1.4  事务的类型 ##

根据系统的设置，可以把事务分成两种类型。一种是系统提供的事务，另一种是用户定义的事务。系统提供的事务是指在执行某些语句时，一条语句就是一个事务。这时要知道，一条语句的对象既可能是表中的一行数据，也可能是表中的多行数据，甚至是表中的全部数据。 因此，只有一条语句构成的事务也可能包含了对多行数据的处理。

事务运行的3种模式如下。

**（1）自动提交事务：**每条单独的语句都是一个事务。每个语句后都隐含一个 COMMIT 。

**（2）显式事务：** 以 BEGIN TRANSACTION 显示开始，以 COMMIT 或 ROLLBACK 显示结束。

**（3）隐式事务：**在前一个事务完成时，新事务隐式启动，但每个事务仍以 COMMIT 或 ROLLBACK 显示结束。

# 2、事务处理 #

## 2.1  事务的起点 ##

事务以 BEGIN TRANSACTION 语句开始。语法结构如下：

BEGIN { TRAN | TRANSACTION }
        [ { transaction_name | @tran_name_variable }
          [ WITH MARK [ 'description' ] ]
        ]
    [ ; ]

**参数说明：**

**transaction\_name ：**分配给事务的名称。 transaction\_name 必须符合标识符规则，但标识符所包含的字符数不能大于32。仅在最外面的 BEGIN…COMMIT 或 BEGIN…ROLLBACK 嵌套语句对中使用事务名称。

**@tran\_name\_variable ：**用户定义的，含有有效事务名称的变量名称。必须使用 char 、varchar 、nchar 或 nvarchar 数据类型声明变量。如果传递给改变了的字符多于32个，则仅使用前32个字符，其余字符将被截断。

**WITH MARK \[ 'description' \] ：**指定在日志中标记事务。 description 是描述该标记的字符串。如果 description 是 Unicode 字符串，那么在将长于255个字符的值存储到 msdb.dbo.logmarkhistory 表之前，先将其截断为255个字符。如果 description 为非 Unicode 字符串，则长于510个字符的值将被截断为510个字符。如果使用了 WITH MARK ，则必须指定事务名。 WITH MARK 允许将事务日志还原到命名标记。

## 2.2  事务的终点 ##

事务以 COMMIT  TRANSACTION 作为隐性事务或显式事务成功结束的标志。语法结构如下：

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

## 2.3  数据回滚 ##

使用 ROLLBACK  TRANSACTION 语句将显式事务或隐性事务回滚到事务的起点或事务内的某个保存点。语法结构如下：

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

**参数说明：**

**transaction\_name ：**是为 BEGIN  TRANSACTION 上事务分配的名称。** **transaction\_name  必须符合标识符规则，但只使用事务名称的前32个字符。嵌套事务时，** **transaction\_name 必须是最外面的 BEGIN  TRANSACTION 语句中的名称。

**@tran\_name\_variable ：**用户定义的，含有有效事务名称的变量名称。必须使用 char 、varchar 、nchar 或 nvarchar 数据类型声明变量。

**savepoint\_name ：**事务保存点名称，必须符合标识符规则。当条件回滚只影响事务的一部分时，可使用 savepoint\_name 。

**@savepoint\_variable ：**用户定义的，包含有效保存点名称的变量名称。必须使用 char 、varchar 、nchar 或 nvarchar 数据类型声明变量。

## 2.4  事务保存点 ##

使用 SAVE TRANSACTION 语句在事务内设置保存点。语法结构如下：

SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable }
    [ ; ]

# 3、事务的应用 #

### 事务实例练习1：利用事务模拟银行转账例子（ A 给 B 转账500元） ###

**目的：**通过该实例，理解数据库的事务操作，理解提交、回滚事务。

第一步，准备数据信息。

--创建数据库
    create database Bank
    --使用数据库
    use Bank
    --创建数据库表
    create table Customer(
    	Cid  int  primary key identity(1,1),	--编号,主键，标识列
    	Cname varchar(20)  unique not null ,	--姓名，唯一键
    	Cmoney decimal(18,2) not null			--金额，decimal类型小数2位总长18位
    )
    --插入数据
    insert into Customer(Cname,Cmoney)
    values('A',1500.00),('B',0.00)
    --查询表中数据
    select * from Customer

第二步，使用事务处理，模拟转账操作。

--使用事务处理，模拟账户转账
    begin transaction	--事务起点
    	select * from Customer		--先查询一下账户初始金额
    	update Customer set Cmoney=Cmoney-500 where Cname='A'  --从A账户减去500
    	update Customer set Cmoney=Cmoney+500 where Cname='B'	--给B账户添加500
    commit transaction	--事务终点，提交

第三步，查看结果

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 1][]

### 扩展练习1-1：验证 ROLLBACK 和 COMMIT 的区别 ###

执行如下代码，最终账户里的余额会是多少？

### 扩展练习1-2：事务原子性验证 ###

执行如下代码，最终账户A和B，里的余额将会是多少？

--事务原子性验证
    begin transaction	--事务起点
    	select * from Customer		--先查询一下账户初始金额
    	update Customer set Cmoney=Cmoney-500 where Cname='A'   --从A账户减去500
    	insert into Customer(Cname,Cmoney)values ('B',200.00)	--此语句违反唯一约束，将会报错
    	update Customer set Cmoney=Cmoney+500 where Cname='B'   --给B账户增加500
    commit transaction	--事务终点，提交

**思考：**事务应该有原子性，要求事务要么全部完成，要么全部不完成，不能停滞在某个中间状态。然而，在上面的例子中事务却没有完全“回滚”，导致了异常数据的发生，为什么？

**原因：**默认SQL Server并不会回滚事务，即使事务中的某个语句报错，事务还是会继续执行下去，除非非常严重的错误（serverity level is greater or equals 16)。这是由数据库选项XACT\_ABORT决定的，默认XACT\_ABORT为OFF。

**解决办法：**设置XACT\_ABORT为on，这样事务中任何一个语句报错都会回滚整个事务。

SET XACT_ABORT ON    --设置之后，事务中只要语句报错，就会回滚

### 事务实例练习2：事务保存点的使用 ###

**目的：**通过该实例，理解数据库的事务保存点。

依次执行以下代码，注意观察结果。

--事务保存点的使用
    	--1.查询一下账户初始状态
    	select * from Customer
    	--2.创建事务保存点
    	begin transaction	--事务起点
    	save transaction savepoint_a
    	--3.修改一条数据信息
    	update Customer set Cmoney=Cmoney+500 where Cname='B'	--给B账户添加500
    	--4.查询账户当前状态
    	select * from Customer
    	--5.回滚到事务保存点 savepoint_a
    	rollback transaction savepoint_a
    	commit transaction		--提交事务
    	--6.查询账户当前状态
    	select * from Customer

### 事务实例练习2-1：事务保存点的使用 ###

--回滚的使用
    begin transaction	--事务起点
    	select * from Customer		--先查询一下账户初始金额
    	save tran abc				--设置一个保存点
    	update Customer set Cmoney=Cmoney-500 where Cname='A'   --从A账户减去500
    	insert into Customer(Cname,Cmoney)values('B',00.00) 	--插入一行数据，违反唯一约束
    if @@error<>0		--如果遇到错误
    	begin
    		print '遇到错误，准备回滚'
    		waitfor delay '0:00:02'
    		rollback tran abc		--回滚到保存点
    	end
    else 
    	print '操作完成'
    commit transaction	--事务终点，提交

=====这里是结束分割线======

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70]: /images/20220829/fdc22702891e4692a9a452cdc46640bc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2RucnVhbmppYW4_size_16_color_FFFFFF_t_70 1]: /images/20220829/d516b5da8521417c9fdf3261438f0eb9.png