MySQL存储过程之：`存储过程`的事务管理：`START TRANSACTION`、`COMMIT`和`ROLLBACK`的用法。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL存储过程的事务管理：START TRANSACTION、COMMIT和ROLLBACK的用法

大家好！今天我们来聊聊MySQL存储过程中的事务管理，重点讲解START TRANSACTION、COMMIT和ROLLBACK这三个关键语句的用法。事务是数据库管理系统中至关重要的概念，它保证了一系列操作要么全部成功，要么全部失败，从而维护数据的完整性和一致性。在存储过程中，合理运用事务可以极大地提升程序的健壮性和可靠性。

1. 什么是事务？

简单来说，事务就是一系列数据库操作的逻辑单元。这些操作要么全部执行成功，要么全部不执行，不能只执行一部分。想象一个银行转账的例子：

步骤1: 从账户A中扣除100元。
步骤2: 向账户B中增加100元。

如果步骤1成功执行，但步骤2因为某种原因失败了，那么账户A少了100元，而账户B没有收到，这就会导致数据不一致。事务的作用就是确保这两个步骤要么都成功，要么都失败，保证账户A和账户B的总金额不变。

事务有四个关键特性，通常被称为ACID特性：

原子性 (Atomicity): 事务中的所有操作要么全部成功，要么全部失败，不存在部分执行的情况。
一致性 (Consistency): 事务执行前后，数据库的状态必须保持一致。例如，转账前后，两个账户的总金额应该不变。
隔离性 (Isolation): 多个并发事务之间应该相互隔离，一个事务的执行不应该受到其他事务的干扰。
持久性 (Durability): 事务一旦提交，其结果应该永久保存在数据库中，即使系统发生故障也不会丢失。

2. START TRANSACTION, COMMIT, ROLLBACK 的基本用法

MySQL 使用 START TRANSACTION 语句来显式地启动一个事务。 COMMIT 语句用于提交事务，将所有更改永久保存到数据库中。 ROLLBACK 语句用于回滚事务，撤销所有未提交的更改，将数据库恢复到事务开始之前的状态。

下面是一个简单的例子：

DELIMITER //

CREATE PROCEDURE transfer_money(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    -- 声明变量
    DECLARE current_balance DECIMAL(10, 2);

    -- 开启事务
    START TRANSACTION;

    -- 检查转出账户余额是否足够
    SELECT balance INTO current_balance FROM accounts WHERE account_id = from_account FOR UPDATE; -- 使用 FOR UPDATE 锁定行

    IF current_balance >= amount THEN
        -- 从转出账户扣款
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;

        -- 向转入账户加款
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;

        -- 提交事务
        COMMIT;
        SELECT '转账成功' AS message;
    ELSE
        -- 回滚事务
        ROLLBACK;
        SELECT '余额不足，转账失败' AS message;
    END IF;
END //

DELIMITER ;

在这个例子中：

START TRANSACTION 启动了一个事务。
SELECT balance INTO current_balance FROM accounts WHERE account_id = from_account FOR UPDATE; 这行代码获取转出账户的余额，并且使用 FOR UPDATE 语句锁定该行，防止其他事务同时修改该账户的余额，保证了隔离性。
IF current_balance >= amount THEN 判断余额是否足够。
UPDATE 语句执行实际的转账操作。
COMMIT 提交事务，将转账操作永久保存到数据库中。
ROLLBACK 回滚事务，撤销转账操作，将数据库恢复到事务开始之前的状态。

3. 错误处理和异常处理

在存储过程中，良好的错误处理机制对于事务管理至关重要。我们需要捕获可能发生的错误，并在出现错误时回滚事务，防止数据不一致。MySQL 提供了 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 和 DECLARE EXIT HANDLER FOR SQLEXCEPTION 等语句来处理异常。

DELIMITER //

CREATE PROCEDURE transfer_money_with_error_handling(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10, 2)
)
BEGIN
    -- 声明变量
    DECLARE current_balance DECIMAL(10, 2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 发生错误时回滚事务
        ROLLBACK;
        SELECT '发生错误，转账失败' AS message;
    END;

    -- 开启事务
    START TRANSACTION;

    -- 检查转出账户余额是否足够
    SELECT balance INTO current_balance FROM accounts WHERE account_id = from_account FOR UPDATE;

    IF current_balance >= amount THEN
        -- 从转出账户扣款
        UPDATE accounts SET balance = balance - amount WHERE account_id = from_account;

        -- 模拟一个错误 (例如，尝试插入重复的记录)
        -- INSERT INTO some_table (unique_column) VALUES (1); -- 假设 some_table 的 unique_column 有唯一索引

        -- 向转入账户加款
        UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;

        -- 提交事务
        COMMIT;
        SELECT '转账成功' AS message;
    ELSE
        -- 回滚事务
        ROLLBACK;
        SELECT '余额不足，转账失败' AS message;
    END IF;
END //

DELIMITER ;

在这个例子中：

DECLARE EXIT HANDLER FOR SQLEXCEPTION 定义了一个异常处理器，当发生任何 SQL 异常时，都会执行该处理器中的代码。
在异常处理器中，我们首先使用 ROLLBACK 回滚事务，然后输出一个错误信息。
如果 INSERT INTO some_table (unique_column) VALUES (1); 这行代码被取消注释，并且some_table 表的 unique_column 列已经存在值为 1 的记录，那么就会触发一个 SQL 异常，导致事务回滚。

4. 嵌套事务 (SAVEPOINT)

MySQL 允许在事务内部设置保存点 (SAVEPOINT)，以便在事务执行过程中回滚到特定的状态。这对于处理复杂的事务逻辑非常有用。

DELIMITER //

CREATE PROCEDURE process_order(
    IN order_id INT,
    IN customer_id INT,
    IN product_id INT,
    IN quantity INT
)
BEGIN
    -- 声明变量
    DECLARE product_price DECIMAL(10, 2);
    DECLARE total_amount DECIMAL(10, 2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK TO SAVEPOINT sp1;
        SELECT '订单处理失败，已回滚到保存点' AS message;
    END;

    -- 开启事务
    START TRANSACTION;

    -- 设置保存点
    SAVEPOINT sp1;

    -- 获取商品价格
    SELECT price INTO product_price FROM products WHERE product_id = product_id;

    -- 计算订单总金额
    SET total_amount = product_price * quantity;

    -- 创建订单记录
    INSERT INTO orders (order_id, customer_id, product_id, quantity, total_amount) VALUES (order_id, customer_id, product_id, quantity, total_amount);

    -- 模拟一个错误 (例如，尝试除以 0)
    -- SET @result = 1 / 0;

    -- 更新库存
    UPDATE products SET stock = stock - quantity WHERE product_id = product_id;

    -- 提交事务
    COMMIT;
    SELECT '订单处理成功' AS message;
END //

DELIMITER ;

在这个例子中：

SAVEPOINT sp1 创建了一个名为 sp1 的保存点。
ROLLBACK TO SAVEPOINT sp1 将事务回滚到 sp1 保存点。这意味着在 SAVEPOINT sp1 之后的数据库更改都会被撤销，但 SAVEPOINT sp1 之前的更改仍然有效。
如果 SET @result = 1 / 0; 这行代码被取消注释，那么就会触发一个 SQL 异常，导致事务回滚到 sp1 保存点。

5. 事务隔离级别

MySQL 支持四种事务隔离级别，分别是：

READ UNCOMMITTED (RU): 允许读取未提交的数据。这是最低的隔离级别，可能会导致脏读、不可重复读和幻读。
READ COMMITTED (RC): 只允许读取已提交的数据。可以防止脏读，但仍然可能导致不可重复读和幻读。
REPEATABLE READ (RR): 保证在同一个事务中多次读取同一数据的结果是一致的。可以防止脏读和不可重复读，但仍然可能导致幻读。这是MySQL默认的隔离级别。
SERIALIZABLE: 最高的隔离级别，强制事务串行执行，可以防止所有并发问题，包括脏读、不可重复读和幻读。但是，SERIALIZABLE 隔离级别的性能通常较差。

可以使用以下语句设置事务隔离级别：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

也可以设置全局的隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

选择合适的隔离级别需要在数据一致性和性能之间进行权衡。通常情况下，REPEATABLE READ 隔离级别是一个不错的选择。

6. 隐式事务

在某些情况下，MySQL 会自动启动一个隐式事务。例如，当执行 ALTER TABLE 语句时，MySQL 会自动启动一个事务，以保证表结构修改的原子性。

7. 事务的注意事项

避免长时间运行的事务: 长时间运行的事务会占用数据库资源，并可能导致锁冲突，影响系统性能。尽量将事务分解为更小的单元。
使用索引: 合理使用索引可以加快查询速度，减少锁的持有时间，提高事务的并发性。
避免死锁: 死锁是指两个或多个事务互相等待对方释放资源，导致所有事务都无法继续执行的情况。可以通过合理设计事务逻辑、避免循环依赖、设置锁超时等方式来避免死锁。
监控事务: 使用 MySQL 提供的监控工具，例如 SHOW ENGINE INNODB STATUS，可以监控事务的执行情况，及时发现和解决问题。

8. 实例分析

假设我们有一个在线购物系统，用户可以购买商品。我们需要编写一个存储过程来处理用户的购买请求。

DELIMITER //

CREATE PROCEDURE purchase_product(
    IN customer_id INT,
    IN product_id INT,
    IN quantity INT
)
BEGIN
    -- 声明变量
    DECLARE product_price DECIMAL(10, 2);
    DECLARE stock_quantity INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '购买失败，请稍后再试';
    END;

    -- 开启事务
    START TRANSACTION;

    -- 检查库存是否足够
    SELECT stock INTO stock_quantity FROM products WHERE product_id = product_id FOR UPDATE;

    IF stock_quantity >= quantity THEN
        -- 获取商品价格
        SELECT price INTO product_price FROM products WHERE product_id = product_id;

        -- 更新库存
        UPDATE products SET stock = stock - quantity WHERE product_id = product_id;

        -- 创建订单记录
        INSERT INTO orders (customer_id, product_id, quantity, order_date) VALUES (customer_id, product_id, quantity, NOW());

        -- 提交事务
        COMMIT;
        SELECT '购买成功' AS message;
    ELSE
        -- 回滚事务
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足';
    END IF;
END //

DELIMITER ;

在这个例子中：

我们首先检查库存是否足够。
如果库存足够，我们获取商品价格，更新库存，并创建订单记录。
如果库存不足，我们回滚事务，并抛出一个自定义的错误信息。
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足'; 用于抛出自定义的异常，方便应用程序处理。

表格总结：常用事务管理语句

语句	作用
`START TRANSACTION`	启动一个事务
`COMMIT`	提交事务，将所有更改永久保存到数据库
`ROLLBACK`	回滚事务，撤销所有未提交的更改
`SAVEPOINT`	在事务中创建一个保存点
`ROLLBACK TO`	回滚到指定的保存点
`FOR UPDATE`	锁定查询的行，防止其他事务修改

总结：关键在于保证数据一致性

掌握START TRANSACTION、COMMIT和ROLLBACK以及错误处理机制，能够帮助我们编写更健壮、更可靠的存储过程，保证数据库的数据一致性和完整性。请务必在实际项目中灵活运用这些技术，并根据具体的业务需求选择合适的事务隔离级别。

MySQL存储过程的事务管理：START TRANSACTION、COMMIT和ROLLBACK的用法

发表回复 取消回复

发表回复取消回复