information_schema는 meta data check 에 사용되고 데이터의 성격이 정적이라면
performance_schema는 성능 중심이고 데이터의 성격이 동적이라는 차이가 있습니다.
기존 information_schema의 innodb_locks , innodb_lock_waits 테이블이 performance_schema의 data_locks 로 lock 조회 관련 정보가 이관되면서
좀 더 자세하게 lock에 대한 정보를 조회할 수 있게되었는데 이번 글에서는 mysql 8버전에서 lock을 조회하는 방법을 살펴보겠습니다.
-. Mysql 5.7
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update lock_test set t=200 where t=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
=>session1 에서 transaction 시작
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 , -> 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; Empty set, 3 warnings (0.00 sec) ERROR: No query specified mysql> use information_schema; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> select * From innodb_locks; Empty set, 1 warning (0.00 sec) mysql> select * From innodb_lock_waits; Empty set, 1 warning (0.00 sec)
=> lock hold 내용도 조회 안됨
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update lock_test set t=2000 where t=2;
=> session 2 , lock wait 상황 발생
mysql> select * From innodb_lock_waits; +-------------------+-------------------+-----------------+------------------+ | requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id | +-------------------+-------------------+-----------------+------------------+ | 2851 | 2851:28:3:3 | 2850 | 2850:28:3:3 | +-------------------+-------------------+-----------------+------------------+ 1 row in 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 , 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: 6 waiting_trx_id: 2851 waiting_query: update lock_test set t=2000 where t=2 blocking_thread: 5 blocking_trx_id: 2850 blocking_query: NULL blocking_lock_id: 2850:28:3:3 blocking_lock_mode: X blocking_lock_type: RECORD blocking_lock_table: `test`.`lock_test` blocking_lock_index: PRIMARY waiting_lock_id: 2851:28:3:3 waiting_lock_mode: X waiting_lock_type: RECORD waiting_lock_table: `test`.`lock_test` waiting_lock_index: PRIMARY 1 row in set, 3 warnings (0.00 sec)
=> 5.7 버전에서는 실제 lock wait 발생해야 lock 관련 정보가 조회됨
lock 경합이 없는 상황에서는 실행중인 트랜잭션이 어떤 lock 을 잡고 있는지 조회 불가
-. Mysql 8.0
mysql> begin; Query OK, 0 rows affected (0.01 sec) mysql> update lock_test set t=200 where t=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
=> session 1
mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140555306883664:1063:140555220741656 ENGINE_TRANSACTION_ID: 2071 THREAD_ID: 47 EVENT_ID: 27 OBJECT_SCHEMA: test OBJECT_NAME: lock_test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140555220741656 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140555306883664:2:4:3:140555220738712 ENGINE_TRANSACTION_ID: 2071 THREAD_ID: 47 EVENT_ID: 27 OBJECT_SCHEMA: test OBJECT_NAME: lock_test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140555220738712 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 2 rows in set (0.00 sec)
=> 5.7 버전과는 달리 lock waiting session 이 없어도 현재 수행중인 트랜잭션이 갖고 있는 lock 정보가 조회됨
IX는 intensive exclusive lock으로 트랜잭션 중 DDL 방지용,
X,REC_NOT_GAP 는 record lock 으로 pk 컬럼에 update 가 발생했기 때문에 gap lock으로 안잡힌 상태
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update test.lock_test set t=2000 where t=2; mysql> select * from data_locks\G; *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140555306885392:1063:140555220753576 ENGINE_TRANSACTION_ID: 2072 THREAD_ID: 49 EVENT_ID: 5 OBJECT_SCHEMA: test OBJECT_NAME: lock_test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140555220753576 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140555306885392:2:4:3:140555220750696 ENGINE_TRANSACTION_ID: 2072 THREAD_ID: 49 EVENT_ID: 5 OBJECT_SCHEMA: test OBJECT_NAME: lock_test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140555220750696 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140555306883664:1063:140555220741656 ENGINE_TRANSACTION_ID: 2071 THREAD_ID: 47 EVENT_ID: 27 OBJECT_SCHEMA: test OBJECT_NAME: lock_test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140555220741656 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140555306883664:2:4:3:140555220738712 ENGINE_TRANSACTION_ID: 2071 THREAD_ID: 47 EVENT_ID: 27 OBJECT_SCHEMA: test OBJECT_NAME: lock_test PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140555220738712 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 4 rows in set (0.00 sec)
-. Mysql 8.0 용 Lock 조회 쿼리
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 |
+-----------+--------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
| 47 | update lock_test set t=200 where t=2 | test | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | GRANTED | 2 |
| 47 | update lock_test set t=200 where t=2 | test | lock_test | NULL | TABLE | IX | GRANTED | NULL |
| 49 | update test.lock_test set t=2000 where t=2 | test | lock_test | PRIMARY | RECORD | X,REC_NOT_GAP | WAITING | 2 |
| 49 | update test.lock_test set t=2000 where t=2 | test | lock_test | NULL | TABLE | IX | GRANTED | NULL |
+-----------+--------------------------------------------+---------------+-------------+------------+-----------+---------------+-------------+-----------+
4 rows in set (0.00 sec)