Instantly share code, notes, and snippets.

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 [テーブル名];
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment