SQL Server教程 - T-SQL-事务（TRANSACTION）

更新记录
转载请注明出处：
2022年8月3日 发布。
2022年7月2日 从笔记迁移到博客。

事务说明（TRANSACTION）

SQL Server 中提供了多种数据完整性的保证机制，如约束、触发器、事务和锁管理等。事务管理主要是为了保证对一批相关数据库中数据的操作能够全部无遗漏地完成，从而保证数据的完整性。

当事务执行时，事务中进行的所有操作都会被写入事务日志中写入日志中的内容通常分为两种：
一是事务进行数据操作的记录，如对数据进行插入和修改。
另一种则是对任务的操作记录，如对表中的某一列创建索引。
当取消事务时，系统会根据日志中的记录进行反操作，保证系统的一致性。

事务的类型

事务是SQL Server中的基本工作单元，它是用户定义的一个数据库操作序列，这些操作要么做要么全不做，是一个不可分割的工作单位。SQL Server中事务主要可以分为：自动提交事务、隐式事务、显式事务和分布式事务4种类型。

类型	含义
自动提交事务	每条单独语句都是一个事务
隐式事务	前一个事务完成时新事务隐式启动，每个事务仍以COMMIT或ROLLBACK 语句显式结束
显式事务	每个事务均以BEGIN TRNSACTION 语句显式开始，以COMMIT或ROLLBACK显式结束
分布式事务	跨越多个服务器的事务

事务的含义

事务要有非常明确的开始点和结束点，SQL Server中的每一条数据操作语句，例如 SELECT、INSERT、UPDATE和DELETE都是隐式事务的一部分。即使只有一条语句，系统也会把这条语句当作一个事务，要么执行所有语句，要么什么都不执行。

事务开始之后，事务中所有的操作都会写到事务日志中，写到日志中的事务，一般有两种：一种是针对数据的操作，例如插入、修改和删除，这些操作的对象是大量的数据；另一种是针对任务的操作，例如创建索引。当取消这些事务操作时，系统自动执行这种操作的反操作，以保证系统的一致性。系统自动生成一个检查点机制，这个检查点周期地检查事务日志。如果在事务日志中，事务全部完成，那么检查点事务日志中的事务提交到数据库中，并且在事务日志中做一个检查点提交标识；如果在事务日志中，事务没有完成，那么检查点不会将事务日志中的事务提交到数据库中，并且在事务日志中做一个检查点未提交的标识。事务的恢复及检查点保证了系统的完整和可恢复。

事务的属性

事务是作为单个逻辑工作单元执行的一系列操作。一个逻辑工作单元必须有4个属性，称为原子性（Atomic)、一致性（Consistent)、隔离性（Isolated)和持久性（Durable),简称 ACID属性，只有这样才能构成一个事务。
原子性：事务必须是原子工作单元。对于其数据修改，要么全部执行，要么全部都不执行。
一致性：事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构都必须是正确的。
隔离性：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可序列化（或可串行化），因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行时的状态相同。
持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。

建立事务应遵循的原则

事务中不能包含以下语句：ALTER DATABASE、DROP DATABASE ALTER FULLTEXT CATALOG、DROP FULLTEXT CATALOG、ALTER FULLTEXT INDEX、DROP FULLTEXT INDEX、BACKUP、RECONFIGURE、CREATE DATABASE、RESTORE、CREATE FULLTEXT CATALOG、UPDATE STATISTICS、CREATE FULLTEXT INDEX。

当调用远程服务器上的存储过程时，不能使用 ROLLBACK TRANSACTION语句，也不可执行回滚操作。

SQL Server不允许在事务内使用存储过程建立临时数据表。

事务适用场景

多个操作当做一个单元操作

保持多个操作的同步

事务类型

事务类型	说明
自动事务	每条单独的语句都是一个事务
隐式事务	前一个事务完成时新事务隐式启动，每个事务COMMIT或ROLLBACK结束 在打开隐式事务开关时，执行下一条语句时会换行启动一个新的事务
用户显式事务	以BEGIN TRANSACTION开始，以COMMIT或ROLLBACK结束
分布式事务	跨越多个服务器的事务

以下语句操作会触发隐式事务

事务的ACID属性

属性	说明
A：原子性（Atomic）	当做一个单元操作；要么成功；要么回滚
C：一致性（Consistency）	多表间保持一致。符合数据库的完整性
I：独立性（Isolated）	每个事物具有明确界限。多个事务无不干扰
D：持续性（Durability）	事务提交成功后，永久保存

事务语法实现

事务中不可以包含以下语句：
CREATE DATABASE、ALTER DATABASE、DROP DATABASE
CREATE FULLTEXT CATALOG、ALTER FULLTEXT CATALOG、DROP FULLTEXT CATALOG
CREATE FULLTEXT INDEX
DROP FULLTEXT INDEX
ALTER FULLTEXT INDEX
BACKUP、RECONFIGURE、RESTORE
UPDATE STATISTICS
使用存储过程创建临时表

开始事务：

BEGIN TRANSACTION 事务名 [WITH MARK 'description'];
-- 或者
BEGIN TRANSACTION

回滚事务：

ROLLBACK TRANSACTION [事务名];
-- 或者
ROLLBACK;

提交事务：

COMMIT TRANSACTION [事务名];
-- 或者
COMMIT;

保存事务：

SAVE TRANSACTION 事务名;

开启远程事务错误回滚：

-- 开启事务错误回滚
SET XACT_ABORT ON

开启分布式事务：

-- 开启分布式事务
BEGIN DISTRIBUTED TRANSACTION [事务名称];
COMMIT TRANSACTION [事务名称];
ROLLBACK TRANSACTION [事务名称];

实例：

-- 开启事务
BEGIN TRANSACTION
BEGIN
	--插入数据
	INSERT INTO [PandaTable1] VALUES
	('Panda',19),
	('Cat',20),
	('Pig',21);
	-- 定义变量
	DECLARE @RowCout INT;
	-- 获得数据条数
	SELECT @RowCout = COUNT(*) FROM [PandaTable1];
	-- 检测数据条数
	IF @RowCout >= 3
		-- 提交
		COMMIT;
	ELSE
		-- 回滚
		ROLLBACK;
END;

事务隔离级别

隔离级别说明

事务具有隔离性，不同事务中所使用的时间必须要和其他事务进行隔离，在同一时间可以有很多个事务正在处理数据，但是每个数据在同一时刻只能有一个事务进行操作。如果将数据锁定，那么使用数据的事务就必须要排队等待，以防止多个事务互相影响。但是如果有几个事务因为锁定了自己的数据，同时又在等待其他事务释放数据，则会造成死锁。
为了提高数据的并发使用效率，可以为事务在读取数据时设置隔离状态，SQL Server 2019中事务的隔离状态由低到高可以分为以下5个级别：

READ UNCOMMITTED级别：该级别不隔离数据，即使事务正在使用数据，其他事务也能同时修改或删除该数据。在READ UNCOMMITTED级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。
READ COMMITTED级别：指定语句不能读取已由其他事务修改但尚未提交的数据。这样可以避免脏读。其他事务可以在当前事务的各个语句之间更改数据，从而产生不可重复读取和幻象数据。在READ COMMITTED级别事务中读取的数据随时都可能被修改，但已经修改过的数据事务会一直被锁定，直到事务结束为止。该选项是SQL Server的默认设置。
REPEATABLE READ级别：指定语句不能读取已由其他事务修改但尚未提交的行，并且指定其他任何事务都不能在当前事务完成之前修改由当前事务读取的数据。该事务中的每个语句所读取的全部数据都设置了共享锁，并且该共享锁一直保持到事务完成为止。这样可以防止其他事务修改当前事务读取的任何行。
SNAPSHOT级别：指定事务中任何语句读取的数据都将是在事务开始时便存在的一致的版本。事务只能识别在其开始之前提交的数据修改。在当前事务中执行的语句将看不到在当前事务开始以后由其他事务所做的数据修改。其效果就好像事务中的语句获得了已提交数据的快照，因为该数据在事务开始时就存在。除非正在恢复数据库，否则SNAPSHOT 事务不会在读取数据时请求锁。读取数据的SNAPSHOT事务不会阻止其他事务写入数据，写入数据的事务也不会阻止 SNAPSHOT事务读取数据。
SERIALIZABLE级别：将事务所要用到的序列化数据全部锁定，不允许其他事务添加、修改和删除数据，使用该等级的事务并发性最低，要读取同一数据的事务必须排队等待。

更改事务的隔离级别

使用SET TRANSACTION语句

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

事务在并发下存在的问题

事务限制

只可以使用DML语句（INSERT、UPDATE、DELETE）
不可以创建表、删除表操作

实例

使用@@EEROR检测事务是否成功

BEGIN TRANSACTION Panda_transaction
    -- sql
COMMIT TRANSACTION Panda_transaction
-- 检测是否存在错误
IF @@ERROR = 0
    PRINT 'SUCCESS';
ELSE
    PRINT 'Some Trouble';