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)
