synch/cond/innodb/row_lock_wait

当一个会话锁定了一个行以进行更新，而另一个会话尝试更新同一行时，将会发生 synch/cond/innodb/row_lock_wait 事件。有关更多信息，请参阅 MySQL 参考中的 InnoDB 锁定。

支持的引擎版本

以下引擎版本支持此等待事件信息：

等待次数增加的可能原因

多个数据操作语言 (DML) 语句同时访问相同的一行或多行。

操作

根据您看到的其他等待事件，我们建议采取不同的操作。

查找并响应负责此等待事件的 SQL 语句

使用性能详情可识别应对此等待事件负责的 SQL 语句。请考虑以下策略：

有关使用性能详情进行故障排除的有用概览，请参阅博客文章利用性能详情分析 Amazon Aurora MySQL 工作负载。

查找并响应阻止会话

确定阻止会话处于空闲状态还是活动状态。另外，了解会话是来自应用程序还是活跃用户。

要识别持有锁的会话，您可以运行 SHOW ENGINE INNODB STATUS。下面的示例显示了示例输出。

mysql> SHOW ENGINE INNODB STATUS;

---TRANSACTION 1688153, ACTIVE 82 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 4244, OS thread handle 70369524330224, query id 4020834 172.31.14.179 reinvent executing
select id1 from test.t1 where id1=1 for update
------- TRX HAS BEEN WAITING 24 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 4 n bits 72 index GEN_CLUST_INDEX of table test.t1 trx id 1688153 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0

或者，您可以使用以下查询来提取有关当前锁定的详细信息。

mysql> SELECT p1.id waiting_thread,
    p1.user waiting_user,
    p1.host waiting_host,
    it1.trx_query waiting_query,
    ilw.requesting_engine_transaction_id waiting_transaction,
    ilw.blocking_engine_lock_id blocking_lock,
    il.lock_mode blocking_mode,
    il.lock_type blocking_type,
    ilw.blocking_engine_transaction_id blocking_transaction,
    CASE it.trx_state
        WHEN 'LOCK WAIT'
        THEN it.trx_state
        ELSE p.state end blocker_state,
    concat(il.object_schema,'.', il.object_name) as locked_table,
    it.trx_mysql_thread_id blocker_thread,
    p.user blocker_user,
    p.host blocker_host
FROM performance_schema.data_lock_waits ilw
JOIN performance_schema.data_locks il
ON ilw.blocking_engine_lock_id = il.engine_lock_id
AND ilw.blocking_engine_transaction_id = il.engine_transaction_id
JOIN information_schema.innodb_trx it
ON ilw.blocking_engine_transaction_id = it.trx_id join information_schema.processlist p
ON it.trx_mysql_thread_id = p.id join information_schema.innodb_trx it1
ON ilw.requesting_engine_transaction_id = it1.trx_id join information_schema.processlist p1
ON it1.trx_mysql_thread_id = p1.id\G

*************************** 1. row ***************************
waiting_thread: 4244
waiting_user: reinvent
waiting_host: 123.456.789.012:18158
waiting_query: select id1 from test.t1 where id1=1 for update
waiting_transaction: 1688153
blocking_lock: 70369562074216:11:4:2:70369549808672
blocking_mode: X
blocking_type: RECORD
blocking_transaction: 1688142
blocker_state: User sleep
locked_table: test.t1
blocker_thread: 4243
blocker_user: reinvent
blocker_host: 123.456.789.012:18156
1 row in set (0.00 sec)

当您识别会话时，您的选项包括：

有关识别阻止事务的更多信息，请参阅 MySQL 参考手册中的使用 InnoDB 事务和锁定信息。