Transaction Isolation Level에 따른 MySQL의 Lock 테스트
MySQL의 Transaction Isolation Level에 따라 같은 상황에 대해서도 MySQL Lock이 다르게 잡힐 수 있다. MySQL 5.7, 8.0 기준으로 각각 테스트 해보자.
MySQL 5.7
테스트 대상 버전: MySQL 5.7.35
사전 작업
auto commit 여부 확인
MySQL은 Default가 autocommit이 ON이다. 확인해본다.
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)rows 생성
CREATE TABLE `tx_isolation_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a_idx` (`a`)
) ENGINE=InnoDB;
## 데이터를 임의로 만들어 넣었다.
mysql> select * from tx_isolation_test;
+----+------+------+
| id | a | b |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 6 | 3 | 4 |
| 7 | 4 | 5 |
+----+------+------+
5 rows in set (0.00 sec)테스트 - case1# REPEATABLE-READ
Transaction Isolation Level 확인
mysql> set transaction_isolation='REPEATABLE-READ';
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ |
+---------------------------------+
1 row in set (0.00 sec)update 수행
update 시 autocommit 영향을 받지 않도록 별도 선언을 반드시 해준다.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tx_isolation_test set a=0 where b=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0상태 확인
------------
TRANSACTIONS
------------
Trx id counter 2267
Purge done for trx's n:o < 2267 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2262, ACTIVE 2 sec
2 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 28, OS thread handle 140036873004800, query id 468 local_test 10.0.2.2 root starting
show engine innodb status
TABLE LOCK table `blog`.`tx_isolation_test` trx id 2262 lock mode IX
RECORD LOCKS space id 30 page no 3 n bits 80 index PRIMARY of table `blog`.`tx_isolation_test` trx id 2262 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 0000000008ab; asc ;;
2: len 7; hex ee000001a60110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 0000000008ac; asc ;;
2: len 7; hex ef000001a70110; asc ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 0000000008b1; asc ;;
2: len 7; hex f2000001a80110; asc ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000002; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 6; hex 0000000008d6; asc ;;
2: len 7; hex 270000014908f2; asc ' I ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000004; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 0000000008b9; asc ;;
2: len 7; hex f8000001ac0110; asc ;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000005; asc ;;원복
# 테스트를 완료했으니 롤백해두자.
mysql> rollback;테스트 - case2# READ-COMMITTED
Transaction Isolation Level 확인
mysql> set transaction_isolation='READ-COMMITTED';
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-COMMITTED |
+---------------------------------+
1 row in set (0.00 sec)update 수행
update 시 autocommit 영향을 받지 않도록 별도 선언을 반드시 해준다.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tx_isolation_test set a=0 where b=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0상황 확인 비교
------------
TRANSACTIONS
------------
Trx id counter 2261
Purge done for trx's n:o < 2260 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2260, ACTIVE 12 sec
2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 28, OS thread handle 140036873004800, query id 461 local_test 10.0.2.2 root starting
show engine innodb status
TABLE LOCK table `blog`.`tx_isolation_test` trx id 2260 lock mode IX
RECORD LOCKS space id 30 page no 3 n bits 80 index PRIMARY of table `blog`.`tx_isolation_test` trx id 2260 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 6; hex 0000000008d4; asc ;;
2: len 7; hex 2600000148061c; asc & H ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000004; asc ;;원복
# 테스트를 완료했으니 롤백해두자.
mysql> rollback;MySQL :: MySQL 5.7 Reference Manual :: 14.7.1 InnoDB Locking
MySQL 8.0
테스트 대상 버전: MySQL 8.0.26
MySQL 8.0 부터는 performance_schema.data_locks 로 아래 내용을 좀 더 자세히 살펴볼 수 있겠다.
사전 작업
auto commit 여부 확인
MySQL은 Default가 autocommit이 ON이다. 확인해본다.
mysql> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
| 1 |
+----------------------+
1 row in set (0.00 sec)rows 생성
CREATE TABLE `tx_isolation_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a_idx` (`a`)
) ENGINE=InnoDB;
## 데이터를 임의로 만들어 넣었다.
mysql> select * from tx_isolation_test;
+----+------+------+
| id | a | b |
+----+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 2 | 2 |
| 6 | 3 | 4 |
| 7 | 4 | 5 |
+----+------+------+
5 rows in set (0.00 sec)테스트 - case1# REPEATABLE-READ
Transaction Isolation Level 확인
mysql> set transaction_isolation='REPEATABLE-READ';
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ |
+---------------------------------+
1 row in set (0.00 sec)update 수행
update 시 autocommit 영향을 받지 않도록 별도 선언을 반드시 해준다.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tx_isolation_test set a=0 where b=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0상황 확인
------------
TRANSACTIONS
------------
Trx id counter 4913
Purge done for trx's n:o < 4912 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421992337238448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421992337237640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 4912, ACTIVE 4 sec
2 lock struct(s), heap size 1128, 6 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140517309314816, query id 52 _gateway 10.0.2.2 root starting
show engine innodb status
TABLE LOCK table `blog`.`tx_isolation_test` trx id 4912 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 80 index PRIMARY of table `blog`.`tx_isolation_test` trx id 4912 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;;
1: len 6; hex 000000001313; asc ;;
2: len 7; hex 810000013e0110; asc > ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;;
1: len 6; hex 000000001314; asc ;;
2: len 7; hex 820000013d0110; asc = ;;
3: len 4; hex 80000001; asc ;;
4: len 4; hex 80000001; asc ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000003; asc ;;
1: len 6; hex 000000001319; asc ;;
2: len 7; hex 81000000850110; asc ;;
3: len 4; hex 80000002; asc ;;
4: len 4; hex 80000002; asc ;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 6; hex 000000001330; asc 0;;
2: len 7; hex 010000010d050f; asc ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000004; asc ;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000007; asc ;;
1: len 6; hex 00000000131d; asc ;;
2: len 7; hex 81000000870110; asc ;;
3: len 4; hex 80000004; asc ;;
4: len 4; hex 80000005; asc ;;mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:1072:140517371357856
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140517371357856
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:1:140517371354944
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:2:140517371354944
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:3:140517371354944
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:4:140517371354944
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 3
*************************** 6. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:7:140517371354944
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 6
*************************** 7. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:8:140517371354944
ENGINE_TRANSACTION_ID: 4912
THREAD_ID: 49
EVENT_ID: 69
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 7
7 rows in set (0.00 sec)원복
# 테스트를 완료했으니 롤백해두자.
mysql> rollback;테스트 - case2# READ-COMMITTED
Transaction Isolation Level 확인
mysql> set transaction_isolation='READ-COMMITTED';
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| READ-COMMITTED |
+---------------------------------+
1 row in set (0.00 sec)update 수행
update 시 autocommit 영향을 받지 않도록 별도 선언을 반드시 해준다.
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update tx_isolation_test set a=0 where b=4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0상황 확인
------------
TRANSACTIONS
------------
Trx id counter 4911
Purge done for trx's n:o < 4911 undo n:o < 0 state: running but idle
History list length 3
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421992337238448, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421992337237640, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 4906, ACTIVE 22 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 8, OS thread handle 140517309314816, query id 43 local_test 10.0.2.2 root starting
show engine innodb status
TABLE LOCK table `blog`.`tx_isolation_test` trx id 4906 lock mode IX
RECORD LOCKS space id 11 page no 4 n bits 80 index PRIMARY of table `blog`.`tx_isolation_test` trx id 4906 lock_mode X locks rec but not gap
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 6; hex 00000000132a; asc *;;
2: len 7; hex 0200000113044d; asc M;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000004; asc ;;mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:1072:140517371357856
ENGINE_TRANSACTION_ID: 4906
THREAD_ID: 49
EVENT_ID: 58
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140517371357856
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140517360528600:11:4:7:140517371354944
ENGINE_TRANSACTION_ID: 4906
THREAD_ID: 49
EVENT_ID: 58
OBJECT_SCHEMA: blog
OBJECT_NAME: tx_isolation_test
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140517371354944
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 6
2 rows in set (0.00 sec)원복
# 테스트를 완료했으니 롤백해두자.
mysql> rollback;MySQL :: MySQL 8.0 Reference Manual :: 15.7.1 InnoDB Locking
MySQL :: MySQL 8.0 Reference Manual :: 27.12.13.1 The data_locks Table
MySQL :: MySQL 8.0 Reference Manual :: 15.17.2 Enabling InnoDB Monitors
| 날짜 | 내용 |
|---|---|
| 2021.09.23 | 최초 글 작성 |