information_schema의 lock 관련 테이블들을 조회하여
lock holder 와 waiting session을 찾는 방법을 소개해드리겠습니다.
-1. LOCK 유발 상황
MariaDB [(none)]> create table test.lock_test
-> ( num int,
-> primary key (num));
Query OK, 0 rows affected (0.07 sec)
-. Lock 유발 세션
MariaDB [(none)]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 23972 | +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> begin; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> insert into test.lock_test values(100); Query OK, 1 row affected (0.01 sec)
=> Primary key 걸려있는 컬럼에 data insert 후 트랜잭션 종료 X -> lock 유발
-. Lock Wait 세션
MariaDB [(none)]> select connection_id(); +-----------------+ | connection_id() | +-----------------+ | 24651 | +-----------------+ 1 row in set (0.00 sec) MariaDB [(none)]> insert into test.lock_test values(100);
=> 대기중
MariaDB [(none)]> show processlist; +-------+-------------+-----------+------+---------+------+--------------------------+----------------------------------------+----------+ | Id | User | Host | db | Command | Time | State | Info | Progress | +-------+-------------+-----------+------+---------+------+--------------------------+----------------------------------------+----------+ | 1 | system user | | NULL | Daemon | NULL | | NULL | 0.000 | | 2 | system user | | NULL | Daemon | NULL | | NULL | 0.000 | | 3 | system user | | NULL | Daemon | NULL | | NULL | 0.000 | | 4 | system user | | NULL | Daemon | NULL | | NULL | 0.000 | | 5 | system user | | NULL | Daemon | NULL | InnoDB background thread | NULL | 0.000 | | 23972 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 | | 24651 | root | localhost | NULL | Query | 1543 | update | insert into test.lock_test values(100) | 0.000 | +-------+-------------+-----------+------+---------+------+--------------------------+----------------------------------------+----------+ 7 rows in set (0.00 sec)
-2. LOCK 조회
=> informatition_schema DB의 innodb_locks, innodb_lock_waits, innodb_trx 테이블로 조회함
MariaDB [(none)]> select * from information_schema.innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 3848 | 3848:6:3:2 | 3847 | 3847:6:3:2 | +-------------------+-------------------+-----------------+------------------+ 1 row in set (0.00 sec)
-. 3848 trx_id를 가진 세션이 block 됨
-. 3847 trx_id를 가진 세션이 block 시킴
MariaDB [(none)]> select * from information_schema.innodb_locks; +------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+ | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | +------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+ | 3848:6:3:2 | 3848 | S | RECORD | "test"."lock_test" | PRIMARY | 6 | 3 | 2 | 100 | | 3847:6:3:2 | 3847 | X | RECORD | "test"."lock_test" | PRIMARY | 6 | 3 | 2 | 100 | +------------+-------------+-----------+-----------+--------------------+------------+------------+-----------+----------+-----------+ 2 rows in set (0.00 sec)
-. 3848 trx_id를 가진 session이 lock_mode S 를 "test"."lock_test" 테이블과 PRIMARY index 에 lock 검
-. 3847 trx_id를 가진 session이 lock_mode X를 "test"."lock_test" 테이블과 PRIMARY index 에 lock 검
MariaDB [(none)]> select * from information_schema.innodb_trx\G;
*************************** 1. row ***************************
trx_id: 3848
trx_state: LOCK WAIT
trx_started: 2018-06-17 11:25:31
trx_requested_lock_id: 3848:6:3:2
trx_wait_started: 2018-06-17 11:26:29
trx_weight: 3
trx_mysql_thread_id: 24651
trx_query: insert into test.lock_test values(100)
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 1136
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: READ COMMITTED
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
-3. Lock 조회하는 유용한 쿼리
MariaDB [(none)]> SELECT straight_join
-> w.trx_mysql_thread_id waiting_thread,
-> w.trx_id waiting_trx_id,
-> w.trx_query waiting_query,
-> b.trx_mysql_thread_id blocking_thread,
-> b.trx_id blocking_trx_id,
-> b.trx_query blocking_query,
-> bl.lock_id blocking_lock_id,
-> bl.lock_mode blocking_lock_mode,
-> bl.lock_type blocking_lock_type,
-> bl.lock_table blocking_lock_table,
-> bl.lock_index blocking_lock_index,
-> wl.lock_id waiting_lock_id,
-> wl.lock_mode waiting_lock_mode,
-> wl.lock_type waiting_lock_type,
-> wl.lock_table waiting_lock_table,
-> wl.lock_index waiting_lock_index
-> FROM
-> information_schema.INNODB_LOCK_WAITS ilw ,
-> information_schema.INNODB_TRX b ,
-> information_schema.INNODB_TRX w ,
-> information_schema.INNODB_LOCKS bl ,
-> information_schema.INNODB_LOCKS wl
-> WHERE
-> b.trx_id = ilw.blocking_trx_id
-> AND w.trx_id = ilw.requesting_trx_id
-> AND bl.lock_id = ilw.blocking_lock_id
-> AND wl.lock_id = ilw.requested_lock_id\G;
*************************** 1. row ***************************
waiting_thread: 24651
waiting_trx_id: 3848
waiting_query: insert into test.lock_test values(100)
blocking_thread: 23972
blocking_trx_id: 3847
blocking_query: NULL
blocking_lock_id: 3847:6:3:2
blocking_lock_mode: X
blocking_lock_type: RECORD
blocking_lock_table: "test"."lock_test"
blocking_lock_index: PRIMARY
waiting_lock_id: 3848:6:3:2
waiting_lock_mode: S
waiting_lock_type: RECORD
waiting_lock_table: "test"."lock_test"
waiting_lock_index: PRIMARY
1 row in set (0.00 sec)
ERROR: No query specified
