【MySQL】第7节｜Mysql锁机制与优化实践以及MVCC底层原理剖析

锁等待分析

我们通过检查InnoDB_row_lock相关的状态变量来分析系统上的行锁的争夺情况

示例场景

假设有两个用户同时操作账户表 accounts（主键为 id）：

1. 用户A：执行转账，锁定账户 id=1 并等待3秒：

BEGIN;
SELECT * FROM accounts WHERE id=1 FOR UPDATE;
-- 模拟业务逻辑耗时3秒
COMMIT;

2. 用户B：几乎同时尝试锁定同一账户： 

BEGIN;
SELECT * FROM accounts WHERE id=1 FOR UPDATE;  -- 被阻塞，等待用户A释放锁
COMMIT;

参数变化说明

1. Innodb_row_lock_current_waits
   • 含义：当前正在等待锁的事务数量。
   • 示例：当用户B被阻塞时，该值为 1；用户A提交后，该值变为 0。
2. Innodb_row_lock_waits
   • 含义：系统启动后锁等待的总次数。
   • 示例：用户B等待一次后，该值增加 1。
3. Innodb_row_lock_time
   • 含义：系统启动后所有锁等待的总时长（毫秒）。
   • 示例：用户B等待3秒（3000毫秒）后，该值增加 3000。
4. Innodb_row_lock_time_avg
   • 含义：每次锁等待的平均时长（毫秒）。
   • 示例：若此前无等待，此次等待后该值为 3000；若已有1次等待（假设为2000毫秒），则平均值为 (2000 + 3000) / 2 = 2500。
5. Innodb_row_lock_time_max
   • 含义：系统启动后最长的单次锁等待时长。
   • 示例：若此前最大值为2500毫秒，此次等待3000毫秒后，该值更新为 3000。

关键指标解读

• 高 Innodb_row_lock_waits：

表示频繁出现锁争用，可能是事务持有锁时间过长或并发度过高。

→ 优化：缩短事务执行时间，避免长事务。

• 高 Innodb_row_lock_time_avg：

表示锁等待时间过长，可能是锁粒度太大或索引缺失导致锁范围扩大。

→ 优化：优化查询语句，确保使用索引减少锁范围。

• 高 Innodb_row_lock_time：

累计等待时间过长，影响整体吞吐量。

→ 优化：调整业务逻辑，减少锁竞争（如批量操作拆分为多次小事务）。

查看方法

SHOW STATUS LIKE 'Innodb_row_lock%';

-- 输出示例：
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 3000  |
| Innodb_row_lock_time_avg      | 3000  |
| Innodb_row_lock_time_max      | 3000  |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+

这些指标是 动态累积值，重启MySQL后会重置为0。通过监控它们的变化趋势，可以定位数据库性能瓶颈。

INFORMATION_SCHEMA系统库锁相关数据表

以下是关于 InnoDB 事务和锁相关系统表的示例说明，结合常见场景演示如何查询和分析数据：

一、查看当前事务（INNODB_TRX / information_schema.INNODB_TRX）

场景：查询当前正在运行的事务

SELECT
  trx_id,               -- 事务ID
  trx_state,            -- 事务状态（RUNNING、LOCK WAIT等）
  trx_started,          -- 事务开始时间
  trx_query,            -- 正在执行的SQL语句（可能为NULL）
  trx_mysql_thread_id   -- 事务对应的线程ID
FROM INFORMATION_SCHEMA.INNODB_TRX;

示例输出：

说明：

• trx_state=LOCK WAIT：表示事务正在等待锁（如示例中线程101在等待锁定id=1的记录）。
• trx_query：若事务未执行具体SQL（如处于空闲状态），则显示为NULL。

二、查看锁信息（INNODB_LOCKS → 8.0+ 改为 performance_schema.data_locks）

场景：查询当前数据库中的行锁

-- MySQL 5.7及之前版本
SELECT
  lock_id,          -- 锁ID
  lock_trx_id,      -- 持有锁的事务ID
  lock_mode,        -- 锁模式（如X锁、S锁、IS锁、IX锁）
  lock_table,       -- 锁定的表名
  lock_index,       -- 锁定的索引（若为行锁，通常为索引名）
  lock_space,       -- 表空间ID
  lock_page,        -- 数据页号
  lock_rec,         -- 数据行号
  lock_data          -- 锁定的行数据（如主键值）
FROM INFORMATION_SCHEMA.INNODB_LOCKS;

-- MySQL 8.0+ 版本（改用 performance_schema.data_locks）
SELECT
  ENGINE_LOCK_ID,    -- 锁ID
  THREAD_ID,         -- 持有锁的线程ID
  LOCK_MODE,         -- 锁模式
  LOCK_TYPE,         -- 锁类型（ROW、TABLE等）
  TABLE_SCHEMA,      -- 表所属数据库
  TABLE_NAME,        -- 表名
  INDEX_NAME,        -- 索引名
  LOCK_DATA           -- 锁定的行数据（如主键值）
FROM performance_schema.data_locks;

示例输出（MySQL 8.0+）：

说明：

• LOCK_MODE=X：表示排他锁（示例中线程102对accounts表id=1的记录加了X锁）。
• LOCK_DATA：行锁通常显示主键值（如1），表锁或意向锁可能显示为NULL。

三、查看锁等待（INNODB_LOCK_WAITS → 8.0+ 改为 performance_schema.data_lock_waits）

场景：查询当前锁等待的阻塞关系

-- MySQL 5.7及之前版本
SELECT
  request_trx_id,     -- 请求锁的事务ID（等待者）
  request_lock_id,    -- 请求的锁ID
  block_trx_id,       -- 持有锁的事务ID（阻塞者）
  block_lock_id       -- 被持有的锁ID
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

-- MySQL 8.0+ 版本（改用 performance_schema.data_lock_waits）
SELECT
  REQUESTING_THREAD_ID,  -- 请求锁的线程ID（等待者）
  BLOCKING_THREAD_ID,    -- 持有锁的线程ID（阻塞者）
  REQUESTED_LOCK_ID,     -- 请求的锁ID
  BLOCKING_LOCK_ID       -- 被持有的锁ID
FROM performance_schema.data_lock_waits;

示例输出：

说明：

• 事务12345（等待者） 请求锁定id=1的记录，但事务12346（阻塞者） 已持有该记录的X锁，导致阻塞。
• 通过此表可快速定位死锁或锁竞争的源头。

四、释放锁（KILL THREAD）

场景：强制终止阻塞事务（需谨慎！）

1. 通过 INNODB_TRX 找到阻塞事务的线程ID：

SELECT trx_mysql_thread_id FROM INFORMATION_SCHEMA.INNODB_TRX WHERE trx_id=12346;  -- 假设阻塞者事务ID为12346
-- 输出：102

1. 终止线程释放锁：

KILL 102;  -- 杀死线程ID为102的事务（需SUPER权限）

注意：

• 直接终止事务可能导致数据不一致或未提交的业务逻辑中断，仅建议在紧急情况下使用（如死锁无法自动解决时）。

五、查看锁等待详细信息（SHOW ENGINE INNODB STATUS）

场景：获取更详细的锁等待日志（包含死锁检测信息）

SHOW ENGINE INNODB STATUS\G

关键输出片段：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2025-05-24 15:35:00 0x7f8a12345678
*** (1) TRANSACTION:
TRANSACTION 12347, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 103, OS thread handle 12345, query id 123456 test 192.168.1.1 root
INSERT INTO accounts (id, balance) VALUES (2, 1000)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12347 lock_mode X insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000003031; asc    01;;
 2: len 7; hex b60000019d0110; asc        ;;

*** (2) TRANSACTION:
TRANSACTION 12348, ACTIVE 6 sec inserting
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 104, OS thread handle 12346, query id 123457 test 192.168.1.2 root
INSERT INTO accounts (id, balance) VALUES (1, 2000)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`accounts` trx id 12348 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000003030; asc    00;;
 2: len 7; hex b60000019d010a; asc        ;;

*** (1) AND (2) WAITING FOR EACH OTHER'S LOCKS!

说明：

• 输出显示两个事务（12347和12348）在插入数据时发生死锁，互相等待对方持有的锁。
• SHOW ENGINE INNODB STATUS 会打印最近一次死锁的详细信息，包括锁定的表、索引、行数据及事务操作，用于分析死锁原因。

总结：如何通过系统表诊断锁问题

1. 第一步：通过 INNODB_TRX 查看是否有事务处于 LOCK WAIT 状态，定位等待者和阻塞者的线程ID。
2. 第二步：通过 data_locks 或 INNODB_LOCKS 查看具体锁的类型、锁定的表和行数据。
3. 第三步：通过 data_lock_waits 或 INNODB_LOCK_WAITS 确认锁等待的阻塞关系。
4. 第四步：结合 SHOW ENGINE INNODB STATUS 的详细日志分析死锁或长时间等待的原因。

通过这些系统表的组合查询，可以快速定位数据库中的锁竞争、死锁等性能问题，并针对性优化事务逻辑或索引设计。

死锁问题分析

set transcation_isolation='repeatable-read';
Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;
查看近期死锁日志信息：show engine innodb status; 

锁优化实践

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理设计索引，尽量缩小锁的范围
• 尽可能减少检索条件范围，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
• 尽可能用低的事务隔离级别

MVCC多版本并发控制机制

MVCC（多版本并发控制）是MySQL中InnoDB存储引擎实现读一致性和并发控制的核心机制，主要用于读已提交（RC）和可重复读（RR）隔离级别（默认是RR）。它通过版本链和一致性读视图避免锁竞争，提升并发性能。

核心要素

1. 版本链
   • 每行数据修改时会生成多个版本，通过隐藏字段（trx_id、roll_ptr等）串联成版本链。
   • trx_id：修改数据的事务ID（唯一递增）。
   • roll_ptr：指向旧版本数据（存于回滚日志undo log）。
2. 一致性读视图（Read View）
   • 事务执行普通查询（非锁定读，如SELECT）时，会生成一个快照（读视图），记录当前活跃事务ID列表。
   • 通过读视图判断数据版本是否可见，实现无锁读。

在MySQL中的应用场景

1. 不同隔离级别下的行为

• 可重复读（RR）
  • 事务启动时生成读视图，整个事务期间只读该视图，保证多次读取结果一致（避免不可重复读和幻读）。
  • 例：

-- 事务A（RR隔离级别）
BEGIN;
SELECT * FROM t WHERE id=1; -- 生成读视图V1，读取版本链中可见的数据
-- 其他事务修改id=1的数据，生成新版本（trx_id=102）
SELECT * FROM t WHERE id=1; -- 仍用V1，读取旧版本数据（不可重复读被禁止）
COMMIT;

• 读已提交（RC）
  • 每次查询时重新生成读视图，保证每次读取的是最新已提交数据（可能出现不可重复读）。
  • 例：

-- 事务A（RC隔离级别）
BEGIN;
SELECT * FROM t WHERE id=1; -- 读视图V1，读取旧版本
-- 其他事务提交修改（trx_id=102）
SELECT * FROM t WHERE id=1; -- 重新生成V2，读取新版本（允许不可重复读）
COMMIT;

2. 与锁的配合

• 快照读（非阻塞读）：普通SELECT使用MVCC，不加锁，读旧版本数据（一致性读）。
• 当前读（阻塞读）：加锁语句（如SELECT ... FOR UPDATE）直接读最新数据，需等待锁释放。
  • 例：

-- 事务A（当前读）
SELECT * FROM t WHERE id=1 FOR UPDATE; -- 读取最新数据，加行锁，阻塞其他写事务

3. 提升并发性能

• 写操作（如INSERT/UPDATE/DELETE）生成新版本，读操作通过MVCC访问旧版本，避免读写阻塞。
• 场景：高并发读场景（如电商商品详情页），读不阻塞写，写不阻塞读。

关键特性

• 无锁读：普通查询不阻塞写事务，反之亦然（除了SERIALIZABLE隔离级别）。
• 读一致性：根据隔离级别控制数据可见性，避免脏读、不可重复读等问题。
• 回滚日志管理：旧版本数据存于undo log，由InnoDB自动清理（purge线程），避免日志膨胀。

总结

MVCC是InnoDB实现高并发的基石，通过版本链和读视图在一致性和并发性间找到平衡。理解其原理有助于优化事务设计（如合理选择隔离级别）和排查锁问题（如长时间锁等待可能因MVCC版本链过长导致）。