Skip to content

Instantly share code, notes, and snippets.

@bluerabbit
Last active Aug 19, 2021
Embed
What would you like to do?
MySQL ロック競合の解析手順
-- 実行中のプロセスを調べる
mysql> show processlist;
-- 実行中のプロセスのSQL詳細を調べる
mysql> show full processlist \G;
-- processlistを元にプロセスをkill
mysql> kill [プロセスId]
-- 実行中のプロセスのSQL詳細を調べる
mysql> SHOW ENGINE INNODB STATUS \G;
-- see
http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-general-monitor.html
-- In_use がTableロック
show open tables from [データベース名];
-- http://d.hatena.ne.jp/sh2/20090618
-- MySQLのディクショナリテーブルを調べる
mysql> use information_schema
mysql> show tables like 'INNO%';
+--------------------------------------+
| Tables_in_information_schema (INNO%) |
+--------------------------------------+
| INNODB_CMP |
| INNODB_CMP_RESET |
| INNODB_CMPMEM |
| INNODB_CMPMEM_RESET |
| INNODB_LOCK_WAITS |
| INNODB_LOCKS |
| INNODB_TRX |
+--------------------------------------+
SELECT * FROM INNODB_LOCK_WAITS;
mysql> desc INNODB_LOCK_WAITS;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |
| requested_lock_id | varchar(81) | NO | | | |
| blocking_trx_id | varchar(18) | NO | | | |
| blocking_lock_id | varchar(81) | NO | | | |
+-------------------+-------------+------+-----+---------+-------+
* blocking_trx_id先にロックを獲得していたtx
* requesting_trx_idはロック取得待ちのtx
-- ロックしているSQLを取得する
SELECT INNODB_TRX.trx_id, INNODB_TRX.trx_query FROM INNODB_LOCK_WAITS INNER JOIN INNODB_TRX ON INNODB_LOCK_WAITS.requesting_trx_id = INNODB_TRX.trx_id;
-- ロック取得待ちのtxのプロセスIDを取得する
SELECT INNODB_TRX.trx_id FROM INNODB_LOCK_WAITS INNER JOIN INNODB_TRX ON INNODB_LOCK_WAITS.blocking_trx_id = INNODB_TRX.trx_id;
SELECT * FROM INNODB_LOCKS;
mysql> desc INNODB_LOCKS;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |
| lock_trx_id | varchar(18) | NO | | | |
| lock_mode | varchar(32) | NO | | | |
| lock_type | varchar(32) | NO | | | |
| lock_table | varchar(1024) | NO | | | |
| lock_index | varchar(1024) | YES | | NULL | |
| lock_space | bigint(21) unsigned | YES | | NULL | |
| lock_page | bigint(21) unsigned | YES | | NULL | |
| lock_rec | bigint(21) unsigned | YES | | NULL | |
| lock_data | varchar(8192) | YES | | NULL | |
+-------------+---------------------+------+-----+---------+-------+
SELECT * FROM INNODB_TRX;
mysql> desc INNODB_TRX;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |
| trx_state | varchar(13) | NO | | | |
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |
| trx_requested_lock_id | varchar(81) | YES | | NULL | |
| trx_wait_started | datetime | YES | | NULL | |
| trx_weight | bigint(21) unsigned | NO | | 0 | |
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |
| trx_query | varchar(1024) | YES | | NULL | |
| trx_operation_state | varchar(64) | YES | | NULL | |
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |
| trx_isolation_level | varchar(16) | NO | | | |
| trx_unique_checks | int(1) | NO | | 0 | |
| trx_foreign_key_checks | int(1) | NO | | 0 | |
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |
+----------------------------+---------------------+------+-----+---------------------+-------+
-- どのSQLで何行ロックされているかとlock_typeを調べる
SELECT
INNODB_TRX.trx_mysql_thread_id,
INNODB_TRX.trx_query,
INNODB_TRX.trx_rows_locked,
INNODB_TRX.trx_tables_in_use,
INNODB_TRX.trx_tables_locked,
INNODB_LOCKS.lock_mode,
INNODB_LOCKS.lock_type,
INNODB_LOCKS.lock_table,
INNODB_LOCKS.lock_index
FROM
INNODB_TRX INNER JOIN INNODB_LOCKS
ON INNODB_TRX.trx_id = INNODB_LOCKS.lock_trx_id
WHERE trx_state = 'LOCK WAIT';
-- タイムアウト時間を調べる
mysql> show global variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50 |
+--------------------------+-------+
-- INDEXの有無を調べる
SHOW INDEX FROM [テーブル名];
@bluerabbit

This comment has been minimized.

Copy link
Owner Author

@bluerabbit bluerabbit commented Aug 19, 2021

MySQL 8からは下記

SELECT
    requesting_ps.user AS requesting_user,
    requesting_ps.host AS requesting_host,
    requesting_ps.db AS requesting_db,
    requesting_lock.lock_mode AS requesting_lock_mode,
    requesting_lock.lock_type AS requesting_lock_type,
    requesting_lock.OBJECT_NAME AS requesting_lock_table,
    requesting_lock.INDEX_NAME AS requesting_lock_index,
    requesting_trx.trx_id AS requesting_trx_id,
    requesting_trx.trx_query AS requesting_trx_query,
    requesting_trx.trx_state AS requesting_trx_state,
    requesting_trx.trx_started AS requesting_trx_started,
    requesting_trx.trx_wait_started AS requesting_trx_wait_started,
    requesting_trx.trx_lock_structs AS requesting_trx_lock_structs,
    requesting_trx.trx_rows_locked AS requesting_trx_rows_locked,
    requesting_trx.trx_isolation_level AS requesting_trx_isolation_level,
    blocking_ps.user AS blocking_user,
    blocking_ps.host AS blocking_host,
    blocking_ps.db AS blocking_db,
    blocking_lock.lock_mode AS blocking_lock_mode,
    blocking_lock.lock_type AS blocking_lock_type,
    blocking_lock.OBJECT_NAME AS blocking_lock_table,
    blocking_lock.INDEX_NAME AS blocking_lock_index,
    blocking_trx.trx_id AS blocking_trx_id,
    blocking_trx.trx_state AS blocking_trx_state,
    blocking_trx.trx_started AS blocking_trx_started,
    blocking_trx.trx_lock_structs AS blocking_trx_lock_structs,
    blocking_trx.trx_rows_locked AS blocking_trx_rows_locked,
    blocking_trx.trx_isolation_level AS blocking_trx_isolation_level
FROM
    performance_schema.data_lock_waits
    INNER JOIN information_schema.INNODB_TRX requesting_trx ON performance_schema.data_lock_waits.requesting_engine_transaction_id = requesting_trx.trx_id
    INNER JOIN information_schema.PROCESSLIST requesting_ps ON requesting_ps.id = requesting_trx.trx_mysql_thread_id
    INNER JOIN performance_schema.data_locks requesting_lock ON requesting_lock.engine_lock_id = performance_schema.data_lock_waits.requesting_engine_lock_id
    INNER JOIN information_schema.INNODB_TRX blocking_trx ON performance_schema.data_lock_waits.blocking_engine_transaction_id = blocking_trx.trx_id
    INNER JOIN information_schema.PROCESSLIST blocking_ps ON blocking_ps.id = blocking_trx.trx_mysql_thread_id
    INNER JOIN performance_schema.data_locks blocking_lock ON blocking_lock.engine_lock_id = performance_schema.data_lock_waits.blocking_engine_lock_id
@bluerabbit

This comment has been minimized.

Copy link
Owner Author

@bluerabbit bluerabbit commented Aug 19, 2021

# ruby watch.rb

while true
  sql = <<-SQL
          SELECT
            requesting_ps.user AS requesting_user,
            requesting_ps.host AS requesting_host,
            requesting_ps.db AS requesting_db,
            requesting_lock.lock_mode AS requesting_lock_mode,
            requesting_lock.lock_type AS requesting_lock_type,
            requesting_lock.lock_table AS requesting_lock_table,
            requesting_lock.lock_index AS requesting_lock_index,
            requesting_trx.trx_id AS requesting_trx_id,
            requesting_trx.trx_query AS requesting_trx_query,
            requesting_trx.trx_state AS requesting_trx_state,
            requesting_trx.trx_started AS requesting_trx_started,
            requesting_trx.trx_wait_started AS requesting_trx_wait_started,
            requesting_trx.trx_lock_structs AS requesting_trx_lock_structs,
            requesting_trx.trx_rows_locked AS requesting_trx_rows_locked,
            requesting_trx.trx_isolation_level AS requesting_trx_isolation_level,
            blocking_ps.user AS blocking_user,
            blocking_ps.host AS blocking_host,
            blocking_ps.db AS blocking_db,
            blocking_lock.lock_mode AS blocking_lock_mode,
            blocking_lock.lock_type AS blocking_lock_type,
            blocking_lock.lock_table AS blocking_lock_table,
            blocking_lock.lock_index AS blocking_lock_index,
            blocking_trx.trx_id AS blocking_trx_id,
            blocking_trx.trx_state AS blocking_trx_state,
            blocking_trx.trx_started AS blocking_trx_started,
            blocking_trx.trx_lock_structs AS blocking_trx_lock_structs,
            blocking_trx.trx_rows_locked AS blocking_trx_rows_locked,
            blocking_trx.trx_isolation_level AS blocking_trx_isolation_level
          FROM
            INNODB_LOCK_WAITS
            INNER JOIN INNODB_TRX requesting_trx
              ON INNODB_LOCK_WAITS.requesting_trx_id = requesting_trx.trx_id
            INNER JOIN PROCESSLIST requesting_ps
              ON requesting_ps.id = requesting_trx.trx_mysql_thread_id
            INNER JOIN INNODB_LOCKS requesting_lock
              ON requesting_lock.lock_id = INNODB_LOCK_WAITS.requested_lock_id
            INNER JOIN INNODB_TRX blocking_trx
              ON INNODB_LOCK_WAITS.blocking_trx_id = blocking_trx.trx_id
            INNER JOIN PROCESSLIST blocking_ps
              ON blocking_ps.id = blocking_trx.trx_mysql_thread_id
            INNER JOIN INNODB_LOCKS blocking_lock
              ON blocking_lock.lock_id = INNODB_LOCK_WAITS.blocking_lock_id
  SQL

  # https://stackoverflow.com/questions/15640287/change-output-format-for-mysql-command-line-results-to-csv
  result = `mysql -uroot -pyour_pass -P 3306 -h 127.0.0.1 -D information_schema --skip-column-names -e '#{sql}' | awk -F'\\t' '{ sep=""; for(i = 1; i <= NF; i++) { gsub(/\\\\t/,"\\t",$i); gsub(/\\\\n/,"\\n",$i); gsub(/\\\\\\\\/,"\\\\",$i); gsub(/"/,"\\"\\"",$i); printf sep"\\""$i"\\""; sep=","; if(i==NF){printf"\\n"}}}'`
  puts result
  File.write("watch.log", result)
  sleep 1
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment