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)