-
-
Save bluerabbit/051a3bceb8162a552349 to your computer and use it in GitHub Desktop.
MySQL ロック競合の解析手順
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 実行中のプロセスを調べる | |
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 [テーブル名]; |
Author
bluerabbit
commented
Aug 19, 2021
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment