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 을 잡고 있는 쿼리 확인이 잘된다.
