MySQL 事务与锁

事务与锁是MySQL并发控制的核心机制，直接影响数据库的性能和数据一致性。本文从DBA视角，详细介绍事务管理、锁机制、死锁处理和性能优化。

事务管理

事务的ACID特性

事务必须满足ACID特性，DBA需要理解每个特性的实现机制：

特性	描述	实现机制	DBA关注点
原子性（Atomicity）	要么全部成功，要么全部失败	Undo Log	监控undo log使用情况
一致性（Consistency）	从一个一致状态到另一个一致状态	约束检查、触发器、应用逻辑	关注数据完整性约束
隔离性（Isolation）	并发事务互不干扰	MVCC、锁机制	选择合适的隔离级别
持久性（Durability）	提交后永久保存	Redo Log、双写缓冲	配置合适的日志刷新策略

事务控制语句

sql

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

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 保存点（复杂事务中使用）
SAVEPOINT sp1;
-- 回滚到保存点
ROLLBACK TO sp1;
-- 删除保存点
RELEASE SAVEPOINT sp1;

-- 只读事务（减少锁开销）
START TRANSACTION READ ONLY;

事务隔离级别

MySQL支持四种隔离级别，DBA需要根据业务需求选择合适的级别：

隔离级别	脏读	不可重复读	幻读	并发性能	适用场景
读未提交	✅	✅	✅	最高	极少使用
读已提交	❌	✅	✅	高	互联网应用
可重复读	❌	❌	✅	中	默认级别
串行化	❌	❌	❌	最低	金融核心业务

隔离级别配置与查看

sql

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

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

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

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

MVCC机制详解

MVCC（多版本并发控制）是InnoDB实现高并发的核心：

核心原理：为每行记录保存多个版本，通过版本号控制并发访问
隐藏列：
- DB_TRX_ID：最后修改该行的事务ID
- DB_ROLL_PTR：指向undo log的指针
- DB_ROW_ID：行ID（无主键时生成）
Read View：
- 快照读（SELECT）：读取创建Read View时的快照数据
- 当前读（SELECT ... FOR UPDATE）：读取最新数据并加锁
Undo Log：
- 记录数据的历史版本
- 用于事务回滚和MVCC查询
- 分为insert、update、delete三种类型

事务相关配置优化

ini

# 事务超时时间
innodb_lock_wait_timeout = 50

# 自动提交设置
autocommit = 1  # 默认开启，建议保持

# Redo Log配置（影响持久性）
innodb_flush_log_at_trx_commit = 1  # 1=最安全，2=性能优先，0=最快但最不安全
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

# Undo Log配置
innodb_undo_tablespaces = 2
innodb_undo_log_truncate = ON
innodb_max_undo_log_size = 1G

# 自动增长锁模式
innodb_autoinc_lock_mode = 2  # 2=交错模式，性能最高

锁机制

锁的分类

按粒度分类

锁类型	粒度	并发度	适用场景
行级锁	单行	高	高并发OLTP
页级锁	16KB数据页	中	极少使用
表级锁	整个表	低	批量更新、全表扫描

按类型分类

锁类型	描述	兼容关系	适用场景
共享锁（S）	读锁，多个事务可同时持有	与S兼容，与X不兼容	并发读操作
排他锁（X）	写锁，同一时间只能一个事务持有	与任何锁不兼容	数据修改操作
意向共享锁（IS）	表级锁，表示事务即将加S锁	与IS/IX兼容	内部使用
意向排他锁（IX）	表级锁，表示事务即将加X锁	与IS/IX兼容	内部使用

InnoDB锁算法

锁算法	描述	适用场景
Record Lock	锁定单行记录	精确匹配主键或唯一索引
Gap Lock	锁定索引间隙	防止幻读，RR及以上隔离级别生效
Next-Key Lock	Record Lock + Gap Lock	InnoDB默认行锁算法
Insert Intention Lock	插入间隙锁	并发插入不同位置的数据

锁的监控与查看

1. 实时监控锁状态

sql

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

-- 查看锁等待
SELECT * FROM information_schema.innodb_lock_waits;

-- 查看锁详情
SELECT * FROM information_schema.innodb_locks;

-- 查看InnoDB状态（包含死锁信息）
SHOW ENGINE INNODB STATUSG

-- 使用performance_schema监控锁
SELECT 
  b.trx_mysql_thread_id AS blocked_thread,
  a.requesting_trx_id AS requesting_trx,
  a.blocking_trx_id AS blocking_trx,
  a.requested_lock_type AS requested_lock,
  b.trx_query AS blocked_query
FROM performance_schema.data_lock_waits a
JOIN information_schema.innodb_trx b ON a.blocking_trx_id = b.trx_id;

2. 锁相关状态变量

sql

-- 查看锁相关状态
SHOW GLOBAL STATUS LIKE '%lock%';

-- 重点关注
-- Innodb_row_lock_current_waits: 当前等待的行锁数量
-- Innodb_row_lock_time: 行锁总等待时间
-- Innodb_row_lock_time_avg: 平均等待时间
-- Innodb_row_lock_waits: 行锁总等待次数

锁等待分析脚本

bash

#!/bin/bash
# 锁等待分析脚本
mysql -u root -p << EOF
SET @now = NOW();
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,
  TIMESTAMPDIFF(SECOND, r.trx_started, @now) wait_time_seconds
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id
ORDER BY wait_time_seconds DESC;
EOF

死锁处理

死锁的产生

死锁是指两个或多个事务相互等待对方持有的锁，导致无限等待的状态。

死锁示例：

时间	事务A	事务B
T1	BEGIN; UPDATE t SET col1=1 WHERE id=1;	BEGIN; UPDATE t SET col2=2 WHERE id=2;
T2	UPDATE t SET col2=1 WHERE id=2;	UPDATE t SET col1=2 WHERE id=1;
T3	等待事务B释放id=2的锁	等待事务A释放id=1的锁
T4	死锁发生	死锁发生

死锁的检测与处理

自动检测：InnoDB默认开启死锁检测（innodb_deadlock_detect=ON）
处理方式：回滚持有锁最少的事务

查看死锁日志：

sql

SHOW ENGINE INNODB STATUS LIKE 'LATEST DETECTED DEADLOCK';

死锁的预防与避免

统一锁顺序：所有事务按相同顺序访问资源

sql

-- 好：统一先更新id=1，再更新id=2
UPDATE t SET col1=1 WHERE id=1;
UPDATE t SET col2=2 WHERE id=2;

减少事务大小：将大事务拆分为小事务

sql

-- 避免：在一个事务中处理大量数据
-- 建议：分批处理，每批提交一次

缩短锁持有时间：尽快提交或回滚事务

sql

-- 避免：在事务中执行非数据库操作
-- 建议：先处理业务逻辑，再开启事务

使用索引：确保更新语句使用索引，避免全表锁

sql

-- 好：使用索引更新
UPDATE t SET col1=1 WHERE id=1;  -- id为主键

-- 不好：无索引，导致表锁
UPDATE t SET col1=1 WHERE name='test';  -- name无索引

调整隔离级别：在允许的情况下使用较低的隔离级别

sql

-- 例如：将隔离级别从REPEATABLE READ调整为READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

使用乐观锁：适合并发冲突较少的场景

sql

-- 乐观锁示例
UPDATE t SET col1=value, version=version+1 WHERE id=? AND version=?;

死锁的监控与告警

bash

#!/bin/bash
# 死锁监控脚本
DEADLOCKS=$(mysql -u root -p -e "SHOW ENGINE INNODB STATUS LIKE 'LATEST DETECTED DEADLOCK'" | grep -A 20 "LATEST DETECTED DEADLOCK")

if [ -n "$DEADLOCKS" ]; then
  echo "检测到死锁：" >> /var/log/mysql/deadlocks.log
  echo "$DEADLOCKS" >> /var/log/mysql/deadlocks.log
  echo "时间：$(date)" >> /var/log/mysql/deadlocks.log
  echo "------------------------" >> /var/log/mysql/deadlocks.log
  
  # 发送告警（示例：邮件）
  mail -s "MySQL死锁告警" dba@company.com <<< "$DEADLOCKS"
fi

MySQL 8.0 新特性

1. 原子DDL

MySQL 8.0支持原子DDL，确保DDL操作要么完全成功，要么完全失败：

sql

-- 原子DDL示例
ALTER TABLE t ADD COLUMN col1 INT, ADD COLUMN col2 VARCHAR(50);
-- 要么两个列都添加成功，要么都失败

2. 锁超时信息增强

MySQL 8.0在错误信息中包含更详细的锁超时信息：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction. Transaction: UPDATE t SET col1=1 WHERE id=1

3. 死锁信息表

MySQL 8.0将死锁信息记录到performance_schema中：

sql

-- 查看历史死锁
SELECT * FROM performance_schema.events_deadlocks;

常见问题与故障排查

1. 长事务处理

症状：事务持有锁时间过长，导致其他事务等待

排查步骤：

sql

-- 查找长事务
SELECT 
  trx_id,
  trx_mysql_thread_id,
  trx_started,
  TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
  trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY duration_seconds DESC;

-- 终止长事务
KILL [thread_id];

2. 锁等待超时

症状：出现"Lock wait timeout exceeded; try restarting transaction"错误

排查步骤：

查看锁等待情况
找出阻塞事务
分析阻塞原因
优化查询或终止阻塞事务

3. 大量锁等待

症状：系统中出现大量锁等待，并发性能下降

排查步骤：

sql

-- 查看锁等待统计
SELECT 
  object_schema,
  object_name,
  index_name,
  count_star AS lock_wait_count,
  sum_timer_wait AS total_wait_time
FROM performance_schema.table_lock_waits_summary_by_index_usage
ORDER BY total_wait_time DESC;

-- 找出热点表
SELECT * FROM sys.schema_table_lock_waits;

4. 事务提交缓慢

症状：事务提交时间过长

可能原因：

磁盘I/O性能差
大量锁竞争
innodb_flush_log_at_trx_commit=1导致频繁刷盘
长事务导致undo log膨胀

解决方案：

使用SSD存储
优化查询，减少锁竞争
考虑调整innodb_flush_log_at_trx_commit为2（性能优先）
定期清理长事务

DBA 最佳实践

1. 事务管理最佳实践

选择合适的隔离级别：根据业务需求平衡一致性和性能
保持事务短小：只包含必要的数据库操作
使用只读事务：对于只读查询，显式使用只读事务
避免在事务中执行非数据库操作：如网络调用、文件I/O等
显式提交或回滚：不要依赖自动提交机制

2. 锁管理最佳实践

使用索引：确保所有WHERE条件都使用索引
避免锁定大量数据：使用精确的WHERE条件
减少锁持有时间：尽快完成数据修改并提交
监控锁等待：定期查看锁等待情况，及时优化
使用乐观锁：对于并发冲突较少的场景
统一锁顺序：所有事务按相同顺序访问资源

3. 死锁处理最佳实践

开启死锁检测：保持innodb_deadlock_detect=ON
监控死锁：定期检查死锁日志，及时分析
调整事务顺序：统一事务访问资源的顺序
拆分大事务：将复杂事务拆分为多个小事务
使用更细粒度的锁：如行级锁替代表级锁

4. 监控与优化

监控长事务：设置阈值，超过则告警
监控锁等待：关注锁等待次数和等待时间
定期分析死锁：找出死锁模式，优化应用逻辑
调整配置：根据业务需求调整事务和锁相关参数
使用性能_schema：深入分析锁和事务性能

总结

事务与锁是MySQL并发控制的核心，DBA需要：

理解事务的ACID特性：掌握其实现机制
选择合适的隔离级别：平衡一致性和性能
掌握锁机制：理解不同类型锁的适用场景
预防和处理死锁：减少死锁发生，及时处理死锁
监控和优化：定期监控锁和事务性能，持续优化

通过合理的事务设计、锁管理和死锁处理，DBA可以构建高性能、高可用的MySQL数据库系统，确保数据一致性的同时提高并发性能。

MySQL 事务与锁 ​

事务管理 ​

事务的ACID特性 ​

事务控制语句 ​

事务隔离级别 ​

隔离级别配置与查看 ​

MVCC机制详解 ​

事务相关配置优化 ​

锁机制 ​

锁的分类 ​

按粒度分类 ​

按类型分类 ​

InnoDB锁算法 ​

锁的监控与查看 ​

1. 实时监控锁状态 ​

2. 锁相关状态变量 ​

锁等待分析脚本 ​

死锁处理 ​

死锁的产生 ​

死锁的检测与处理 ​

死锁的预防与避免 ​

死锁的监控与告警 ​

MySQL 8.0 新特性 ​

1. 原子DDL ​

2. 锁超时信息增强 ​

3. 死锁信息表 ​

常见问题与故障排查 ​

1. 长事务处理 ​

2. 锁等待超时 ​

3. 大量锁等待 ​

4. 事务提交缓慢 ​

DBA 最佳实践 ​

1. 事务管理最佳实践 ​

2. 锁管理最佳实践 ​

3. 死锁处理最佳实践 ​

4. 监控与优化 ​

总结 ​

MySQL 事务与锁

事务管理

事务的ACID特性

事务控制语句

事务隔离级别

隔离级别配置与查看

MVCC机制详解

事务相关配置优化

锁机制

锁的分类

按粒度分类

按类型分类

InnoDB锁算法

锁的监控与查看

1. 实时监控锁状态

2. 锁相关状态变量

锁等待分析脚本

死锁处理

死锁的产生

死锁的检测与处理

死锁的预防与避免

死锁的监控与告警

MySQL 8.0 新特性

1. 原子DDL

2. 锁超时信息增强

3. 死锁信息表

常见问题与故障排查

1. 长事务处理

2. 锁等待超时

3. 大量锁等待

4. 事务提交缓慢

DBA 最佳实践

1. 事务管理最佳实践

2. 锁管理最佳实践

3. 死锁处理最佳实践

4. 监控与优化

总结