lock session 찾기

show engine innodb status 의 TRANSACTIONS 부분에서 ACTIVE TRANSACTION 으로 Lock 을 잡고 있는 쿼리를 찾아야될때가 있다. 

회사에서 가장 많이 사용중인 5.7 버전부터 테스트해 본다. 

MySQL 5.7 테스트

mysql> create table tb_test (
    -> col1 int not null primary key,
    -> col2 varchar(10) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb_test values (1, 'aaa');
Query OK, 1 row affected (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set col2 = 'bbb' where col1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ACTIVE TRANSACTION은 확인되지만 어떤 쿼리인지는 확인이 안된다. 

mysql> show engine innodb status;
...
------------
TRANSACTIONS
------------
Trx id counter 1289
Purge done for trx's n:o < 0 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421405712030432, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1288, ACTIVE 80 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 139930706618112, query id 65 localhost root

mysql> select * from information_schema.innodb_locks;
Empty set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits;
Empty set, 1 warning (0.00 sec)

mysql> 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
    -> INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = ilw.blocking_trx_id
    -> INNER JOIN information_schema.INNODB_TRX w ON w.trx_id = ilw.requesting_trx_id
    -> INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = ilw.blocking_lock_id
    -> INNER JOIN information_schema.INNODB_LOCKS wl ON wl.lock_id = ilw.requested_lock_id;
Empty set, 3 warnings (0.00 sec)

다른 세션에서 Lock Wait 상황을 발생시키면?

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set col2 = 'bbb' where col1 = 1;

Lock Wait 가 발생하면 확인이 된다.

mysql> select * from information_schema.INNODB_LOCK_WAITS;
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 1289              | 1289:23:3:2       | 1288            | 1288:23:3:2      |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from information_schema.INNODB_TRX;
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| trx_id | trx_state | trx_started         | trx_requested_lock_id | trx_wait_started    | trx_weight | trx_mysql_thread_id | trx_query                                      | trx_operation_state | trx_tables_in_use | trx_tables_locked | trx_lock_structs | trx_lock_memory_bytes | trx_rows_locked | trx_rows_modified | trx_concurrency_tickets | trx_isolation_level | trx_unique_checks | trx_foreign_key_checks | trx_last_foreign_key_error | trx_adaptive_hash_latched | trx_adaptive_hash_timeout | trx_is_read_only | trx_autocommit_non_locking |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
| 1289   | LOCK WAIT | 2022-10-01 15:39:28 | 1289:23:3:2           | 2022-10-01 15:40:35 |          2 |                  10 | update tb_test set col2 = 'bbb' where col1 = 1 | starting index read |                 1 |                 1 |                2 |                  1136 |               2 |                 0 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
| 1288   | RUNNING   | 2022-10-01 15:31:45 | NULL                  | NULL                |          3 |                   8 | NULL                                           | NULL                |                 0 |                 1 |                2 |                  1136 |               1 |                 1 |                       0 | REPEATABLE READ     |                 1 |                      1 | NULL                       |                         0 |                         0 |                0 |                          0 |
+--------+-----------+---------------------+-----------------------+---------------------+------------+---------------------+------------------------------------------------+---------------------+-------------------+-------------------+------------------+-----------------------+-----------------+-------------------+-------------------------+---------------------+-------------------+------------------------+----------------------------+---------------------------+---------------------------+------------------+----------------------------+
2 rows in set (0.00 sec)

mysql> 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
    -> INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = ilw.blocking_trx_id
    -> INNER JOIN information_schema.INNODB_TRX w ON w.trx_id = ilw.requesting_trx_id
    -> INNER JOIN information_schema.INNODB_LOCKS bl ON bl.lock_id = ilw.blocking_lock_id
    -> INNER JOIN information_schema.INNODB_LOCKS wl ON wl.lock_id = ilw.requested_lock_id;
+----------------+----------------+------------------------------------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+-----------------+-------------------+-------------------+--------------------+--------------------+
| waiting_thread | waiting_trx_id | waiting_query                                  | blocking_thread | blocking_trx_id | blocking_query | blocking_lock_id | blocking_lock_mode | blocking_lock_type | blocking_lock_table | blocking_lock_index | waiting_lock_id | waiting_lock_mode | waiting_lock_type | waiting_lock_table | waiting_lock_index |
+----------------+----------------+------------------------------------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+-----------------+-------------------+-------------------+--------------------+--------------------+
|             10 | 1289           | update tb_test set col2 = 'bbb' where col1 = 1 |               8 | 1288            | NULL           | 1288:23:3:2      | X                  | RECORD             | `test`.`tb_test`    | PRIMARY             | 1289:23:3:2     | X                 | RECORD            | `test`.`tb_test`   | PRIMARY            |
+----------------+----------------+------------------------------------------------+-----------------+-----------------+----------------+------------------+--------------------+--------------------+---------------------+---------------------+-----------------+-------------------+-------------------+--------------------+--------------------+
1 row in set, 3 warnings (0.00 sec)

5.7 에서는 Lock Wait 이 발생해야 Lock 정보 확인이 가능하다.

즉 Lock 경합이 없으면 어떤 쿼리가 Lock 잡고 있는지 확인이 안된다. 

아하하하 미친다. (왜 미치냐면 이게 문제가 되고 있어서.)

 

MySQL 8.0 테스트

mysql> create table tb_test (
    -> col1 int not null primary key,
    -> col2 varchar(10) not null
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tb_test values (1, 'aaa');
Query OK, 1 row affected (0.03 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update tb_test set col2 = 'bbb' where col1 = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

ACTIVE TRANSACTION, Lock 쿼리 확인 

mysql> show engine innodb status;
------------
TRANSACTIONS
------------
Trx id counter 7713
Purge done for trx's n:o < 7710 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422006973530112, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422006973528496, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 422006973527688, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 7712, ACTIVE 26 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 140531536729856, query id 26 localhost root

mysql> select * from performance_schema.data_locks;
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID                         | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 140531996818648:1083:140532001090368   |                  7712 |        48 |       32 | test          | tb_test     | NULL           | NULL              | NULL       |       140532001090368 | TABLE     | IX            | GRANTED     | NULL      |
| INNODB | 140531996818648:22:4:2:140532001087456 |                  7712 |        48 |       32 | test          | tb_test     | NULL           | NULL              | PRIMARY    |       140532001087456 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
+--------+----------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
2 rows in set (0.01 sec)

mysql> SELECT straight_join
    ->   dl.thread_id,est.sql_text,dl.object_schema,dl.object_name,dl.index_name,
    ->   dl.lock_type,dl.lock_mode,dl.lock_status,dl.lock_data
    -> FROM performance_schema.data_locks dl 
    -> INNER JOIN performance_schema.events_statements_current est ON dl.thread_id = est.thread_id
    -> ORDER BY est.timer_start,dl.object_instance_begin;
+-----------+------------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| thread_id | sql_text                                       | object_schema | object_name | index_name | lock_type | lock_mode     | lock_status | lock_data |
+-----------+------------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
|        48 | update tb_test set col2 = 'bbb' where col1 = 1 | test          | tb_test     | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
|        48 | update tb_test set col2 = 'bbb' where col1 = 1 | test          | tb_test     | NULL       | TABLE     | IX            | GRANTED     | NULL      |
+-----------+------------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
2 rows in set (0.00 sec)

8.0 에서는 Lock Wait 이 발생하지 않아도 Lock 을 잡고 있는 쿼리 확인이 잘된다.