MySQL鎖等待分析【2】

標籤：tab   pdb   read   format   block   info   bsp   使用   date   

MySQL鎖等待分析【1】中對鎖等待的分析是一步一步來的、雖然最後是分析出來了，可是用時是比較長的；理清各個表之間的關係後，得到如下SQL語句，方便以後使用

select     block_trx.trx_mysql_thread_id as blocking_session_id, -- 已經持有鎖的session ID    request_trx.trx_mysql_thread_id as request_session_id, -- 正在申請鎖的session ID    block_trx.trx_query as blocking_sql_text, -- 已經持有鎖的SQL語句    request_trx.trx_query as requesting_sql_text, -- 正在申請鎖的SQL語句    waits.blocking_trx_id as blocking_trx_id, -- 已經持有鎖的事務ID    waits.requesting_trx_id as requesting_trx_id, -- 正在申請鎖的事務ID    waits.requested_lock_id as requested_lock_id, -- 鎖對象的ID    locks.lock_table as lock_table, -- 鎖對象所鎖定的表    locks.lock_type as lock_type, -- 鎖類型    locks.lock_mode as lock_mode -- 鎖模式from information_schema.innodb_lock_waits as waitsinner join information_schema.innodb_trx as block_trx    on waits.blocking_trx_id=block_trx.trx_idinner join information_schema.innodb_trx as request_trx    on waits.requesting_trx_id=request_trx.trx_idinner join information_schema.innodb_locks as locks    on waits.requested_lock_id=locks.lock_id;+---------------------+--------------------+-------------------+-----------------------------------+-----------------+-------------------+-------------------+--------------+-----------+-----------+| blocking_session_id | request_session_id | blocking_sql_text | requesting_sql_text               | blocking_trx_id | requesting_trx_id | requested_lock_id | lock_table   | lock_type | lock_mode |+---------------------+--------------------+-------------------+-----------------------------------+-----------------+-------------------+-------------------+--------------+-----------+-----------+|                1435 |               1255 | NULL              | select * from tempdb.t for update | 26293           | 26299             | 26299:38:3:2      | `tempdb`.`t` | RECORD    | X         ||                1435 |               1165 | NULL              | insert into t(x) values(3)        | 26293           | 26294             | 26294:38:3:1      | `tempdb`.`t` | RECORD    | X         |+---------------------+--------------------+-------------------+-----------------------------------+-----------------+-------------------+-------------------+--------------+-----------+-----------+

 

MySQL鎖等待分析【2】