MySQL高级（五）：事务

概念

事务是数据库管理系统中用于保证数据一致性和完整性的重要机制。它允许将一组操作视为一个整体，要么全部执行，要么全部回滚，以确保数据的正确性。

事务的特性（ACID）

原子性（Atomicity）

整个事务是不可分割的操作单元，要么全部成功执行，要么失败后回滚。
例如：转账中，A账户扣款和B账户入账必须同时成功，否则事务回滚。

一致性（Consistency）

事务执行前后，数据库必须保持一致性状态。
例如：银行总余额在转账操作后保持不变。

隔离性（Isolation）

一个事务的执行不应影响其他事务，即使它们同时操作相同的数据。
MySQL 提供不同的隔离级别（见后续内容）。

持久性（Durability）

一旦事务提交，其对数据库的修改应永久保存，即使系统发生崩溃。

事务相关操作

基本命令

启动事务

START TRANSACTION;

提交事务

COMMIT;

回滚事务

ROLLBACK;

查看当前隔离级别

SELECT @@transaction_isolation;

设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

自动提交模式

默认情况下，MySQL 是 自动提交模式。
每个 SQL 语句执行后都会自动提交。
关闭自动提交：

SET AUTOCOMMIT = 0;

事务隔离级别

MySQL 提供四种隔离级别，用于处理并发事务中的数据一致性问题。

隔离级别	描述	可能出现的问题
READ UNCOMMITTED	可以读取未提交的数据（脏读）。	脏读、不可重复读、幻读
READ COMMITTED	只能读取其他事务已提交的数据。	不可重复读、幻读
REPEATABLE READ	保证在一个事务内多次读取相同的数据结果一致（MySQL 默认）。	幻读
SERIALIZABLE	强制事务串行执行，避免所有并发问题，但性能较低。	无

事务的具体案例

案例 1：银行转账

假设有一个账户表 accounts，包含以下字段：

account_id: 账户 ID
balance: 账户余额

需求：从账户 A 转账 100 元到账户 B。

-- 假设账户 A 的 ID 是 1，账户 B 的 ID 是 2
START TRANSACTION;-- 从账户 A 扣除 100 元
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;-- 向账户 B 添加 100 元
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;-- 检查账户 A 的余额是否小于 0
SELECT balance FROM accounts WHERE account_id = 1;-- 如果余额小于 0，回滚事务
IF (balance < 0) THENROLLBACK;
ELSECOMMIT;
END IF;

案例 2：库存管理

假设有一个订单系统，涉及以下两个表：

products：记录商品库存；
orders：记录订单信息。

需求：下单时，检查库存是否充足，如果不足则回滚事务。

-- 假设商品 ID 是 101，用户购买了 2 件商品
START TRANSACTION;-- 检查库存是否足够
SELECT stock FROM products WHERE product_id = 101;-- 假设检查的库存数量保存在变量 @stock
IF (@stock < 2) THENROLLBACK; -- 库存不足，回滚
ELSE-- 扣减库存UPDATE products SET stock = stock - 2 WHERE product_id = 101;-- 插入订单记录INSERT INTO orders (product_id, quantity) VALUES (101, 2);-- 提交事务COMMIT;
END IF;

事务的常见问题

死锁问题
- 当两个事务互相等待对方释放资源时，会发生死锁。
- MySQL 会自动检测死锁，并回滚其中一个事务。
长事务
- 长时间运行的事务可能锁定资源，影响系统性能。
- 优化方法：
  - 尽量缩短事务的生命周期。
  - 提交或回滚后立即释放锁。
锁的粒度
- InnoDB 支持行级锁，但在某些情况下会升级为表锁（如未使用索引的查询）。
读写分离
- 在高并发场景下，可以通过主从复制实现读写分离，提升性能。

事务优化

在 MySQL 中，优化事务的使用对提高性能和减少资源占用至关重要。以下是 MySQL 事务优化的一些常见方法和策略：

1. 减少事务的生命周期

尽量缩短事务的执行时间
- 确保事务内的逻辑尽量简单，避免不必要的计算或等待操作。
- 在事务中尽量避免用户交互或外部依赖（如网络调用），以减少事务锁的持有时间。
提前准备数据
- 在开始事务之前，准备好所需的查询条件和数据，减少事务内的操作时间。
及时提交或回滚事务
- 当事务完成或检测到错误时，立即执行 COMMIT 或 ROLLBACK 以释放资源。

2. 减少锁的粒度

使用合适的索引
- 确保 WHERE 条件中的列有索引，以减少锁定的行数。
- 避免全表扫描，减少锁从行级锁升级为表锁的风险。
控制事务锁的范围
- 只操作必要的行或表，避免不必要的锁定。
分批操作
- 对大批量数据的修改操作，可以分批次执行，减少单个事务中的锁资源占用。

3. 选择合适的事务隔离级别

不同的隔离级别对事务性能和一致性要求有不同的影响：
- READ COMMITTED 和 REPEATABLE READ 是较常用的隔离级别，通常能在一致性和性能之间取得平衡。
- SERIALIZABLE 隔离级别提供最高的数据一致性，但性能开销最大，应尽量避免在高并发场景中使用。
- 根据业务需求，选择最合适的隔离级别，避免不必要的性能损耗。

4. 减少并发事务冲突

读写分离
- 使用主从复制将读操作分流到从库，减少主库的写入压力。
- 确保事务中的写操作只发生在主库。
分区表设计
- 将数据分布在多个分区中，减少并发事务对同一表的争用。
避免热点更新
- 在高并发场景中，减少对同一行或少数行频繁更新的情况。

5. 避免死锁

固定资源访问顺序
- 在事务中以固定的顺序访问表或行，减少死锁发生的概率。
分解长事务
- 将一个长事务拆分成多个短事务，避免长时间锁定资源。
及时处理冲突
- 捕获死锁异常，并重新尝试事务逻辑。

6. 使用批量操作

批量插入、更新、删除
- 避免逐条执行操作，尽可能使用批量语句提高效率，例如：
```
INSERT INTO table_name (col1, col2) VALUES (1, 2), (3, 4), (5, 6);
```
分块提交
- 对于需要修改大量数据的事务，将其拆分成多个小事务，以减轻单次事务的压力。

7. 合理配置 MySQL 参数

innodb_lock_wait_timeout
- 设置事务等待锁的超时时间，避免事务长时间占用资源。
```
SET innodb_lock_wait_timeout = 50;
```
innodb_flush_log_at_trx_commit
- 配置日志刷新策略，可以平衡性能和数据持久性：
  - 值为 1（默认值）：事务提交时强制刷盘，保证持久性。
  - 值为 2：减少刷盘频率，性能更高但持久性稍弱。
innodb_buffer_pool_size
- 提高 InnoDB 缓冲池大小，以减少磁盘 IO，提高性能。
binlog_format
- 选择合适的二进制日志格式（STATEMENT、ROW 或 MIXED），根据事务内容优化日志性能。

8. 避免不必要的事务

对于只读操作，不需要使用事务。例如：
```
SELECT ...;
```
对于非关键性数据写入，可以采用异步写入或消息队列来减少事务压力。

9. 使用更高效的 SQL 语句

避免 SELECT FOR UPDATE 误用
- 只在确实需要锁定行时使用 SELECT FOR UPDATE，否则使用普通的 SELECT。
优化 DML 语句
- 确保 UPDATE 和 DELETE 的条件明确，减少无效锁。

10. 监控和调试事务性能

使用性能工具
- 使用 MySQL 提供的性能工具（如 SHOW ENGINE INNODB STATUS）来检查事务锁和死锁信息。
启用慢查询日志
- 检查长时间运行的事务，并针对这些事务进行优化。
分析事务的行锁等待
- 通过 INFORMATION_SCHEMA.INNODB_LOCKS 和 INNODB_LOCK_WAITS 查看锁冲突信息。