MySQL 事务 - MySQL 教程 | 小傅哥技术教程

事务概述

事务（Transaction）是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成。事务确保数据库的完整性和一致性，即使在系统故障的情况下也能保持数据的可靠性。

                    事务的特点：
                    事务是一个不可分割的工作单位
事务中的操作要么全部成功，要么全部失败
事务必须满足 ACID 特性
事务可以包含多个 SQL 语句

                

ACID 特性

ACID 是事务处理的四个基本特性，确保数据库事务的可靠性：

🔒 原子性 (Atomicity)

事务是一个不可分割的工作单位，要么全部完成，要么全部不做

✅ 一致性 (Consistency)

事务必须使数据库从一个一致性状态变换到另一个一致性状态

🔐 隔离性 (Isolation)

一个事务的执行不能被其他事务干扰

💾 持久性 (Durability)

事务一旦提交，其对数据库的改变就是永久性的

事务控制语句

基本语法

-- 开始事务
START TRANSACTION;
-- 或者
BEGIN;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置保存点
SAVEPOINT savepoint_name;

-- 回滚到保存点
ROLLBACK TO savepoint_name;

-- 释放保存点
RELEASE SAVEPOINT savepoint_name;
                

注意：MySQL 默认开启自动提交模式（autocommit=1），每个 SQL 语句都会自动提交。要使用事务，需要显式开始事务或关闭自动提交。

示例数据准备

-- 创建银行账户表
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    account_number VARCHAR(20) UNIQUE NOT NULL,
    account_name VARCHAR(50) NOT NULL,
    balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建交易记录表
CREATE TABLE transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_account VARCHAR(20),
    to_account VARCHAR(20),
    amount DECIMAL(15,2) NOT NULL,
    transaction_type ENUM('transfer', 'deposit', 'withdraw') NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (from_account) REFERENCES accounts(account_number),
    FOREIGN KEY (to_account) REFERENCES accounts(account_number)
);

-- 插入测试数据
INSERT INTO accounts (account_number, account_name, balance) VALUES
('ACC001', '张三', 10000.00),
('ACC002', '李四', 5000.00),
('ACC003', '王五', 8000.00);
                

基本事务操作

简单转账事务

-- 转账事务：从张三账户转 1000 元到李四账户
START TRANSACTION;

-- 检查余额
SELECT balance FROM accounts WHERE account_number = 'ACC001';

-- 扣除转出账户余额
UPDATE accounts 
SET balance = balance - 1000.00 
WHERE account_number = 'ACC001';

-- 增加转入账户余额
UPDATE accounts 
SET balance = balance + 1000.00 
WHERE account_number = 'ACC002';

-- 记录交易
INSERT INTO transactions (from_account, to_account, amount, transaction_type, description)
VALUES ('ACC001', 'ACC002', 1000.00, 'transfer', '转账给李四');

-- 提交事务
COMMIT;
                

带错误处理的事务

-- 带余额检查的转账事务
START TRANSACTION;

-- 检查转出账户余额
SELECT @from_balance := balance FROM accounts WHERE account_number = 'ACC001';

-- 检查余额是否足够
SELECT @sufficient := (@from_balance >= 2000.00);

-- 如果余额不足，回滚事务
-- 在实际应用中，这通常在应用程序中处理
UPDATE accounts 
SET balance = balance - 2000.00 
WHERE account_number = 'ACC001' AND balance >= 2000.00;

-- 检查是否成功更新（ROW_COUNT() 返回受影响的行数）
SELECT @updated := ROW_COUNT();

-- 如果更新失败，回滚
-- 在存储过程中可以使用条件语句
UPDATE accounts 
SET balance = balance + 2000.00 
WHERE account_number = 'ACC002';

INSERT INTO transactions (from_account, to_account, amount, transaction_type, description)
VALUES ('ACC001', 'ACC002', 2000.00, 'transfer', '大额转账');

-- 提交事务
COMMIT;
                

保存点的使用

-- 使用保存点的复杂事务
START TRANSACTION;

-- 第一步：存款
UPDATE accounts SET balance = balance + 500.00 WHERE account_number = 'ACC001';
INSERT INTO transactions (to_account, amount, transaction_type, description)
VALUES ('ACC001', 500.00, 'deposit', '现金存款');

-- 设置保存点
SAVEPOINT after_deposit;

-- 第二步：转账
UPDATE accounts SET balance = balance - 300.00 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 300.00 WHERE account_number = 'ACC003';
INSERT INTO transactions (from_account, to_account, amount, transaction_type, description)
VALUES ('ACC001', 'ACC003', 300.00, 'transfer', '转账给王五');

-- 设置另一个保存点
SAVEPOINT after_transfer;

-- 第三步：尝试大额提取（可能失败）
UPDATE accounts SET balance = balance - 15000.00 
WHERE account_number = 'ACC001' AND balance >= 15000.00;

-- 如果提取失败，回滚到转账后的状态
-- 检查是否成功
SELECT ROW_COUNT() as affected_rows;

-- 假设提取失败，回滚到转账后
ROLLBACK TO after_transfer;

-- 提交剩余的操作（存款和转账）
COMMIT;
                

事务隔离级别

MySQL 支持四种事务隔离级别，用于控制事务之间的相互影响：

隔离级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	可能	可能	可能	最低级别，性能最好
READ COMMITTED	避免	可能	可能	Oracle 默认级别
REPEATABLE READ	避免	避免	可能	MySQL 默认级别
SERIALIZABLE	避免	避免	避免	最高级别，性能最差

设置隔离级别

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置会话级别的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 为下一个事务设置隔离级别
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- 事务操作
COMMIT;
                

隔离级别演示

-- 会话 1：演示不可重复读
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE account_number = 'ACC001';  -- 第一次读取
-- 此时会话 2 修改了数据
SELECT balance FROM accounts WHERE account_number = 'ACC001';  -- 第二次读取，结果可能不同
COMMIT;

-- 会话 1：演示可重复读
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE account_number = 'ACC001';  -- 第一次读取
-- 此时会话 2 修改了数据
SELECT balance FROM accounts WHERE account_number = 'ACC001';  -- 第二次读取，结果相同
COMMIT;
                

锁机制

表级锁

-- 锁定表（读锁）
LOCK TABLES accounts READ;
SELECT * FROM accounts;  -- 可以读取
-- UPDATE accounts SET balance = 1000 WHERE id = 1;  -- 会报错，不能写入
UNLOCK TABLES;

-- 锁定表（写锁）
LOCK TABLES accounts WRITE;
SELECT * FROM accounts;   -- 可以读取
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC001';  -- 可以写入
UNLOCK TABLES;
                

行级锁

-- 共享锁（读锁）
START TRANSACTION;
SELECT * FROM accounts WHERE account_number = 'ACC001' LOCK IN SHARE MODE;
-- 其他事务可以读取，但不能修改这行数据
COMMIT;

-- 排他锁（写锁）
START TRANSACTION;
SELECT * FROM accounts WHERE account_number = 'ACC001' FOR UPDATE;
-- 其他事务不能读取或修改这行数据
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
COMMIT;
                

死锁处理

-- 查看死锁信息
SHOW ENGINE INNODB STATUS;

-- 设置死锁超时时间
SET innodb_lock_wait_timeout = 50;

-- 死锁示例（需要两个会话同时执行）
-- 会话 1:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
-- 等待会话 2 执行第一个 UPDATE
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC002';
COMMIT;

-- 会话 2:
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC002';
-- 等待会话 1 执行第一个 UPDATE
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC001';
COMMIT;
                

自动提交模式

-- 查看自动提交状态
SELECT @@autocommit;

-- 关闭自动提交
SET autocommit = 0;
-- 现在每个语句都需要手动提交
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC001';
COMMIT;  -- 必须手动提交

-- 开启自动提交
SET autocommit = 1;
-- 每个语句自动提交
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC001';
-- 自动提交，无需手动 COMMIT
                

注意：即使在自动提交模式下，START TRANSACTION 也会开始一个显式事务，需要手动 COMMIT 或 ROLLBACK。

存储过程中的事务

-- 创建转账存储过程
DELIMITER //

CREATE PROCEDURE TransferMoney(
    IN from_acc VARCHAR(20),
    IN to_acc VARCHAR(20),
    IN amount DECIMAL(15,2),
    IN description VARCHAR(255),
    OUT result_code INT,
    OUT result_message VARCHAR(255)
)
BEGIN
    DECLARE from_balance DECIMAL(15,2);
    DECLARE exit_flag INT DEFAULT 0;
    
    -- 声明异常处理
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SET exit_flag = 1;
        ROLLBACK;
        SET result_code = -1;
        SET result_message = 'Transaction failed due to SQL exception';
    END;
    
    -- 开始事务
    START TRANSACTION;
    
    -- 检查转出账户余额
    SELECT balance INTO from_balance 
    FROM accounts 
    WHERE account_number = from_acc;
    
    -- 检查余额是否足够
    IF from_balance < amount THEN
        ROLLBACK;
        SET result_code = -2;
        SET result_message = 'Insufficient balance';
    ELSE
        -- 执行转账
        UPDATE accounts 
        SET balance = balance - amount 
        WHERE account_number = from_acc;
        
        UPDATE accounts 
        SET balance = balance + amount 
        WHERE account_number = to_acc;
        
        -- 记录交易
        INSERT INTO transactions (from_account, to_account, amount, transaction_type, description)
        VALUES (from_acc, to_acc, amount, 'transfer', description);
        
        -- 检查是否有异常
        IF exit_flag = 0 THEN
            COMMIT;
            SET result_code = 0;
            SET result_message = 'Transfer successful';
        END IF;
    END IF;
END //

DELIMITER ;

-- 调用存储过程
CALL TransferMoney('ACC001', 'ACC002', 500.00, '存储过程转账', @code, @msg);
SELECT @code, @msg;
                

事务日志和恢复

查看事务状态

-- 查看当前活动的事务
SELECT * FROM information_schema.INNODB_TRX;

-- 查看锁等待情况
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看锁信息
SELECT * FROM information_schema.INNODB_LOCKS;

-- 查看事务隔离级别
SELECT @@transaction_isolation;

-- 查看二进制日志状态
SHOW BINARY LOGS;

-- 查看事务日志大小
SHOW VARIABLES LIKE 'innodb_log_file_size';
                

事务性能监控

-- 查看 InnoDB 状态
SHOW ENGINE INNODB STATUS;

-- 查看事务相关的状态变量
SHOW STATUS LIKE 'Innodb_rows_%';
SHOW STATUS LIKE 'Com_commit';
SHOW STATUS LIKE 'Com_rollback';

-- 查看锁等待统计
SHOW STATUS LIKE 'Innodb_lock_time%';
SHOW STATUS LIKE 'Innodb_deadlocks';
                

事务最佳实践

                    事务使用最佳实践：
                    保持事务尽可能短小，减少锁定时间
避免在事务中进行用户交互或长时间等待
合理选择事务隔离级别
使用适当的锁策略，避免死锁
在应用程序中正确处理事务异常
定期监控事务性能和死锁情况

                

事务设计原则

-- 好的事务设计
START TRANSACTION;
-- 1. 快速获取需要的数据
SELECT balance FROM accounts WHERE account_number = 'ACC001' FOR UPDATE;
-- 2. 执行必要的业务逻辑
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC002';
-- 3. 立即提交
COMMIT;

-- 避免的做法
START TRANSACTION;
SELECT balance FROM accounts WHERE account_number = 'ACC001';
-- 不要在事务中进行复杂计算或外部调用
-- 不要等待用户输入
-- 不要进行长时间的操作
UPDATE accounts SET balance = balance - 100 WHERE account_number = 'ACC001';
COMMIT;
                

错误处理模式

-- 应用程序中的事务处理模式（伪代码）
/*
BEGIN
    START TRANSACTION;
    
    TRY {
        -- 执行业务操作
        UPDATE accounts SET balance = balance - amount WHERE account_number = from_account;
        UPDATE accounts SET balance = balance + amount WHERE account_number = to_account;
        INSERT INTO transactions (...) VALUES (...);
        
        COMMIT;
        return success;
    } CATCH (Exception e) {
        ROLLBACK;
        log_error(e);
        return failure;
    }
END
*/
                

常见问题和解决方案

常见事务问题：

长事务导致锁等待和性能问题
死锁导致事务回滚
隔离级别设置不当导致数据不一致
忘记提交或回滚事务
在事务中执行 DDL 语句导致隐式提交

DDL 语句的隐式提交

-- 注意：DDL 语句会导致隐式提交
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE account_number = 'ACC001';
-- 下面的 DDL 语句会自动提交上面的 UPDATE
CREATE TABLE temp_table (id INT);  -- 隐式提交
-- 此时无法回滚之前的 UPDATE 操作
ROLLBACK;  -- 只能回滚 CREATE TABLE 之后的操作
                

事务超时处理

-- 设置事务超时时间
SET SESSION innodb_lock_wait_timeout = 10;  -- 10秒超时

-- 设置事务大小限制
SET SESSION max_binlog_cache_size = 1048576;  -- 1MB

-- 监控长时间运行的事务
SELECT 
    trx_id,
    trx_started,
    trx_state,
    trx_requested_lock_id,
    trx_wait_started,
    trx_mysql_thread_id,
    TIMESTAMPDIFF(SECOND, trx_started, NOW()) as duration_seconds
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 30;  -- 超过30秒的事务
                

🔄 MySQL 事务