MySQL事务管理

事务是数据库管理系统执行过程中的一个逻辑单位，由一个或多个SQL语句组成，这些语句要么全部执行成功，要么全部不执行。本文档将详细介绍MySQL事务管理的概念、特性、隔离级别和最佳实践。

事务基础概念

ACID特性

事务必须满足ACID四个特性：

原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败。

sql

START TRANSACTION;
INSERT INTO accounts (id, balance) VALUES (1, 1000);
INSERT INTO accounts (id, balance) VALUES (2, 500);
-- 如果下面的语句失败，所有操作都会回滚
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

一致性（Consistency）：事务执行前后，数据库都必须处于一致状态。

sql

-- 转账操作必须保持总金额不变
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 确保总金额不变
SELECT SUM(balance) FROM accounts; -- 应该等于操作前的总金额
COMMIT;

隔离性（Isolation）：并发执行的事务之间不能相互干扰。

sql

-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读取余额
-- 此时事务B可能修改了余额，但事务A看不到
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

持久性（Durability）：事务提交后，对数据库的修改是永久性的。

sql

START TRANSACTION;
INSERT INTO transactions (from_account, to_account, amount) VALUES (1, 2, 100);
COMMIT; -- 提交后，即使系统崩溃，数据也不会丢失

事务语法

开始事务：

sql

-- 显式开始事务
START TRANSACTION;

-- 或使用别名
BEGIN;

提交事务：

sql

-- 提交事务，使更改永久生效
COMMIT;

回滚事务：

sql

-- 回滚事务，撤销所有更改
ROLLBACK;

事务隔离级别

MySQL支持四种事务隔离级别，每种级别解决不同程度的并发问题：

读未提交（READ UNCOMMITTED）

最低的隔离级别，允许读取未提交的数据，可能产生脏读问题。

sql

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 事务A未提交，但事务B可以读取到这个未提交的更改（脏读）

-- 事务B
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 可能读取到事务A未提交的数据

读已提交（READ COMMITTED）

只允许读取已提交的数据，解决了脏读问题，但可能出现不可重复读。

sql

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读取余额，假设为1000
-- 事务B在此期间提交了更新
SELECT balance FROM accounts WHERE id = 1; -- 可能读取到不同的值（不可重复读）
COMMIT;

可重复读（REPEATABLE READ）

MySQL默认的隔离级别，确保在同一事务中多次读取同一数据结果相同，解决了脏读和不可重复读问题。

sql

-- 设置隔离级别（MySQL默认）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 第一次读取，值为1000
-- 事务B修改并提交了数据
SELECT balance FROM accounts WHERE id = 1; -- 仍然读取到1000（可重复读）
COMMIT;

串行化（SERIALIZABLE）

最高的隔离级别，强制事务串行执行，解决了幻读问题，但性能最差。

sql

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- 事务A
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 1000;
-- 事务B无法插入满足条件的新记录，直到事务A结束
COMMIT;

并发问题

脏读（Dirty Read）

一个事务读取了另一个事务未提交的数据。

sql

-- 演示脏读问题
-- 事务A
START TRANSACTION;
UPDATE accounts SET balance = 900 WHERE id = 1; -- 余额从1000改为900
-- 注意：事务A未提交

-- 事务B
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读取到900（脏数据）
-- 事务A可能回滚，但事务B已经读取了无效数据

不可重复读（Non-repeatable Read）

同一个事务中，多次读取同一数据得到不同的结果。

sql

-- 演示不可重复读问题
-- 事务A
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1; -- 读取到1000
-- 事务B在此期间修改并提交了数据
SELECT balance FROM accounts WHERE id = 1; -- 读取到900
COMMIT;

幻读（Phantom Read）

同一个事务中，相同的查询返回不同的行集。

sql

-- 演示幻读问题
-- 事务A
START TRANSACTION;
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- 返回5
-- 事务B插入了一条满足条件的新记录并提交
SELECT COUNT(*) FROM accounts WHERE balance > 1000; -- 返回6（幻读）
COMMIT;

事务控制语句

SAVEPOINT

在事务中设置保存点，允许部分回滚。

sql

START TRANSACTION;

INSERT INTO orders (user_id, amount) VALUES (1, 100);
SAVEPOINT sp1;

INSERT INTO orders (user_id, amount) VALUES (2, 200);
SAVEPOINT sp2;

-- 发现第二个订单有问题，回滚到sp1
ROLLBACK TO sp2; -- 回滚到sp2保存点
-- 或者
-- ROLLBACK TO sp1; -- 回滚到sp1保存点

COMMIT; -- 提交剩余的更改

事务注释

sql

-- 使用注释标记事务用途
START TRANSACTION;
-- 用户注册事务
INSERT INTO users (username, email) VALUES ('newuser', 'newuser@example.com');
INSERT INTO profiles (user_id, created_at) VALUES (LAST_INSERT_ID(), NOW());
COMMIT;

自动提交模式

MySQL默认开启自动提交模式，每个SQL语句都被当作一个独立事务。

sql

-- 查看自动提交状态
SELECT @@autocommit; -- 1表示开启，0表示关闭

-- 关闭自动提交
SET autocommit = 0;

-- 手动控制事务
INSERT INTO users (username) VALUES ('user1');
INSERT INTO users (username) VALUES ('user2');
COMMIT; -- 必须显式提交

-- 重新开启自动提交
SET autocommit = 1;

存储引擎对事务的支持

InnoDB（推荐）

完整的事务支持，支持ACID特性。

sql

CREATE TABLE transactional_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=InnoDB;

-- 完整的事务支持
START TRANSACTION;
INSERT INTO transactional_table VALUES (1, 'data1');
COMMIT;

MyISAM

不支持事务，所有操作都是自动提交的。

sql

CREATE TABLE non_transactional_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE=MyISAM;

-- 不支持事务，每个语句都是自动提交的
INSERT INTO non_transactional_table VALUES (1, 'data1');
-- 无法回滚

事务最佳实践

1. 事务范围控制

保持事务尽可能短，减少锁的持有时间。

sql

-- 不好的做法：长时间运行的事务
START TRANSACTION;
SELECT * FROM large_table; -- 处理大量数据
-- 长时间计算
UPDATE accounts SET balance = new_balance WHERE id = 1;
COMMIT;

-- 好的做法：缩短事务范围
SELECT * FROM large_table; -- 在事务外处理数据
START TRANSACTION;
UPDATE accounts SET balance = calculated_balance WHERE id = 1;
COMMIT;

2. 错误处理

在事务中正确处理错误和异常。

sql

DELIMITER //
CREATE PROCEDURE transfer_money(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    -- 检查余额
    SET @balance = (SELECT balance FROM accounts WHERE id = from_account FOR UPDATE);
    IF @balance < amount THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
    
    -- 执行转账
    UPDATE accounts SET balance = balance - amount WHERE id = from_account;
    UPDATE accounts SET balance = balance + amount WHERE id = to_account;
    
    COMMIT;
END //
DELIMITER ;

3. 死锁处理

实现重试机制处理死锁。

sql

DELIMITER //
CREATE PROCEDURE safe_transfer(IN from_account INT, IN to_account INT, IN amount DECIMAL(10,2))
BEGIN
    DECLARE attempts INT DEFAULT 0;
    DECLARE max_attempts INT DEFAULT 5;
    DECLARE deadlock_detected INT DEFAULT 0;
    
    transfer_loop: WHILE attempts < max_attempts DO
        BEGIN
            DECLARE EXIT HANDLER FOR 1213, 1205  -- Deadlock or lock wait timeout
            BEGIN
                SET deadlock_detected = 1;
                ROLLBACK;
            END;
            
            SET deadlock_detected = 0;
            START TRANSACTION;
            
            -- 按ID顺序锁定账户以避免死锁
            IF from_account < to_account THEN
                SELECT balance FROM accounts WHERE id = from_account FOR UPDATE;
                SELECT balance FROM accounts WHERE id = to_account FOR UPDATE;
            ELSE
                SELECT balance FROM accounts WHERE id = to_account FOR UPDATE;
                SELECT balance FROM accounts WHERE id = from_account FOR UPDATE;
            END IF;
            
            UPDATE accounts SET balance = balance - amount WHERE id = from_account;
            UPDATE accounts SET balance = balance + amount WHERE id = to_account;
            
            COMMIT;
            
            IF deadlock_detected = 0 THEN
                LEAVE transfer_loop;
            END IF;
            
            SET attempts = attempts + 1;
            SELECT SLEEP(0.1 * attempts); -- 递增延迟
        END;
    END WHILE;
    
    IF deadlock_detected = 1 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Transfer failed after multiple attempts';
    END IF;
END //
DELIMITER ;

事务监控

查看当前事务

sql

-- 查看当前运行的事务
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

事务统计信息

sql

-- 查看事务统计
SHOW ENGINE INNODB STATUS\G

-- 查看当前事务
SELECT * FROM information_schema.innodb_trx;

事务与锁

行级锁

InnoDB支持行级锁，减少锁冲突。

sql

-- 显式获取行级锁
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- 排他锁
-- 其他事务无法修改这行数据，直到当前事务结束
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

意向锁

InnoDB使用意向锁来协调行锁和表锁。

sql

-- 意向共享锁（IS）和意向排他锁（IX）
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- 获取共享锁
-- 这会先获取表的意向共享锁，再获取行的共享锁
COMMIT;

分布式事务

MySQL支持XA分布式事务。

sql

-- XA事务示例
XA START 'xid1';
INSERT INTO table1 VALUES (1, 'data');
XA END 'xid1';
XA PREPARE 'xid1';
XA COMMIT 'xid1';

-- 或者回滚
-- XA ROLLBACK 'xid1';

事务性能优化

1. 减少事务冲突

sql

-- 避免热点更新
-- 不好的做法：同时更新同一个计数器
UPDATE counter_table SET value = value + 1; -- 所有事务都竞争同一行

-- 好的做法：分区计数器
UPDATE counter_table SET value = value + 1 WHERE partition_id = CONNECTION_ID() % 10;
-- 最后汇总
SELECT SUM(value) FROM counter_table;

2. 批量操作

sql

-- 在一个事务中批量处理
START TRANSACTION;
INSERT INTO logs (message, created_at) VALUES 
    ('message1', NOW()),
    ('message2', NOW()),
    ('message3', NOW());
COMMIT;

事务安全实践

1. 数据验证

sql

-- 在事务中进行数据验证
START TRANSACTION;
SET @current_balance = (SELECT balance FROM accounts WHERE id = 1 FOR UPDATE);
IF @current_balance >= 100 THEN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    INSERT INTO transactions (account_id, amount, type) VALUES (1, -100, 'withdraw');
ELSE
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient funds';
END IF;
COMMIT;

2. 审计日志

sql

-- 在事务中记录审计日志
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
INSERT INTO audit_log (table_name, operation, record_id, old_value, new_value, user_id, timestamp)
VALUES ('accounts', 'UPDATE', 1, '1000', '900', 1, NOW());
COMMIT;

总结

MySQL事务管理是确保数据一致性和完整性的重要机制。在使用事务时，需要注意：

选择合适的隔离级别
保持事务尽可能短
正确处理错误和异常
防范死锁和并发问题
监控事务性能
遵循安全编程实践

通过合理使用事务，可以构建可靠、一致的数据库应用系统。

MySQL事务管理 #

事务基础概念 #

ACID特性 #

事务语法 #

事务隔离级别 #

读未提交（READ UNCOMMITTED） #

读已提交（READ COMMITTED） #

可重复读（REPEATABLE READ） #

串行化（SERIALIZABLE） #

并发问题 #

脏读（Dirty Read） #

不可重复读（Non-repeatable Read） #

幻读（Phantom Read） #

事务控制语句 #

SAVEPOINT #

事务注释 #

自动提交模式 #

存储引擎对事务的支持 #

InnoDB（推荐） #

MyISAM #

事务最佳实践 #

1. 事务范围控制 #

2. 错误处理 #

3. 死锁处理 #

事务监控 #

查看当前事务 #

事务统计信息 #

事务与锁 #

行级锁 #

意向锁 #

分布式事务 #

事务性能优化 #

1. 减少事务冲突 #

2. 批量操作 #

事务安全实践 #

1. 数据验证 #

2. 审计日志 #

总结 #