事务

事务（Transaction）是数据库管理系统中保证数据一致性的重要机制。本章将详细介绍MySQL事务的概念、特性、使用方法以及锁机制。

什么是事务

事务是一组数据库操作的逻辑单元，这些操作要么全部成功执行，要么全部不执行。事务是保证数据一致性的重要手段。

事务的ACID特性

sql

-- ACID是事务的四个核心特性：

-- A - Atomicity（原子性）
-- 事务中的所有操作要么全部完成，要么全部不完成
-- 不会停留在中间状态

-- C - Consistency（一致性）
-- 事务执行前后，数据库从一个一致状态转换到另一个一致状态
-- 数据完整性约束不会被破坏

-- I - Isolation（隔离性）
-- 多个事务并发执行时，每个事务都感觉不到其他事务的存在
-- 事务之间相互隔离

-- D - Durability（持久性）
-- 事务一旦提交，对数据的修改就是永久的
-- 即使系统崩溃也不会丢失

事务的应用场景

sql

-- 典型应用场景：

-- 1. 银行转账
--    - 从账户A扣款
--    - 向账户B加款
--    这两个操作必须同时成功或同时失败

-- 2. 订单处理
--    - 创建订单
--    - 扣减库存
--    - 扣除用户余额
--    - 生成物流记录

-- 3. 数据迁移
--    - 删除旧数据
--    - 插入新数据
--    必须保证数据完整性

事务的基本操作

开启事务

sql

-- 方式一：使用START TRANSACTION（推荐）
START TRANSACTION;

-- 方式二：使用BEGIN
BEGIN;

-- 方式三：关闭自动提交模式
SET autocommit = 0;  -- 关闭自动提交
-- 执行SQL语句...
-- 需要手动COMMIT或ROLLBACK

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

提交事务

sql

-- 准备示例表
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    balance DECIMAL(10, 2) DEFAULT 0
);

-- 插入测试数据
INSERT INTO accounts (name, balance) VALUES 
('张三', 1000.00),
('李四', 500.00);

-- 银行转账示例
START TRANSACTION;  -- 开启事务

-- 从张三账户扣款500
UPDATE accounts SET balance = balance - 500 WHERE name = '张三';

-- 向李四账户加款500
UPDATE accounts SET balance = balance + 500 WHERE name = '李四';

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

-- 查看结果
SELECT * FROM accounts;
-- 张三: 500, 李四: 1000

回滚事务

sql

-- 回滚事务：撤销未提交的修改

-- 示例：模拟转账失败
START TRANSACTION;

-- 从张三账户扣款
UPDATE accounts SET balance = balance - 500 WHERE name = '张三';

-- 假设发现李四账户不存在或出现错误
-- 执行回滚
ROLLBACK;  -- 撤销所有未提交的修改

-- 查看结果，张三的余额没有变化
SELECT * FROM accounts;

使用保存点

sql

-- 保存点（SAVEPOINT）允许在事务中设置回滚点
-- 可以回滚到指定保存点，而不必回滚整个事务

START TRANSACTION;

-- 操作1：插入一条记录
INSERT INTO accounts (name, balance) VALUES ('王五', 1000);

-- 设置保存点
SAVEPOINT sp1;

-- 操作2：更新张三余额
UPDATE accounts SET balance = balance + 100 WHERE name = '张三';

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

-- 操作3：更新李四余额
UPDATE accounts SET balance = balance + 100 WHERE name = '李四';

-- 回滚到保存点sp2
ROLLBACK TO SAVEPOINT sp2;
-- 此时操作3被撤销，操作1和操作2仍然有效

-- 回滚到保存点sp1
ROLLBACK TO SAVEPOINT sp1;
-- 此时操作2和操作3都被撤销，只有操作1有效

-- 释放保存点
RELEASE SAVEPOINT sp1;

-- 提交事务
COMMIT;

事务隔离级别

隔离级别介绍

sql

-- MySQL支持四种隔离级别：

-- 1. READ UNCOMMITTED（读未提交）
--    可以读取未提交的数据，可能导致脏读
--    隔离级别最低，并发性能最高

-- 2. READ COMMITTED（读已提交）
--    只能读取已提交的数据，避免脏读
--    可能导致不可重复读

-- 3. REPEATABLE READ（可重复读）-- MySQL默认
--    同一事务中多次读取结果相同，避免不可重复读
--    可能导致幻读（MySQL通过MVCC和Next-Key Lock解决）

-- 4. SERIALIZABLE（串行化）
--    最高隔离级别，完全串行执行
--    避免所有并发问题，但性能最低

查看和设置隔离级别

sql

-- 查看当前隔离级别
SELECT @@transaction_isolation;  -- MySQL 8.0+
SELECT @@tx_isolation;           -- MySQL 5.7及以下

-- 查看全局隔离级别
SELECT @@global.transaction_isolation;

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

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

-- 在事务开始时设置隔离级别
START TRANSACTION ISOLATION LEVEL READ COMMITTED;

脏读演示

sql

-- 脏读：读取到其他事务未提交的数据

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

-- 会话1：开启事务并修改数据但不提交
START TRANSACTION;
UPDATE accounts SET balance = 2000 WHERE name = '张三';
-- 不提交，保持事务开启

-- 会话2：读取数据（在另一个连接中执行）
SELECT * FROM accounts WHERE name = '张三';
-- 可以读取到未提交的修改（balance = 2000）
-- 这就是脏读

-- 会话1：回滚事务
ROLLBACK;

-- 会话2再次读取
SELECT * FROM accounts WHERE name = '张三';
-- 数据恢复原值，之前读取的是"脏数据"

不可重复读演示

sql

-- 不可重复读：同一事务中两次读取结果不同（针对UPDATE）

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

-- 会话1：开启事务并查询
START TRANSACTION;
SELECT balance FROM accounts WHERE name = '张三';  -- 结果：500

-- 会话2：修改并提交（在另一个连接中执行）
START TRANSACTION;
UPDATE accounts SET balance = 1500 WHERE name = '张三';
COMMIT;

-- 会话1：再次查询
SELECT balance FROM accounts WHERE name = '张三';  -- 结果：1500
-- 同一事务中两次查询结果不同，这就是不可重复读

COMMIT;

幻读演示

sql

-- 幻读：同一事务中两次读取的行数不同（针对INSERT/DELETE）

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

-- 会话1：查询所有余额大于800的账户
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 800;  -- 假设返回2条记录

-- 会话2：插入一条新记录（在另一个连接中执行）
START TRANSACTION;
INSERT INTO accounts (name, balance) VALUES ('赵六', 900);
COMMIT;

-- 会话1：再次查询
SELECT * FROM accounts WHERE balance > 800;  -- 返回3条记录
-- 多出了一条"幻影"记录，这就是幻读

COMMIT;

-- 注意：MySQL的REPEATABLE READ通过MVCC和Next-Key Lock
-- 在很大程度上避免了幻读问题

隔离级别对比

sql

-- 隔离级别对比表：
-- +------------------+--------+--------------+--------+
-- | 隔离级别         | 脏读   | 不可重复读   | 幻读   |
-- +------------------+--------+--------------+--------+
-- | READ UNCOMMITTED| 可能   | 可能         | 可能   |
-- | READ COMMITTED  | 不可能 | 可能         | 可能   |
-- | REPEATABLE READ | 不可能 | 不可能       | 可能*  |
-- | SERIALIZABLE    | 不可能 | 不可能       | 不可能 |
-- +------------------+--------+--------------+--------+
-- * MySQL的REPEATABLE READ通过MVCC和间隙锁基本解决了幻读

-- 各隔离级别的锁机制：
-- READ UNCOMMITTED：读不加锁，写加行锁
-- READ COMMITTED：读加MVCC快照，写加行锁
-- REPEATABLE READ：读加MVCC快照，写加间隙锁
-- SERIALIZABLE：读写都加锁

锁机制

锁的类型

sql

-- MySQL中的锁类型：

-- 1. 表级锁（Table Lock）
--    - 锁定整张表
--    - 开销小，加锁快
--    - 并发度低
--    - MyISAM和InnoDB都支持

-- 2. 行级锁（Row Lock）
--    - 只锁定被操作的行
--    - 开销大，加锁慢
--    - 并发度高
--    - 只有InnoDB支持

-- 3. 页面锁（Page Lock）
--    - 锁定一组相邻的行
--    - 开销和并发度介于表锁和行锁之间
--    - BDB存储引擎支持

共享锁和排他锁

sql

-- 共享锁（Shared Lock，S锁）
-- 允许多个事务同时读取同一资源
-- 读取时使用，也称为读锁

-- 排他锁（Exclusive Lock，X锁）
-- 只允许一个事务修改资源
-- 写入时使用，也称为写锁

-- 锁的兼容性：
-- +-------+-------+-------+
-- |       | S锁   | X锁   |
-- +-------+-------+-------+
-- | S锁   | 兼容  | 冲突  |
-- | X锁   | 冲突  | 冲突  |
-- +-------+-------+-------+

-- 加共享锁（读锁）
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0+ 新语法
SELECT * FROM accounts WHERE id = 1 FOR SHARE;

-- 加排他锁（写锁）
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

表级锁

sql

-- 表级锁分为：表共享读锁和表独占写锁

-- 加表锁
LOCK TABLES accounts READ;  -- 表共享读锁
-- 其他会话可以读，但不能写
-- 当前会话可以读，不能写，也不能访问其他表

LOCK TABLES accounts WRITE;  -- 表独占写锁
-- 其他会话不能读也不能写
-- 当前会话可以读写

-- 解锁表
UNLOCK TABLES;

-- 示例：使用表锁进行数据迁移
LOCK TABLES accounts WRITE, accounts_backup WRITE;

INSERT INTO accounts_backup SELECT * FROM accounts;

UNLOCK TABLES;

行级锁

sql

-- InnoDB行级锁类型：

-- 1. Record Lock：记录锁，锁定单条索引记录
-- 2. Gap Lock：间隙锁，锁定索引记录之间的间隙
-- 3. Next-Key Lock：记录锁+间隙锁，锁定记录及前面的间隙

-- 行级锁示例

-- 创建测试表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    stock INT,
    INDEX idx_name (name)
);

INSERT INTO products (name, stock) VALUES 
('iPhone', 100),
('iPad', 50),
('MacBook', 30);

-- 会话1：使用FOR UPDATE锁定行
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- 此时id=1的行被锁定

-- 会话2：尝试修改被锁定的行（会阻塞）
-- UPDATE products SET stock = stock - 1 WHERE id = 1;
-- 等待会话1释放锁...

-- 会话1：提交事务
COMMIT;
-- 会话2现在可以执行了

间隙锁

sql

-- 间隙锁：锁定索引记录之间的间隙，防止幻读

-- 示例表数据：id为1, 3, 5
-- 间隙：(负无穷,1), (1,3), (3,5), (5,正无穷)

-- 会话1：查询id>2的记录并加锁
START TRANSACTION;
SELECT * FROM products WHERE id > 2 FOR UPDATE;
-- 锁定id=3和id=5的记录，以及间隙(2,3), (3,5), (5,正无穷)

-- 会话2：尝试在间隙中插入数据（会阻塞）
-- INSERT INTO products (name, stock) VALUES ('iWatch', 20);
-- 因为间隙被锁定，无法插入

-- 会话1：提交
COMMIT;
-- 会话2现在可以执行了

-- 注意：间隙锁只在REPEATABLE READ隔离级别下生效

意向锁

sql

-- 意向锁是表级锁，用于表示事务打算在表中的行上加什么类型的锁

-- 意向锁类型：
-- IS（意向共享锁）：事务打算在行上加共享锁
-- IX（意向排他锁）：事务打算在行上加排他锁

-- 意向锁的作用：
-- 当事务想获取表锁时，只需要检查意向锁
-- 而不需要检查每一行的锁

-- 意向锁兼容性：
-- +----+----+----+----+
-- |    | IS | IX | S  | X  |
-- +----+----+----+----+----+
-- | IS | Y  | Y  | Y  | N  |
-- | IX | Y  | Y  | N  | N  |
-- | S  | Y  | N  | Y  | N  |
-- | X  | N  | N  | N  | N  |
-- +----+----+----+----+----+

-- 查看锁信息（MySQL 8.0+）
SELECT * FROM performance_schema.data_locks;

-- 查看锁等待（MySQL 8.0+）
SELECT * FROM performance_schema.data_lock_waits;

死锁

死锁的产生

sql

-- 死锁：两个或多个事务互相等待对方释放锁，导致都无法继续执行

-- 示例：两个事务互相等待

-- 会话1：
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 持有id=1的行锁

-- 会话2：
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 2;
-- 持有id=2的行锁

-- 会话1继续：
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- 等待id=2的行锁...

-- 会话2继续：
UPDATE accounts SET balance = balance + 100 WHERE id = 1;
-- 等待id=1的行锁...
-- 死锁产生！

死锁检测和处理

sql

-- MySQL会自动检测死锁，并回滚其中一个事务

-- 查看死锁检测设置
SELECT @@innodb_deadlock_detect;  -- 默认ON

-- 查看最近的死锁信息
SHOW ENGINE INNODB STATUS;
-- 在LATEST DETECTED DEADLOCK部分查看死锁详情

-- 查看锁等待超时时间
SELECT @@innodb_lock_wait_timeout;  -- 默认50秒

-- 设置锁等待超时时间
SET innodb_lock_wait_timeout = 30;

避免死锁的方法

sql

-- 1. 按固定顺序访问表和行

-- 不好的做法：不同事务按不同顺序访问
-- 事务1：先访问表A，再访问表B
-- 事务2：先访问表B，再访问表A

-- 好的做法：所有事务按相同顺序访问
-- 事务1和事务2都：先访问表A，再访问表B

-- 2. 大事务拆分为小事务
-- 减少锁持有时间

-- 3. 尽量使用索引访问数据
-- 避免表锁

-- 4. 合理设计索引
-- 减少锁的范围

-- 5. 在业务层面处理死锁
-- 检测到死锁后重试

DELIMITER //
CREATE PROCEDURE SafeTransfer(
    IN from_id INT,
    IN to_id INT,
    IN amount DECIMAL(10,2),
    OUT result VARCHAR(100)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET result = '转账失败，请重试';
    END;
    
    -- 按ID顺序锁定，避免死锁
    IF from_id < to_id THEN
        START TRANSACTION;
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
        COMMIT;
    ELSE
        START TRANSACTION;
        UPDATE accounts SET balance = balance + amount WHERE id = to_id;
        UPDATE accounts SET balance = balance - amount WHERE id = from_id;
        COMMIT;
    END IF;
    
    SET result = '转账成功';
END //
DELIMITER ;

事务最佳实践

合理设置隔离级别

sql

-- 根据业务需求选择合适的隔离级别：

-- 1. 大多数应用使用READ COMMITTED或REPEATABLE READ即可
-- 2. 对一致性要求不高但性能要求高的场景，可以使用READ UNCOMMITTED
-- 3. 对一致性要求极高的场景（如金融），使用SERIALIZABLE

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

-- 推荐配置（my.cnf）
-- transaction-isolation = REPEATABLE-READ

控制事务大小

sql

-- 不推荐：大事务
START TRANSACTION;
-- 大量操作...
-- 长时间持有锁，影响并发
COMMIT;

-- 推荐：小事务
-- 分批处理
START TRANSACTION;
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
COMMIT;

START TRANSACTION;
DELETE FROM logs WHERE created_at < '2024-01-01' LIMIT 1000;
COMMIT;
-- ...继续分批删除

避免长事务

sql

-- 长事务的问题：
-- 1. 长时间持有锁，影响并发
-- 2. 占用undo log空间
-- 3. 容易导致死锁
-- 4. 主从延迟

-- 监控长事务
SELECT * FROM information_schema.innodb_trx 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
-- 查询运行超过60秒的事务

-- 设置事务超时（MySQL 8.0+）
SET SESSION max_execution_time = 30000;  -- 30秒超时

正确使用锁

sql

-- 1. 明确需要加锁的场景

-- 库存扣减场景：需要加锁防止超卖
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;  -- 加锁
-- 检查库存
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- 2. 避免不必要的锁

-- 只读查询不需要加锁
SELECT * FROM products WHERE id = 1;  -- 不加锁，使用MVCC

-- 3. 使用合适的锁类型

-- 共享锁：多个事务可以同时读取
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁：只有一个事务可以修改
SELECT * FROM products WHERE id = 1 FOR UPDATE;

本章小结

本章详细介绍了MySQL事务的相关知识：

事务基础：理解事务的概念和ACID特性
事务操作：掌握START TRANSACTION、COMMIT、ROLLBACK的使用
保存点：学会使用SAVEPOINT实现部分回滚
隔离级别：理解四种隔离级别及其解决的问题
锁机制：掌握共享锁、排他锁、行锁、表锁、间隙锁等概念
死锁：了解死锁的产生原因和处理方法
最佳实践：学会合理使用事务，避免常见问题

事务是保证数据一致性的核心机制，正确理解和使用事务对于开发高质量的数据库应用至关重要。下一章我们将学习用户权限管理的相关知识。

事务 ​

什么是事务 ​

事务的ACID特性 ​

事务的应用场景 ​

事务的基本操作 ​

开启事务 ​

提交事务 ​

回滚事务 ​

使用保存点 ​

事务隔离级别 ​

隔离级别介绍 ​

查看和设置隔离级别 ​

脏读演示 ​

不可重复读演示 ​

幻读演示 ​

隔离级别对比 ​

锁机制 ​

锁的类型 ​

共享锁和排他锁 ​

表级锁 ​

行级锁 ​

间隙锁 ​

意向锁 ​

死锁 ​

死锁的产生 ​

死锁检测和处理 ​

避免死锁的方法 ​

事务最佳实践 ​

合理设置隔离级别 ​

控制事务大小 ​

避免长事务 ​

正确使用锁 ​

本章小结 ​

事务