MySQL的事务相关的语句的使用

MySQL的事务相关的语句的使用

事务是数据库管理系统执行过程中的一个程序单位，由一个或多个数据库操作组成。MySQL作为一款流行的关系型数据库管理系统，支持事务处理，允许用户定义一系列的操作，这些操作要么完全执行，要么完全不执行，保证了数据库的完整性和一致性。

事务的基本概念

事务通常用于处理需要多个步骤的复杂操作，而这些步骤必须完整执行，不能只执行部分。在MySQL中，事务的处理依赖于存储引擎，最常见的支持事务的存储引擎是InnoDB。

事务的四大特性（ACID）

• 原子性（Atomicity）：事务是数据库的基本操作单元，它的原子性保证了操作要么全完成要么全不完成。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态转换到另一个一致性状态。
• 隔离性（Isolation）：事务的执行不应受其他事务的干扰。
• 持久性（Durability）：一旦事务提交，则其所做的修改将永久保存在数据库中。

在MySQL中，你可以使用以下SQL语句来控制事务的开始、执行和结束。

事务基本语法

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}
1
2
3
4
5
6
7
8
9
10
11
12
13

START TRANSACTION 或 BEGIN

这两个命令用于启动一个新的数据库事务。BEGIN 是 START TRANSACTION 的简写形式，功能相同。

• WITH CONSISTENT SNAPSHOT: 在事务开始时创建一个一致性视图。这意味着事务可以看到一个一致的数据库快照，而不受并发事务中的更新操作影响。
• READ WRITE: 指定事务可以进行读取和写入操作。这是默认设置。
• READ ONLY: 指定事务只能进行读取操作，不允许写入。这可以优化事务的执行，因为数据库系统知道不需要处理可能的写冲突。

COMMIT

COMMIT 语句用于提交当前事务中的所有更改，使它们成为数据库中的永久更改。

• WORK: 这是一个可选关键字，用于明确指示提交的是工作事务。通常不是必需的，因为其默认即为操作事务。
• AND CHAIN: 自动开始一个新的事务，该事务继承当前事务的属性。适用于需要连续处理多个事务的场景。
• AND NO CHAIN: 结束当前事务后不自动开始新事务。
• RELEASE: 提交事务并断开当前的数据库连接。
• NO RELEASE: 提交事务但不断开数据库连接。

ROLLBACK

ROLLBACK 语句用于撤销当前事务中的所有更改，回到事务开始前的状态。

• 与 COMMIT 类似，ROLLBACK 也可以用 WORK, AND CHAIN, AND NO CHAIN, RELEASE, NO RELEASE 这些选项来控制事务的结束行为和连接的状态。

SET autocommit

SET autocommit 用于控制MySQL的自动提交行为。当 autocommit 设置为1时，每个SQL语句都会被视为一个独立的事务并自动提交。设置为0时，SQL语句不会自动提交，需要显式使用 COMMIT 来提交事务。

• SET autocommit = 0;: 关闭自动提交。
• SET autocommit = 1;: 开启自动提交。

常用的事务控制语句

开始一个事务

事务的开始通常使用START TRANSACTION或BEGIN声明。

START TRANSACTION;
-- 或者
BEGIN;
1
2
3

提交一个事务

提交事务意味着所有在事务块内的语句都被执行，并且修改永久保存到数据库中。

COMMIT;
1

回滚一个事务

如果在执行事务过程中发生错误或者用户主动决定取消事务的执行，可以使用ROLLBACK语句来撤销所有未提交的修改。

ROLLBACK;
1

设置保存点

在事务中，你可以通过设置保存点（SAVEPOINT）来进行局部的回滚。这是非常有用的功能，可以在事务中的任何点保存当前状态，之后你可以回滚到这个状态而不影响其他的操作。

SAVEPOINT savepoint_name;
ROLLBACK TO savepoint_name;
1
2

释放保存点

如果确定不再需要某个特定的保存点，可以使用RELEASE SAVEPOINT语句来释放它。

RELEASE SAVEPOINT savepoint_name;
1

设置事务的隔离级别

为了控制事务如何通过锁定机制与其他事务隔离，可以设置事务的隔离级别。MySQL支持以下四种隔离级别：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ（默认级别）
• SERIALIZABLE

设置隔离级别的语句如下：

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
1

示例：使用事务处理

考虑一个银行转账的场景，其中涉及到两个账户的余额更新。这里展示如何使用事务来确保操作的原子性。

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';

-- 如果以上操作都成功执行，提交事务
COMMIT;

-- 如果有任何错误发生，回滚事务
ROLLBACK;
1
2
3
4
5
6
7
8
9
10

参考链接

• MySQL事务处理：MySQL官方文档
• InnoDB存储引擎：InnoDB存储引擎介绍