事务

在 MySQL 中，事务（Transaction）是一组逻辑上相关的 SQL 操作，这些操作要么全部成功执行，要么全部不执行。事务提供了原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），通常被称为 ACID 属性。

基本概念

1. 原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。
2. 一致性（Consistency）：事务必须使数据库从一个一致状态变换到另一个一致状态。
3. 隔离性（Isolation）：多个事务并发执行时，一个事务的执行不能被其他事务干扰。
4. 持久性（Durability）：一旦事务提交，其结果将永久保存在数据库中，即使系统发生故障也不会丢失。

银行存取款：假如A想要把自己账户上的10万元转到B的账户上，这时就需要先从A账户中扣除10万元，然后再给B账户加上10万元。如果从A账户扣除10万元时发生了错误，会出现什么情况呢？结果可能会导致以下这两种情况。

1. A账户的10万元没有扣除成功，B账户加上了10万元。
2. A账户的10万元扣除成功，B账户没有加上。

而如果把这两个操作作为一个整体来处理，要么全部成功要么全部失败，像这样的功能我们称之为“事务（transaction）”，然后将事务开始之后的处理结果反映到数据库的操作称为“提交（commit）”，不反映到数据库中而是恢复成原来状态的操作成为“回滚（rollback）”。

使用事务

• START TRANSACTION 或 BEGIN：开始一个新的事务。
• COMMIT：提交当前事务，使其对数据库的更改永久生效。
• ROLLBACK：回滚当前事务，撤销所有未提交的操作。
• SAVEPOINT：在事务中设置保存点，可以回滚到某个保存点而不是整个事务。
• RELEASE SAVEPOINT：释放保存点。

示例

假设我们有一个 accounts 表，并希望创建一个存储过程来模拟两个账户之间的转账操作。我们将使用事务来确保转账操作的原子性和一致性。

创建表：

-- 创建 accounts 表
CREATE TABLE accounts (
    account_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '账户ID',
    owner_name VARCHAR(50) COMMENT '账户所有人',
    balance DECIMAL(10, 2) COMMENT '余额'
);

-- 插入示例数据
INSERT INTO accounts (owner_name, balance) VALUES
('Alice', 1000.00),
('Bob', 500.00);

创建存储过程：

我们将创建一个存储过程 transfer_money，该过程用于从一个账户向另一个账户转账，并使用事务来确保操作的原子性和一致性。

-- 定义了一个名为transfer_money的存储过程
-- 该存储过程接受三个输入参数：
	-- from_account：转出账户的ID，类型为INT。
	-- to_account：转入账户的ID，类型为INT。
	-- amount：转账金额，类型为DECIMAL(10, 2)
CREATE PROCEDURE transfer_money(IN from_account INT, IN to_account INT, IN amount DECIMAL(10, 2))
BEGIN
	-- 声明一个变量from_balance，用于存储转出账户的余额
    DECLARE from_balance DECIMAL(10, 2);
    
    -- 开始一个新的事务，确保转账操作的原子性
    START TRANSACTION;
    
    -- 获取转出账户的余额，从 accounts 表中选择 from_account 账户的余额，并将其存储在 from_balance 变量中
    -- FOR UPDATE 子句用于锁定该行，防止其他事务同时修改该行数据，确保数据一致性
    SELECT balance INTO from_balance FROM accounts WHERE account_id = from_account FOR UPDATE;
    
    -- 检查余额是否足够
    IF from_balance < amount THEN -- 检查转出账户的余额是否小于转账金额
    	-- 如果余额不足，抛出一个自定义的SQL错误，错误代码为45000，错误信息为'Insufficient balance'
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
        -- 回滚事务，撤销所有未提交的更改
        ROLLBACK;
    ELSE
        -- 扣除转出账户的金额
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;
        
        -- 增加转入账户的金额
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;
        
        -- 提交事务，使所有更改永久生效
        COMMIT;
    END IF;
END

调用

CALL transfer_money(1, 2, 200.00);

结果

account_id	owner_name	balance
1	Alice	800.00
2	Bob	700.00

总结

• 事务：一组逻辑上相关的 SQL 操作，确保数据的一致性和完整性。
• 事务控制语句：包括 START TRANSACTION、COMMIT、ROLLBACK、SAVEPOINT 和 RELEASE SAVEPOINT。
• 锁机制：使用 FOR UPDATE 锁定行，防止其他事务修改。
• 错误处理：使用 SIGNAL 抛出自定义错误，并使用 ROLLBACK 回滚事务。