MySQL编程进阶之：存储过程与事务：如何在存储过程中正确使用`START TRANSACTION`、`COMMIT`和`ROLLBACK`。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

各位观众老爷，大家好！我是你们的老朋友，今天咱们来聊聊MySQL存储过程中的事务处理，也就是如何正确地使用START TRANSACTION、COMMIT和ROLLBACK这三个老伙计。这玩意儿要是用不好，轻则数据乱成一锅粥，重则半夜被老板从被窝里挖起来修bug。所以，咱们今天必须把它搞明白！

一、什么是事务？（别跟我说ACID，换个说法！）

咱们先来聊聊啥是事务。别跟我拽那些ACID（原子性、一致性、隔离性、持久性）的定义，太学术了。我就问你，你网购的时候，如果支付成功了，但订单没生成，你咋办？肯定找客服撕逼啊！

事务就是为了保证这类操作的完整性。它就像一个“要么全做，要么全不做”的承诺。要么付款成功，订单也生成；要么付款失败，啥事儿都没发生。

更通俗一点说，事务就是把一系列数据库操作打包成一个“原子操作”，要么全部成功，要么全部失败回滚到最初状态。

二、为什么要在存储过程中用事务？

存储过程就像一个预先编译好的SQL脚本，执行效率高。但是，如果存储过程里面包含多个数据库操作，万一中间某个环节出错了，那数据可能就乱套了。这时候，事务就派上用场了。

使用事务，你可以把存储过程中的一系列操作放在一个事务块里面，如果任何一个操作失败，就可以回滚到事务开始之前的状态，保证数据的完整性。

三、 START TRANSACTION、COMMIT和ROLLBACK：三剑客登场！

这三个家伙就是事务处理的核心指令。

START TRANSACTION (或者 BEGIN): 告诉MySQL，我要开始一个新的事务了，后面的操作要么一起成功，要么一起失败。 BEGIN是START TRANSACTION的别名，效果完全一样。
COMMIT: 告诉MySQL，这个事务里面的所有操作都成功了，可以把数据永久保存到数据库里面了。就像你按下“确认支付”按钮一样。
ROLLBACK: 告诉MySQL，这个事务里面的某些操作失败了，我要撤销所有操作，回到事务开始之前的状态。就像你取消支付一样。

四、如何在存储过程中使用事务？（代码说话！）

咱们来看个例子，假设我们要写一个存储过程，实现以下功能：

从products表中扣除商品的库存。
在orders表中创建一个新的订单。
在order_items表中添加订单的商品明细。

如果任何一个步骤失败，比如库存不足，我们就需要回滚所有操作。

DELIMITER //

CREATE PROCEDURE create_order(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT
)
BEGIN
    -- 声明变量
    DECLARE v_product_price DECIMAL(10, 2);
    DECLARE v_product_stock INT;
    DECLARE v_order_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新抛出异常
    END;

    START TRANSACTION;

    -- 1. 获取商品价格和库存
    SELECT price, stock INTO v_product_price, v_product_stock
    FROM products
    WHERE id = p_product_id;

    -- 2. 检查库存是否足够
    IF v_product_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '库存不足';
    END IF;

    -- 3. 扣除商品库存
    UPDATE products
    SET stock = stock - p_quantity
    WHERE id = p_product_id;

    -- 4. 创建新的订单
    INSERT INTO orders (customer_id, order_date)
    VALUES (p_customer_id, NOW());

    -- 5. 获取新订单的ID
    SET v_order_id = LAST_INSERT_ID();

    -- 6. 添加订单的商品明细
    INSERT INTO order_items (order_id, product_id, quantity, price)
    VALUES (v_order_id, p_product_id, p_quantity, v_product_price);

    COMMIT;

    SELECT v_order_id AS order_id; -- 返回订单ID

END //

DELIMITER ;

代码解释：

DELIMITER //: 这个是MySQL的语句分隔符。因为存储过程里面有很多语句，MySQL默认用;作为语句分隔符，会把存储过程的定义搞乱，所以我们先把它改成//，存储过程定义完之后再改回来。
CREATE PROCEDURE create_order(...): 创建存储过程，定义输入参数。
DECLARE: 声明变量，用于存储商品价格、库存和订单ID。
DECLARE EXIT HANDLER FOR SQLEXCEPTION: 这个是重点！它定义了一个异常处理器。如果存储过程里面发生了任何SQL异常（比如违反唯一约束、除以0等等），就会自动执行BEGIN ... ROLLBACK; RESIGNAL; END; 里面的代码。 ROLLBACK 负责回滚事务，RESIGNAL负责重新抛出异常，这样调用者才能知道存储过程执行失败了。如果不加 RESIGNAL，调用者可能以为存储过程执行成功了，但实际上数据已经被回滚了，这会导致更严重的错误。
START TRANSACTION: 开始一个新的事务。
SELECT ... INTO ... FROM ...: 从products表中查询商品的价格和库存。
IF ... THEN ... SIGNAL ... END IF: 检查库存是否足够。如果库存不足，就抛出一个自定义的SQL异常。 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存不足'; 这句代码会抛出一个SQLSTATE为’45000’的异常，错误信息是’库存不足’。 45000 是一个通用的SQLSTATE，表示用户自定义的错误。
UPDATE ... SET ... WHERE ...: 扣除商品的库存。
INSERT INTO ... VALUES ...: 创建新的订单，并获取新订单的ID。
COMMIT: 提交事务，把所有操作永久保存到数据库里面。
SELECT v_order_id AS order_id: 返回新订单的ID。

五、错误处理：不止有SQLEXCEPTION！

上面的例子只处理了SQLEXCEPTION，但实际情况可能更复杂。你可能需要处理特定的错误，比如重复插入、外键约束等等。

DELIMITER //

CREATE PROCEDURE create_order_with_error_handling(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT
)
BEGIN
    -- 声明变量
    DECLARE v_product_price DECIMAL(10, 2);
    DECLARE v_product_stock INT;
    DECLARE v_order_id INT;

    -- 声明错误处理
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新抛出异常
    END;

    DECLARE EXIT HANDLER FOR SQLSTATE '23000' -- 处理违反唯一约束或外键约束
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '违反约束条件';
    END;

    DECLARE EXIT HANDLER FOR SQLSTATE '45000' -- 处理自定义的错误
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新抛出异常
    END;

    START TRANSACTION;

    -- 1. 获取商品价格和库存
    SELECT price, stock INTO v_product_price, v_product_stock
    FROM products
    WHERE id = p_product_id;

    -- 2. 检查库存是否足够
    IF v_product_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '库存不足';
    END IF;

    -- 3. 扣除商品库存
    UPDATE products
    SET stock = stock - p_quantity
    WHERE id = p_product_id;

    -- 4. 创建新的订单
    INSERT INTO orders (customer_id, order_date)
    VALUES (p_customer_id, NOW());

    -- 5. 获取新订单的ID
    SET v_order_id = LAST_INSERT_ID();

    -- 6. 添加订单的商品明细
    INSERT INTO order_items (order_id, product_id, quantity, price)
    VALUES (v_order_id, p_product_id, p_quantity, v_product_price);

    COMMIT;

    SELECT v_order_id AS order_id; -- 返回订单ID

END //

DELIMITER ;

代码解释：

DECLARE EXIT HANDLER FOR SQLSTATE '23000': 处理SQLSTATE为’23000’的异常。 ‘23000’表示违反唯一约束或外键约束。如果发生这类异常，存储过程会回滚事务，并抛出一个自定义的SQL异常，错误信息是’违反约束条件’。

你可以根据实际情况，添加更多的错误处理器，处理不同的SQLSTATE。常见的SQLSTATE可以参考MySQL官方文档。

六、 SAVEPOINT：事务中的“后悔药”

有时候，你可能不想回滚整个事务，只想回滚到事务中的某个中间状态。这时候，SAVEPOINT就派上用场了。 SAVEPOINT 就像一个“存档点”，你可以随时回滚到这个存档点。

DELIMITER //

CREATE PROCEDURE create_order_with_savepoint(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT
)
BEGIN
    -- 声明变量
    DECLARE v_product_price DECIMAL(10, 2);
    DECLARE v_product_stock INT;
    DECLARE v_order_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL; -- 重新抛出异常
    END;

    START TRANSACTION;

    -- 1. 获取商品价格和库存
    SELECT price, stock INTO v_product_price, v_product_stock
    FROM products
    WHERE id = p_product_id;

    -- 2. 检查库存是否足够
    IF v_product_stock < p_quantity THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '库存不足';
    END IF;

    -- 3. 扣除商品库存
    UPDATE products
    SET stock = stock - p_quantity
    WHERE id = p_product_id;

    -- 创建一个保存点
    SAVEPOINT after_stock_update;

    -- 4. 创建新的订单
    INSERT INTO orders (customer_id, order_date)
    VALUES (p_customer_id, NOW());

    -- 5. 获取新订单的ID
    SET v_order_id = LAST_INSERT_ID();

    -- 6. 添加订单的商品明细
    INSERT INTO order_items (order_id, product_id, quantity, price)
    VALUES (v_order_id, p_product_id, p_quantity, v_product_price);

    COMMIT;

    SELECT v_order_id AS order_id; -- 返回订单ID

    -- 如果需要回滚到保存点： ROLLBACK TO SAVEPOINT after_stock_update;

END //

DELIMITER ;

代码解释：

SAVEPOINT after_stock_update: 创建一个名为after_stock_update的保存点。
ROLLBACK TO SAVEPOINT after_stock_update: 如果需要回滚到这个保存点，就执行这句代码。注意，回滚到保存点之后，事务并没有结束，你还可以继续执行其他操作，或者提交事务。

七、一些需要注意的坑（不填就掉坑里了！）

自动提交: MySQL默认是自动提交的，也就是说，每执行一条SQL语句，都会自动提交。如果你想使用事务，必须先关闭自动提交。可以通过以下语句关闭自动提交：
```
SET autocommit = 0;
```
但是，在存储过程中，MySQL会自动处理autocommit，所以你不需要手动设置。
锁: 事务会涉及到锁的问题。如果多个事务同时修改同一行数据，可能会发生死锁。你需要了解MySQL的锁机制，避免死锁的发生。
嵌套事务: MySQL不支持真正的嵌套事务。也就是说，在一个事务里面再开始一个新的事务，实际上还是在同一个事务里面。如果你想模拟嵌套事务，可以使用SAVEPOINT。
存储引擎: 只有支持事务的存储引擎（比如InnoDB）才能使用事务。如果你的表使用的是不支持事务的存储引擎（比如MyISAM），START TRANSACTION、COMMIT和ROLLBACK 会被忽略。
显式锁表: 某些场景，例如需要批量更新数据时，可能需要显式锁表以防止其他事务干扰。可以使用LOCK TABLES和UNLOCK TABLES命令，但务必谨慎使用，避免长时间锁定资源。
```
LOCK TABLES products WRITE;
-- 执行更新操作
UPDATE products SET stock = stock - 10 WHERE id = 1;
UNLOCK TABLES;
```

八、最佳实践（避免被同事diss！）

保持事务简短: 事务的时间越长，锁定的资源就越多，发生死锁的可能性就越大。尽量保持事务简短，只包含必要的数据库操作。
避免在事务中进行耗时操作: 比如发送邮件、调用外部接口等等。这些操作应该放在事务之外进行。
使用索引: 索引可以提高查询效率，减少事务的锁定时间。
监控: 监控数据库的事务和锁，及时发现和解决问题。

九、总结（别忘了点赞！）

今天我们聊了MySQL存储过程中的事务处理，包括START TRANSACTION、COMMIT、ROLLBACK、SAVEPOINT、错误处理和一些需要注意的坑。希望大家能够掌握这些知识，写出更健壮、更可靠的存储过程。记住，事务是保证数据完整性的重要手段，一定要重视！

最后，别忘了点赞、评论、转发，支持一下我这个老朋友！咱们下期再见！

发表回复 取消回复

发表回复取消回复