事务并发死锁查询语句

5.7 版本查询方式：

SELECT
    b.trx_mysql_thread_id,
    b.trx_query,
    c.trx_mysql_thread_id,
    c.trx_query,
    d.lock_mode,
    lock_type,
    lock_table,
    lock_index
FROM
    information_schema.innodb_lock_waits a
    INNER JOIN information_schema.innodb_trx b ON a.requesting_trx_id = b.trx_id
    INNER JOIN information_schema.innodb_trx c ON a.blocking_trx_id = c.trx_id
    INNER JOIN information_schema.innodb_locks d ON a.blocking_lock_id = d.lock_id;

8.0 版本查询方式：

SELECT
    b.trx_query,
    c.OBJECT_SCHEMA,
    c.OBJECT_NAME,
    c.INDEX_NAME,
    c.LOCK_MODE,
    c.LOCK_TYPE,
    c.LOCK_STATUS,
    d.PROCESSLIST_STATE AS STATE,
    d.PROCESSLIST_COMMAND AS COMMAD,
    d.PROCESSLIST_HOST AS HOST,
    d.PROCESSLIST_USER AS USER,
    d. `NAME` AS ProcessName,
    e.SQL_TEXT AS BlockSql

FROM
    performance_schema.data_lock_waits a
    LEFT JOIN performance_schema.threads d ON a.REQUESTING_THREAD_ID = d.THREAD_ID
    LEFT JOIN information_schema.INNODB_TRX b ON a.REQUESTING_ENGINE_TRANSACTION_ID = b.trx_id
    LEFT JOIN performance_schema.data_locks c ON a.REQUESTING_ENGINE_LOCK_ID = c.ENGINE_LOCK_ID
    LEFT JOIN performance_schema.events_statements_current e ON a.BLOCKING_THREAD_ID = e.THREAD_ID

或者再通过 show full processlist 查看所有进程 状态为等待状态的语句

优化生产死锁sql 问题

本作品采用《CC 协议》，转载必须注明作者和本文链接