MySQL——事务

一、什么是事务？

事务（Transaction） 是数据库操作的最小逻辑单元，它由一组不可分割的SQL操作组成。事务的核心目标是确保多个操作要么全部成功，要么全部失败，从而维护数据的完整性。例如，银行转账场景中，张三给李四转账1000元，必须同时完成“张三账户减1000”和“李四账户加1000”两个操作，否则就会导致数据不一致。

二、事务的四大特性（ACID）

三、事务操作：手动控制与自动提交

1. 默认行为：自动提交

MySQL默认开启自动提交（autocommit=1），每条SQL语句独立为一个事务，执行后立即提交。

-- 查看当前提交模式
SELECT @@autocommit;  -- 返回1表示自动提交

2. 手动控制事务

通过以下命令显式控制事务流程：

-- 开启事务
START TRANSACTION;

-- 执行操作（如转账）
UPDATE account SET money = money - 1000 WHERE name = '张三';
UPDATE account SET money = money + 1000 WHERE name = '李四';

-- 提交事务（持久化更改）
COMMIT;

-- 回滚事务（撤销所有操作）
ROLLBACK;

四、事务中的错误处理

1. 使用SIGNAL抛出异常

在存储过程中，可通过SIGNAL主动触发错误并回滚：

DELIMITER $$
CREATE PROCEDURE transfer()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; -- 异常时回滚
    START TRANSACTION;
    -- 检查余额
    IF (SELECT money FROM account WHERE name='张三') < 1000 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    -- 执行转账
    UPDATE account SET money = money - 1000 WHERE name = '张三';
    UPDATE account SET money = money + 1000 WHERE name = '李四';
    COMMIT;
END $$
DELIMITER ;

2. 使用ROW_COUNT()验证操作

通过检查受影响行数判断操作是否成功：

START TRANSACTION;
-- 扣款操作
UPDATE account SET money = money - 1000 WHERE name = '张三' AND money >= 1000;
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;

五、存储过程与触发器中的事务

1. 存储过程的事务控制

存储过程可显式管理事务，适合复杂业务逻辑：

DELIMITER $$
CREATE PROCEDURE transfer_funds(IN from_id INT, IN to_id INT, IN amount DECIMAL)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    -- 扣款
    UPDATE account SET money = money - amount WHERE id = from_id;
    -- 加款
    UPDATE account SET money = money + amount WHERE id = to_id;
    COMMIT;
END $$
DELIMITER ;

2. 触发器与事务的关系

• 触发器不能显式控制事务，但错误会触发外层事务回滚。

• 示例：禁止账户余额为负

CREATE TRIGGER check_balance
BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
    IF NEW.money < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不能为负';
    END IF;
END;

六、实战案例：转账事务的完整实现

1. 数据准备

CREATE TABLE account (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
    money DECIMAL(10,2)
);
INSERT INTO account (name, money) VALUES ('张三', 2000), ('李四', 2000);

2. 实现转账逻辑

DELIMITER $$
CREATE PROCEDURE secure_transfer(
    IN from_name VARCHAR(20),
    IN to_name VARCHAR(20),
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
    -- 检查余额
    IF (SELECT money FROM account WHERE name = from_name) < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '余额不足';
    END IF;
    -- 执行转账
    UPDATE account SET money = money - amount WHERE name = from_name;
    UPDATE account SET money = money + amount WHERE name = to_name;
    COMMIT;
END $$
DELIMITER ;

-- 调用示例
CALL secure_transfer('张三', '李四', 1000);

七、事务的最佳实践

1. 短事务原则：尽量减小事务范围，避免长时间锁定资源。

2. 合理设置隔离级别：默认REPEATABLE READ适用于多数场景，高并发时可考虑READ COMMITTED。

3. 避免嵌套事务：MySQL不支持嵌套事务，复杂逻辑用保存点（SAVEPOINT）替代。

4. 监控与优化：使用SHOW ENGINE INNODB STATUS分析事务锁情况。