MySQL7 事务（一）

1. 概念

事务是什么

• 事务由一组DML语句组成，这些语句在逻辑上存在相关性

事务的四个属性

• 原子性（Atomicity）：事务中的所有操作被视为一个不可分割的原子单元，要么全部执行成功，要么全部失败回滚。如果事务中的任何一个操作失败，整个事务将被撤销，数据库将恢复到事务开始前的状态，就好像事务从未发生过一样。
• 一致性（Consistency）：事务必须使数据库从一个一致状态转换到另一个一致状态。在事务开始和结束时，数据库的完整性约束必须保持一致，即数据必须满足所有既定的规则和约束条件。
• 隔离性（Isolation）：多个事务并发执行时，每个事务都感觉不到其他事务的存在，它们之间相互隔离，互不干扰。一个事务的执行不能被其他事务干扰，直到该事务提交或回滚。不同的事务隔离级别可以控制事务之间的可见性和相互影响程度，以满足不同的业务需求和并发控制要求。
• 持久性（Durability）：一旦事务提交成功，其所做的修改将永久保存到数据库中，即使系统出现故障或重启，数据也不会丢失。数据库管理系统通过使用日志文件、数据备份等机制来保证事务的持久性，确保数据的可靠性和稳定性。

2. 事务操作

开始事务 - begin

• 使用START TRANSACTION 或 BEGIN 语句来标记事务的开始

提交事务 - commit

• 当事务中的所有操作都成功完成，并且满足数据的一致性要求时，可以使用 COMMIT 语句来提交事务，使事务的修改永久生效。
• 如果在commit之前，客户端掉了，会自动回滚。

示例代码

START TRANSACTION;
UPDATE student SET score = score - 5 WHERE id = 1122;
UPDATE student SET score =100 WHERE id = 3344;
COMMIT;
# 提交了一个更改两个学生分数的事务

回滚事务 rollback

• 如果在事务执行过程中发生错误，或者某些操作违反了数据的一致性约束，可以使用 ROLLBACK 语句来撤销事务中已执行的所有操作，将数据库恢复到事务开始前的状态。

示例代码

START TRANSACTION;
-- 录入学生的考试成绩
UPDATE student_scores SET score = 85 WHERE student_id = 2001;
-- 如果没有这个学生，则上面成绩属于是录入失败的
IF (SELECT score FROM scores WHERE student_id = 2001) = 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

查看数据库自动提交事务设置情况

• SHOW VARIABLES LIKE ‘autocommit’;

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.07 sec)

• 当你在 MySQL 客户端执行这条命令时，它会返回一个结果集，显示 autocommit 变量的值。
• 如果 autocommit 的值为 ON，则表示 MySQL 会自动提交每一条单独的 SQL 语句作为一个独立的事务
• 可以通过 set autocommit = 0 设置值为OFF
• 如果值为 OFF，则需要显式地使用 COMMIT 或 ROLLBACK 来提交或回滚事务（执行的增删改查操作在 commit 之前都是无效的）。

设置标记点 - savepoint

作用

• SAVEPOINT 是一种用于事务处理的机制，它允许在一个事务中设置标记点，以便在需要时可以回滚到该标记点，而不是回滚整个事务。
• 当一个事务包含多个操作步骤时，使用 SAVEPOINT 可以将事务划分为多个阶段。如果在某个阶段出现错误，但不希望回滚整个事务，而是只想回滚到之前设置的某个保存点，就可以使用 ROLLBACK TO SAVEPOINT 语句来实现部分回滚，从而保留事务中在保存点之前已经成功执行的操作，提高事务处理的灵活性和效率。

示例代码

START TRANSACTION;

-- 设置第一个保存点
SAVEPOINT sp1;
INSERT INTO table1 (col1, col2) VALUES ('value1', 'value2');

-- 设置第二个保存点
SAVEPOINT sp2;
INSERT INTO table2 (col3, col4) VALUES ('value3', 'value4');

-- 假设这里出现错误，需要回滚到第二个保存点 sp2
ROLLBACK TO SAVEPOINT sp2;

-- 继续执行其他操作
INSERT INTO table3 (col5, col6) VALUES ('value5', 'value6');

-- 提交事务
COMMIT;

注意

• 保存点的作用范围：保存点仅在当前事务内有效，一旦事务提交或回滚，所有的保存点都会被释放，不再可用。
• 保存点的命名唯一性：在一个事务中，保存点的名称必须是唯一的。如果尝试使用相同的名称创建多个保存点，将会导致错误。
• 与事务隔离级别的关系：SAVEPOINT 的使用与事务的隔离级别相互独立，但在不同的隔离级别下，保存点的行为和效果可能会受到一定的影响。

3. 隔离级别

查看会话隔离级别

• SHOW SESSION VARIABLES LIKE ‘transaction_isolation’;

mysql> SHOW SESSION VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

查看全局隔离级别

• SHOW GLOBAL VARIABLES LIKE ‘transaction_isolation’;

mysql> SHOW GLOBAL VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.00 sec)

设置隔离级别

- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
- 全局级别的设置会影响到所有新建立的会话，但对于已经存在的会话不会立即生效
- 只有当这些会话重新建立连接时才会应用新的隔离级别。

四种隔离级别

set global transaction isolation level read uncommitted

读未提交 - Read Uncommitted

• 现象：一个终端写，另外一个终端也会读到
• 一个事务在执行中，读到另一个执行中事务的更新但未 commit 的数据，这种情况属于脏读

读已提交 - Read Committed

set global transaction isolation level read committed

• 现象：一个终端写，不提交另外一个终端读不到
• 一个事务在执行中，提交前和提交后，使得另一个事务读到不同的结果
• 尽管解决了脏读的问题，但还是会出现其他问题（针对一个事务在提交前提交后，另一个事务读取结果的问题）

• 不可重复读：当对一个事务一条数据进行修改时，另一个事务可能第一次读和第二次读的结果不一样
• 幻读：同样的条件，一个事物对数据进行新增删除时，另一个事物第一次和第二次读的记录数不一样

可重复读 - Repeatable Read

set global transaction isolation level repeatable read

• 现象：一个终端写，即使提交了另一个终端也读不到
• 两个事务并发运行，互不影响
• 使用场景：适用于对数据一致性和稳定性要求较高的场景，如银行系统中的账户查询、财务系统中的报表生成等，这些场景下要求在一个事务内多次读取的数据必须保持一致，以确保数据的准确性和可靠性。

串行化 - Serializable

set global transaction isolation level serializable

• 现象：一个终端在执行事务，另一个终端的插入删除动作会被阻塞
• 这是最高的事务隔离级别，它通过强制事务串行执行，避免了脏读、不可重复读和幻读等所有并发问题。在串行化隔离级别下，事务之间完全隔离，就像依次执行每个事务一样，一个事务必须等待前一个事务完成后才能开始执行。
• 使用场景：对数据一致性要求极高，且并发操作较少的场景，如航空订票系统中的座位预订、证券交易系统中的交易处理等，这些场景下数据的一致性和准确性至关重要，即使牺牲一定的性能也要确保数据的绝对正确。