MySQL 事务详解
什么是事务？
事务(Transaction)是数据库操作的最小工作单元，是一组不可分割的SQL操作序列，要么全部执行成功，要么全部回滚。
事务的特性
- 原子性(Atomicity)：事务是不可分割的工作单位，事务中的操作要么全部成功，要么全部失败回滚
- 一致性(Consistency)：事务执行前后，数据库从一个一致状态变到另一个一致状态
- 隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务
- 持久性(Durability)：事务一旦提交，其结果就是永久性的
MySQL事务操作语法
事务处理命令
- begin：开始一个事务
- commit：提交一个事务
- rollback：回滚一个事务（撤销）
事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 说明 |
|---|---|---|---|---|
| READ UNCOMMITTED | 可能 | 可能 | 可能 | 最低隔离级别 |
| READ COMMITTED | 不可能 | 可能 | 可能 | 保证不读取未提交数据 |
| REPEATABLE READ | 不可能 | 不可能 | 可能 | MySQL默认级别 |
| SERIALIZABLE | 不可能 | 不可能 | 不可能 | 最高隔离级别 |
存储引擎的事务支持
| 存储引擎 | 事务支持 | ACID特性 | 说明 |
|---|---|---|---|
| InnoDB | 完全支持 | 完整ACID | 默认引擎 |
| MyISAM | 不支持 | 无 | 表级锁定 |
| MEMORY | 不支持 | 无 | 内存表 |
| NDB | 支持 | 部分ACID | 集群引擎 |
基本事务控制语句
sql
-- 开始事务(三种方式等效)
START TRANSACTION;
-- 或
BEGIN;
-- 或
BEGIN WORK;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;事务参数配置
事务隔离级别配置
ini
# 在my.cnf/my.ini中设置默认隔离级别
[mysqld]
transaction-isolation = REPEATABLE-READ # 可选: READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE自动提交模式
ini
# 控制单条SQL是否自动提交(默认为1)
[mysqld]
autocommit = 1 # 1-启用(默认), 0-禁用InnoDB事务日志配置
ini
# 事务日志(redo log)配置
innodb_log_file_size = 1G # 每个日志文件大小(建议1-2GB)
innodb_log_files_in_group = 2 # 日志文件数量(通常2-4个)
innodb_log_buffer_size = 16M # 日志缓冲区大小
innodb_flush_log_at_trx_commit = 1 # 关键参数！控制日志刷新策略innodb_flush_log_at_trx_commit参数详解：
1（默认）：每次事务提交都刷新日志到磁盘，完全ACID合规0：每秒写入日志并刷新到磁盘，性能最高但可能丢失1秒数据2：每次事务提交写入日志缓存，但每秒刷新到磁盘
事务性能优化配置
并发事务控制
ini
# 最大并发事务数
innodb_thread_concurrency = 0 # 0表示无限制(默认)
innodb_concurrency_tickets = 5000 # 每个连接可执行的事务数
# 回滚段数量(MySQL 8.0+)
innodb_rollback_segments = 128 # 默认128大事务优化
ini
# 大事务内存分配
innodb_sort_buffer_size = 1M # 排序缓冲区
innodb_online_alter_log_max_size = 128M # 在线DDL日志大小只读事务优化
ini
# 启用只读事务优化
transaction_read_only = 0 # 1-启用, 0-禁用(默认)监控与诊断配置
ini
# 启用性能模式的事务监控
performance_schema = ON
performance_schema_consumer_events_transactions_current = ON
performance_schema_consumer_events_transactions_history = ON
# 记录执行时间超过N秒的事务
long_query_time = 2 # 秒
log_slow_admin_statements = ON # 记录慢管理语句
log_queries_not_using_indexes = ON # 记录未使用索引的查询事务状态检查
查看当前事务状态
sql
-- 查看运行中的事务
SELECT * FROM information_schema.innodb_trx;
-- 查看事务隔离级别
SELECT @@transaction_isolation;
-- 查看锁等待情况
SELECT * FROM sys.innodb_lock_waits;性能监控
sql
-- 查看事务统计
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT/1000000000 AS sec
FROM performance_schema.events_transactions_summary_global_by_event_name;
-- 查看长事务
SELECT * FROM information_schema.innodb_trx
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;MySQL 数据库语言的事务特点
各类SQL语言概述
| 语言类型 | 全称 | 主要功能 | 常用命令 |
|---|---|---|---|
| DDL | Data Definition Language | 定义和管理数据库对象 | CREATE, ALTER, DROP, TRUNCATE, RENAME |
| DML | Data Manipulation Language | 操作数据库中的数据 | INSERT, UPDATE, DELETE, MERGE |
| DCL | Data Control Language | 控制数据库访问权限 | GRANT, REVOKE |
| DQL | Data Query Language | 查询数据库数据 | SELECT |
各类语言的事务特点
DDL (数据定义语言) 的事务特点
自动提交：DDL语句执行后会隐式提交当前事务（InnoDB引擎）
不可回滚：执行后无法通过ROLLBACK撤销
元数据锁：执行时会获取元数据锁，阻塞其他会话的DDL操作
示例：
sqlSTART TRANSACTION; INSERT INTO users VALUES(1, 'John'); -- DML，可回滚 CREATE TABLE orders (id INT); -- DDL，会自动提交事务 ROLLBACK; -- 只能回滚DDL之后的操作
DML (数据操纵语言) 的事务特点
显式事务控制：需要显式使用START TRANSACTION/BEGIN开启事务
可回滚：在事务内执行的操作可以通过ROLLBACK撤销
行级锁：InnoDB会对受影响的行加锁
支持ACID：
- 原子性(Atomicity)：要么全部执行，要么全部回滚
- 一致性(Consistency)：保持数据一致状态
- 隔离性(Isolation)：事务间相互隔离
- 持久性(Durability)：提交后永久生效
示例：
sqlSTART TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; COMMIT; -- 或 ROLLBACK
DCL (数据控制语言) 的事务特点
隐式提交：执行后会提交当前事务
权限操作不可逆：权限变更立即生效，无法通过事务回滚
全局影响：权限变更会影响所有后续连接
示例：
sqlSTART TRANSACTION; INSERT INTO logs VALUES('operation started'); -- DML GRANT SELECT ON db.* TO 'user'@'host'; -- DCL，会提交事务 ROLLBACK; -- 只能回滚DCL之后的操作
DQL (数据查询语言) 的事务特点
不影响事务状态：SELECT查询不会提交或回滚事务
隔离级别影响：查询结果受事务隔离级别影响
可设置快照：在REPEATABLE READ级别下，第一次SELECT会建立一致性视图
锁选项：可通过FOR UPDATE/SHARE加锁
示例：
sqlSTART TRANSACTION; SELECT * FROM products WHERE stock > 0; -- 不影响事务 UPDATE products SET stock = stock - 1 WHERE id = 10; COMMIT;
事务使用建议
- 避免在事务中混用DDL/DCL：会导致意外提交
- 合理设置隔离级别：根据业务需求选择READ COMMITTED或REPEATABLE READ
- 控制事务大小：大事务会导致锁持有时间过长
- 明确提交或回滚：避免长时间未完成的事务
- 使用保存点(SAVEPOINT)：实现部分回滚
sql
START TRANSACTION;
INSERT INTO table1 VALUES(1);
SAVEPOINT sp1;
INSERT INTO table1 VALUES(2);
ROLLBACK TO sp1; -- 只回滚第二条INSERT
COMMIT;