mysql如何排查锁等待问题-mysql教程-PHP中文网

答案是通过分析SHOW ENGINE INNODB STATUS和information_schema中INNODB_LOCK_WAITS等表，可定位锁等待的等待方与持有方；常见原因为长事务、索引不合理、大批量操作、隔离级别不当及应用访问顺序不一致，优化策略包括拆分事务、添加索引、批量分批处理、调整隔离级别及统一资源访问顺序；死锁由InnoDB自动检测并回滚牺牲事务，需结合日志分析冲突SQL，通过短事务、一致访问顺序和重试机制应对。

mysql如何排查锁等待问题

MySQL锁等待问题，说白了，就是数据库里有事务因为要获取某个资源（比如一行数据、一个表）而被另一个事务“卡住”了，迟迟无法继续执行。排查这类问题，核心思路是找到是谁在等待、等待什么、以及谁持有了这个资源并导致了等待。

解决方案

要排查MySQL的锁等待，我通常会从几个关键点入手。首先，最直接的证据往往藏在

SHOW ENGINE INNODB STATUS

登录后复制

的输出里，尤其是

LATEST DETECTED DEADLOCK

登录后复制

和

TRANSACTIONS

登录后复制

部分。这里能看到最近的死锁信息，以及当前活跃事务的状态，包括它们持有的锁和正在请求的锁。

比如，当你在终端里敲下：

SHOW ENGINE INNODB STATUS;

登录后复制

你会看到一大段输出。你需要仔细阅读

TRANSACTIONS

登录后复制

这个小节。它会列出每一个正在运行的InnoDB事务，包括它们的ID (

trx_id

登录后复制

)、状态 (

state

登录后复制

)、正在执行的SQL语句 (

query

登录后复制

)，以及最重要的——它们正在等待的锁 (

waiting for lock

登录后复制

) 和已经持有的锁 (

has held lock(s)

登录后复制

)。

如果发现有事务处于

waiting for lock

登录后复制

状态，那么恭喜你，问题就在眼前了。你需要记下这个事务的

trx_id

登录后复制

，以及它正在等待的锁信息。

再进一步，

information_schema

登录后复制

数据库里有几张表是排查锁等待的利器：

INNODB_LOCKS

登录后复制

、

INNODB_LOCK_WAITS

登录后复制

和

INNODB_TRX

登录后复制

。它们提供了更结构化、更方便查询的锁信息。

```
INNODB_LOCKS
```
登录后复制
：记录了当前系统中所有的InnoDB锁。
```
INNODB_LOCK_WAITS
```
登录后复制
：记录了所有正在发生的锁等待，它会明确告诉你哪个事务在等待哪个事务释放锁。
```
INNODB_TRX
```
登录后复制
：提供了所有活跃事务的详细信息，包括事务ID、执行的SQL、事务开始时间、锁等待时间等。

通过组合查询这些表，我们就能构建出完整的锁等待链条。

如何快速定位正在等待的事务和持有锁的事务？

说实话，每次遇到锁等待，我最想做的就是马上找出那个“肇事者”和“受害者”。这有点像侦探破案，需要一点精准的查询。

我个人觉得，直接查询

information_schema.INNODB_LOCK_WAITS

登录后复制

是最高效的。它直接揭示了等待者和持有者之间的关系。

一个经典的查询语句是这样的：

SELECT
    lw.requesting_trx_id AS waiting_trx_id,
    trx.trx_query AS waiting_query,
    lw.blocking_trx_id AS blocking_trx_id,
    b_trx.trx_query AS blocking_query,
    b_lock.lock_mode,
    b_lock.lock_type,
    b_lock.lock_table,
    b_lock.lock_index,
    lw.wait_started,
    trx.trx_state AS waiting_trx_state,
    b_trx.trx_state AS blocking_trx_state,
    trx.trx_mysql_thread_id AS waiting_thread_id,
    b_trx.trx_mysql_thread_id AS blocking_thread_id
FROM
    information_schema.INNODB_LOCK_WAITS lw
JOIN
    information_schema.INNODB_TRX trx ON lw.requesting_trx_id = trx.trx_id
JOIN
    information_schema.INNODB_TRX b_trx ON lw.blocking_trx_id = b_trx.trx_id
JOIN
    information_schema.INNODB_LOCKS b_lock ON lw.blocking_trx_id = b_lock.trx_id
WHERE
    b_lock.lock_mode IS NOT NULL
ORDER BY
    lw.wait_started ASC;

登录后复制

这条SQL语句的目的是将等待事务、持有锁事务以及它们各自正在执行的查询、锁的模式、锁定的表和索引等信息，全部整合到一起。

```
waiting_trx_id
```
登录后复制
和
```
waiting_query
```
登录后复制
告诉你哪个事务在等待，它在执行什么SQL。
```
blocking_trx_id
```
登录后复制
和
```
blocking_query
```
登录后复制
告诉你哪个事务持有了锁，导致了等待，它在执行什么SQL。
```
lock_table
```
登录后复制
和
```
lock_index
```
登录后复制
则指明了被锁定的具体资源。

通过这个查询，你就能一目了然地看到谁在等谁，以及为什么等。这对于快速判断问题根源至关重要。

分析锁等待的常见原因和优化策略有哪些？

定位到问题之后，下一步就是分析原因并着手优化。我的经验告诉我，锁等待问题并非无迹可寻，它通常是以下几个原因造成的：

AI建筑知识问答

用人工智能ChatGPT帮你解答所有建筑问题

查看详情

长事务：这是最常见的元凶。一个事务执行时间过长，比如包含了大量的数据操作，或者中间需要人工确认，那么它持有的锁就会长时间不释放，导致其他事务排队等待。
- 优化策略：尽量将大事务拆分成小事务，或者优化业务逻辑，减少事务的执行时间。避免在事务中进行耗时的网络请求或用户交互。
不合理的索引：如果查询没有命中索引，或者索引选择不当，InnoDB可能会进行全表扫描。即使是
```
UPDATE
```
登录后复制
或
```
DELETE
```
登录后复制
操作，在没有合适索引的情况下，也可能导致锁的粒度从行锁升级为表锁（或锁定大量不必要的行），从而大大增加锁冲突的概率。
- 优化策略：分析
```
EXPLAIN
```
  登录后复制
  输出，确保所有涉及
```
WHERE
```
  登录后复制
  条件的字段、
```
JOIN
```
  登录后复制
  字段、
```
ORDER BY
```
  登录后复制
  和
```
GROUP BY
```
  登录后复制
  字段都有合适的索引。特别是更新或删除操作，其
```
WHERE
```
  登录后复制
  子句的索引至关重要。
大批量更新/删除：一次性更新或删除成千上万条数据，会长时间持有大量行锁。
- 优化策略：将大批量操作拆分成小批次进行，每次处理一部分数据，并在批次之间进行
```
COMMIT
```
  登录后复制
  。这能有效缩短每次事务的执行时间，减少锁的持有时间。
事务隔离级别设置不当：MySQL InnoDB默认的隔离级别是
```
REPEATABLE READ
```
登录后复制
，它能保证在一个事务内多次读取同一数据结果一致，但代价是会持有更多的锁。
- 优化策略：如果业务允许，可以考虑将隔离级别降低到
```
READ COMMITTED
```
  登录后复制
  。
```
READ COMMITTED
```
  登录后复制
  在每次读取时都会重新获取最新数据，减少了不必要的锁持有，但在某些场景下可能会引入“不可重复读”的问题，需要权衡。
应用程序逻辑问题：有时候，问题不在数据库本身，而在应用程序对数据库的访问顺序。比如，两个事务都想更新A和B两张表，一个先锁A再锁B，另一个先锁B再锁A，这很容易造成死锁。
- 优化策略：在应用程序层面，尽量保证对数据库资源的访问顺序一致性。

遇到死锁（Deadlock）时，应该如何分析和处理？

死锁是锁等待的一种特殊形式，它意味着两个或多个事务互相持有对方想要的锁，形成了一个闭环，谁也无法继续。MySQL InnoDB引擎会自动检测死锁并选择一个“牺牲品”（通常是修改行数最少的事务）进行回滚，以打破循环。虽然MySQL处理了，但对应用程序来说，这仍然是个错误，需要我们去分析并解决。

当死锁发生时，

SHOW ENGINE INNODB STATUS

登录后复制

的

LATEST DETECTED DEADLOCK

登录后复制

部分就是你的第一手资料。它会非常详细地告诉你：

死锁发生的时间。
涉及的两个事务：通常会标识为
```
TRANSACTION (1)
```
登录后复制
和
```
TRANSACTION (2)
```
登录后复制
。
每个事务的
trx_id
登录后复制
、
query
登录后复制
：它们当时在执行什么SQL。
每个事务持有的锁和正在请求的锁：这才是关键！它会告诉你事务A持有什么锁，想获取什么锁；事务B持有什么锁，想获取什么锁。通过对比，你就能看出哪个锁是造成死锁的症结。
哪个事务被回滚：通常会明确指出
```
WE ROLL BACK TRANSACTION (X)
```
登录后复制
。

分析死锁，核心在于理解这两个事务在“抢”什么资源。

举个例子，你可能会看到类似这样的输出：

*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec, process no 6789, OS thread id 1234567890
...
LOCK WAIT 2 lock(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 10, query id 100 localhost root updating
UPDATE my_table SET value = 'new_val_A' WHERE id = 1;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `my_db`.`my_table` trx id 12345 lock_mode X locks rec but not gap waiting
...
*** (2) TRANSACTION:
TRANSACTION 54321, ACTIVE 0 sec, process no 9876, OS thread id 9876543210
...
LOCK WAIT 2 lock(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 20, query id 200 localhost root updating
UPDATE my_table SET value = 'new_val_B' WHERE id = 2;
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 456 n bits 72 index `PRIMARY` of table `my_db`.`my_table` trx id 54321 lock_mode X locks rec but not gap
...

登录后复制

这个例子可能不是一个典型的死锁，更像一个锁等待，但死锁的输出会更复杂，显示两个事务互相等待。你需要关注

WAITING FOR THIS LOCK TO BE GRANTED

登录后复制

和

HOLDS THE LOCK(S)

登录后复制

这两部分，它们揭示了事务之间的依赖关系。

处理死锁的策略：

保持事务短小精悍：这是防止死锁最有效的手段。事务持续时间越短，持有锁的时间越少，发生冲突的概率就越低。
以一致的顺序访问资源：如果应用程序中的所有事务都以相同的顺序访问和锁定资源，那么死锁的发生率会大大降低。这是解决“交叉锁定”型死锁的关键。
为WHERE子句添加合适的索引：确保
```
UPDATE
```
登录后复制
和
```
DELETE
```
登录后复制
语句的
```
WHERE
```
登录后复制
子句能够有效利用索引，这样InnoDB就能精确锁定需要的行，而不是扫描并锁定更多不相关的行。
优化SQL语句：减少不必要的锁，例如避免在事务中执行全表扫描。
应用程序层面的重试机制：虽然我们尽力避免死锁，但它仍然可能发生。在应用程序中捕获死锁异常（例如MySQL错误码1213），然后实现一个合理的重试机制。通常，回滚被牺牲的事务后，等待一小段时间再重新尝试执行该事务，是一个常见的做法。