-
Lock에 대해 알아보기: Gap Lock 2글또 2024. 8. 30. 00:37
0. 들어가며
이전 글에서 Gap Lock에 대해 알아보았습니다.
단순 INSERT 문은 Gap Lock을 걸지 않는다는 것을 알게 되었습니다.
다만, INSERT 문은 Gap에 대해 Insert Intention Lock을 건다는 사실도 확인했습니다.
Insert Intention Lock은 Gap Lock과 유사하지만, 범위가 겹치지 않는다면 INSERT가 가능한 Lock입니다.
공식 문서를 살펴보던 중, INSERT로 인해 Deadlock이 발생하는 예제를 발견했습니다.INSERT가 Gap Lock을 사용하지 않는다고 알고 있었는데, 어떤 이유로 문제가 발생했는지 궁금해졌습니다.
오늘은 공식 문서의 예시를 통해 이 문제를 이해해보도록 하겠습니다
💡 LOCK_MODE 데이터 확인
S, X : 넥스트 키 락
X,REC_NOT_GAP : 레코드 락
X,GAP : 갭 락
IX : 테이블 락(인텐트 락, X LOCK 생성시)
IS : 테이블 락(인텐트 락, S LOCK 생성시)글의 순서는 다음과 같습니다.
- Insert 시에 대한 Dead Lock이나는 경우가 있다.
- INSERT ... ON DUPLICATE KEY UPDATE
- PK Update
- 마무리
1. Insert 시에 대한 Dead Lock이나는 경우가 있다.
https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html MySQL 문서에 따르면, INSERT 시 중복 키 오류가 발생할 경우, S Lock(공유 잠금)을 요청하게 됩니다.
이로 인해 Deadlock이 발생할 수 있습니다.
이 예제를 실험하기 위해서는 세 개의 트랜잭션이 필요합니다.
첫 번째 트랜잭션이 INSERT를 실행하고, 나머지 두 개의 트랜잭션이 연이어 INSERT를 시도합니다.
이때 뒤에 실행된 트랜잭션들은 중복 키 오류로 인해 S Lock을 요청하게 됩니다.
이후 첫 번째 트랜잭션이 ROLLBACK을 수행하면, 나머지 트랜잭션들은 S Lock이 부여된 상태에서 Insert Intention Lock을 요청하게 됩니다.
그러나 각 트랜잭션이 서로의 Lock을 대기하게 되면서 Deadlock이 발생하게 됩니다.
특이한 점은, 대기 상태에서는 REC_NOT_GAP 상태로 있다가 ROLLBACK 등으로 인해 선점 트랜잭션이 Lock을 해제할 경우, Gap Lock이 부여된다는 것입니다.
이제 예제를 통해 자세히 살펴보겠습니다.
예제
CREATE TABLE tb_gaplock ( id INT NOT NULL, name VARCHAR(50) DEFAULT NULL, num INT DEFAULT NULL, PRIMARY KEY (id), INDEX idx_name (name), INDEX idx_num (num) ); INSERT INTO tb_gaplock VALUES (1, 'Matt', 1), (3, 'Esther', 3), (6, 'Peter', 6), (15, '15', 15), (20, '20', 20);
1-1. Rollback 시
첫 번째 트랜잭션이 ROLLBACK을 수행하면, 나머지 트랜잭션들 사이에서 Deadlock이 발생할 수 있습니다.
Deadlock이 감지되면, 하나의 트랜잭션은 실패하고, 다른 하나는 Lock이 부여된 후에 쿼리가 실행됩니다.
Seq A B C 1 mysql> insert into tb_gaplock Values (2, 'Matt2', 1);
Query OK, 1 row affected (0.00 sec)2 mysql> insert into tb_gaplock Values (2, 'Matt2', 1)
;
wait;3 mysql> insert into tb_gaplock Values (2, 'Matt2', 1)
;
wait;4 rollback; 5 Query OK, 1 row affected (22.69 sec) ERROR 1213 (40001): Deadlock found when trying to g
et lock; try restarting transaction
더보기1번까지 실행 후 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6948 THREAD_ID: 49 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL 1 row in set (0.00 sec) 2번까지 실행 후 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6953 THREAD_ID: 50 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6953 THREAD_ID: 50 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6948 THREAD_ID: 49 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:2:4:53:140636422463296 ENGINE_TRANSACTION_ID: 6948 THREAD_ID: 50 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422463296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 4 rows in set (0.00 sec) 3번까지 실행 후 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:1068:140636422478176 ENGINE_TRANSACTION_ID: 6954 THREAD_ID: 51 EVENT_ID: 27 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422478176 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:53:140636422475264 ENGINE_TRANSACTION_ID: 6954 THREAD_ID: 51 EVENT_ID: 27 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475264 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6953 THREAD_ID: 50 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6953 THREAD_ID: 50 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6948 THREAD_ID: 49 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:2:4:53:140636422463296 ENGINE_TRANSACTION_ID: 6948 THREAD_ID: 50 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422463296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 6 rows in set (0.00 sec) 5번까지 실행 후 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:1068:140636422478176 ENGINE_TRANSACTION_ID: 6954 THREAD_ID: 51 EVENT_ID: 27 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422478176 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:49:140636422475608 ENGINE_TRANSACTION_ID: 6954 THREAD_ID: 49 EVENT_ID: 29 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475608 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:53:140636422475608 ENGINE_TRANSACTION_ID: 6954 THREAD_ID: 49 EVENT_ID: 29 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475608 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:49:140636422475952 ENGINE_TRANSACTION_ID: 6954 THREAD_ID: 51 EVENT_ID: 27 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475952 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: GRANTED LOCK_DATA: 3 4 rows in set (0.00 sec)1-2. Commit 시
커밋이 이루어지면, 나머지 세션들은 모두 Duplicate Key 에러가 발생하게 됩니다.
커밋 후에 다른 트랜잭션들이 S Lock을 획득한 상태에서 X Lock을 획득하기 전에, 제약 조건을 검사하는 과정에서 Duplicate Key 에러가 발생합니다.
에러가 발생하므로 Insert Intention Lock을 요청하지 않게 되며, 이로 인해 Deadlock이 발생하지 않습니다.
A B C 1 mysql> insert into tb_gaplock Values (2, 'Matt2', 1);
Query OK, 1 row affected (0.00 sec)2 mysql> insert into tb_gaplock Values (2, 'Matt2', 1);
wait;3 mysql> insert into tb_gaplock Values (2, 'Matt2', 1)
;
wait;4 commit; 5 ERROR 1062 (23000): Duplicate entry '2' for ke
y 'tb_gaplock.PRIMARY'ERROR 1062 (23000): Duplicate entry '2' for ke
y 'tb_gaplock.PRIMARY'6 더보기3까지 실행 후 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:1068:140636422478176 ENGINE_TRANSACTION_ID: 6961 THREAD_ID: 51 EVENT_ID: 31 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422478176 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:53:140636422475264 ENGINE_TRANSACTION_ID: 6961 THREAD_ID: 51 EVENT_ID: 31 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475264 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6960 THREAD_ID: 50 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6960 THREAD_ID: 50 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6955 THREAD_ID: 49 EVENT_ID: 33 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:2:4:53:140636422463296 ENGINE_TRANSACTION_ID: 6955 THREAD_ID: 50 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422463296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 6 rows in set (0.00 sec) 5까지 실행 후 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:1068:140636422478176 ENGINE_TRANSACTION_ID: 6961 THREAD_ID: 51 EVENT_ID: 31 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422478176 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:53:140636422475264 ENGINE_TRANSACTION_ID: 6961 THREAD_ID: 51 EVENT_ID: 31 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475264 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6960 THREAD_ID: 50 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6960 THREAD_ID: 50 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 4 rows in set (0.00 sec)
1-3. Delete 시
공식 문서에 따르면, Delete 시에도 유사한 현상을 마주할 수 있습니다.
Delete 트랜잭션이 Commit되면, 나머지 트랜잭션이 S Lock을 획득한 후 Insert Intention Lock을 획득하려고 시도하는 과정에서 Deadlock이 발생할 수 있습니다.
이러한 상황은 트랜잭션 간의 잠금 충돌로 인해 발생하며, Delete 이후의 트랜잭션들이 서로의 잠금을 기다리게 되면서 Deadlock이 형성됩니다.
A B C 1 mysql> delete from tb_gaplock where id = 1;
Query OK, 1 row affected (0.00 sec)2 mysql> insert into tb_gaplock Values (1, 'Matt2', 1)
;
wait;3 mysql> insert into tb_gaplock Values (1, 'Matt2', 1)
;
wait;4 commit; 5 Query OK, 1 row affected (22.69 sec) ERROR 1213 (40001): Deadlock found when trying to g
et lock; try restarting transaction6 더보기1번 실행 시 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6969 THREAD_ID: 49 EVENT_ID: 43 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:2:4:53:140636422463296 ENGINE_TRANSACTION_ID: 6969 THREAD_ID: 49 EVENT_ID: 43 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422463296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 2 rows in set (0.00 sec) 2번 실행 시 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6969 THREAD_ID: 49 EVENT_ID: 43 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:2:4:53:140636422463296 ENGINE_TRANSACTION_ID: 6969 THREAD_ID: 49 EVENT_ID: 43 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422463296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 4 rows in set (0.00 sec) 3번 실행 시 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:1068:140636422478176 ENGINE_TRANSACTION_ID: 6971 THREAD_ID: 51 EVENT_ID: 35 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422478176 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533696080:2:4:53:140636422475264 ENGINE_TRANSACTION_ID: 6971 THREAD_ID: 51 EVENT_ID: 35 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422475264 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:1068:140636422466208 ENGINE_TRANSACTION_ID: 6969 THREAD_ID: 49 EVENT_ID: 43 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422466208 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533694464:2:4:53:140636422463296 ENGINE_TRANSACTION_ID: 6969 THREAD_ID: 49 EVENT_ID: 43 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422463296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 6 rows in set (0.00 sec) 5번 실행 시 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:1068:140636422472192 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140636422472192 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469280 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469280 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140636533695272:2:4:53:140636422469624 ENGINE_TRANSACTION_ID: 6970 THREAD_ID: 50 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140636422469624 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 21-4. Update만 실행
Update의 경우, S Lock을 획득하려 하지 않기 때문에 Deadlock이 발생하지 않습니다.
또한, Update는 Duplicate Key 제약 조건을 검사하지 않으므로 Duplicate Key 오류도 발생하지 않습니다.
Seq A B C 1 mysql> update tb_gaplock set name = 'msss' where id
= 3;
Query OK, 1 row affected (0.00 sec)2 mysql> update tb_gaplock set name = 'msss' where id = 3;
wait;3 mysql> update tb_gaplock set name = 'msss' where id = 3;
wait;4 rollback; or commit; 5 Query OK, 1 row affected (22.69 sec) 6 rollback; or commit; 7 Query OK, 1 row affected (22.69 sec) 더보기3번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139653971959000:1076:139653881582288 ENGINE_TRANSACTION_ID: 4964 THREAD_ID: 59 EVENT_ID: 45 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139653881582288 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139653971959000:10:4:10:139653881579296 ENGINE_TRANSACTION_ID: 4964 THREAD_ID: 59 EVENT_ID: 45 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139653881579296 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139653971960616:1076:139653881594368 ENGINE_TRANSACTION_ID: 4963 THREAD_ID: 57 EVENT_ID: 46 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139653881594368 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139653971960616:10:4:10:139653881591456 ENGINE_TRANSACTION_ID: 4963 THREAD_ID: 57 EVENT_ID: 46 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139653881591456 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 3 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139653971959808:1076:139653881588384 ENGINE_TRANSACTION_ID: 4958 THREAD_ID: 58 EVENT_ID: 49 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139653881588384 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139653971959808:10:4:10:139653881585472 ENGINE_TRANSACTION_ID: 4958 THREAD_ID: 58 EVENT_ID: 49 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139653881585472 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 6 rows in set (0.00 sec)
2. INSERT ... ON DUPLICATE KEY UPDATE
https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html INSERT ... ON DUPLICATE KEY UPDATE 명령어는 Duplicate Key 오류가 발생할 경우, S Lock 대신 X Lock을 획득하려고 합니다.
Primary Key에 대해서는 Record Lock을, Unique Key에 대해서는 Next-Key Lock을 획득하게 됩니다.
이를 바탕으로 COMMIT과 ROLLBACK 시 발생할 수 있는 상황을 실습해본 결과는 다음과 같습니다.
2-1. Insert ... On Duplicate Key Update
A B C 1 mysql> insert into tb_gaplock Values (2, 'Matt2', 1, 1) on duplicate key update name = 'Matt2';
Query OK, 1 row affected (0.00 sec)2 mysql> insert into tb_gaplock Values (2, 'Matt2', 1, 1) on duplicate key update name = 'Matt2';
wait;3 mysql> insert into tb_gaplock Values (2, 'Matt2', 1, 1) on duplicate key update name = 'Matt2';
;
wait;4 rollback; 5 Query OK, 1 row affected (24.97 sec) ERROR 1213 (40001): Deadlock found when trying to get lock; try
restarting transaction
show inodb status ROLLBACK 시 Deadlock 발생
- A 트랜잭션이 공유 자원에 대해 X Lock을 선점합니다.
- B와 C 트랜잭션은 X, REC_NOT_GAP Lock을 요청합니다.
- A 트랜잭션이 ROLLBACK을 수행합니다.
- 이로 인해 B와 C 트랜잭션은 X Gap Lock을 부여받습니다.
- B와 C 트랜잭션은 서로 Insert Intention Lock을 요청하게 되며, 이로 인해 Deadlock이 발생합니다.
여기도 동일하게 처음 Lock 요청은 REC_NOT_GAP을 요청하지만 Rollback이 이뤄지면 Gap Lock을 부여받는 것을 알 수 있습니다.
COMMIT 시 Deadlock 회피
반면, COMMIT을 할 경우, 트랜잭션들은 차례로 Lock을 획득하며 쿼리를 실행하게 됩니다.
이 상황에서는 기본적인 INSERT와 달리 Duplicate Key 오류가 발생하지 않으며, Duplicate Key UPDATE가 진행되면서 트랜잭션들이 순차적으로 대기하며 UPDATE를 실행하게 됩니다.
더보기1번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:1068:140132904653520 ENGINE_TRANSACTION_ID: 7448 THREAD_ID: 48 EVENT_ID: 29 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904653520 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL 1 row in set (0.01 sec) 2번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:1068:140132904659616 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904659616 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:2:4:53:140132904656704 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904656704 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:1068:140132904653520 ENGINE_TRANSACTION_ID: 7448 THREAD_ID: 48 EVENT_ID: 29 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904653520 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:2:4:53:140132904650528 ENGINE_TRANSACTION_ID: 7448 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904650528 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 4 rows in set (0.00 sec) 3번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982883112:1068:140132904665600 ENGINE_TRANSACTION_ID: 7454 THREAD_ID: 52 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904665600 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982883112:2:4:53:140132904662688 ENGINE_TRANSACTION_ID: 7454 THREAD_ID: 52 EVENT_ID: 24 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904662688 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:1068:140132904659616 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904659616 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:2:4:53:140132904656704 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904656704 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:1068:140132904653520 ENGINE_TRANSACTION_ID: 7448 THREAD_ID: 48 EVENT_ID: 29 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904653520 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:2:4:53:140132904650528 ENGINE_TRANSACTION_ID: 7448 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904650528 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 6 rows in set (0.00 sec) 4번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:1068:140132904659616 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140132904659616 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:2:4:49:140132904657048 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 48 EVENT_ID: 30 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904657048 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:2:4:53:140132904657048 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 48 EVENT_ID: 30 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904657048 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:2:4:49:140132904657392 ENGINE_TRANSACTION_ID: 7453 THREAD_ID: 49 EVENT_ID: 26 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140132904657392 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: GRANTED LOCK_DATA: 3 4 rows in set (0.00 sec)3. PK Update
Update 대상이 UK(Unique Key)나 PK(Primary Key)일 때도 Gap Lock이 발생하는지 궁금했습니다.
실험 결과, UK가 업데이트될 때는 Gap Lock이 발생하지 않았습니다.
그러나 PK를 업데이트할 경우, UK 범위에 대해 Gap Lock이 발생했습니다.
다만, UK가 아닌 다른 인덱스에 대해서는 이러한 현상이 나타나지 않았습니다.
정확한 원인은 파악하지 못했습니다.
PK가 수정되는 일이 흔치 않지만, 이런 경우도 있다는 것을 알고 있는 것이 나쁠 것은 없다고 생각합니다.
아래의 질문글에서는 Delete와 Insert를 통해서 같은 현상을 볼 수 있습니다.
Pk Update
Seq A B 1 mysql> update users set id = 8 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 02 mysql> insert into users values (15, 171, 'ss');
Query OK, 1 row affected (0.00 sec)3 mysql> insert into users values (16, 168, 'ss');
wait..4 mysql> insert into users values (16, 99, 'ss');
wait..더보기1번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:1075:140177196503760 ENGINE_TRANSACTION_ID: 9526 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140177196503760 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:4:2:140177196500768 ENGINE_TRANSACTION_ID: 9526 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140177196500768 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:5:2:140177196501112 ENGINE_TRANSACTION_ID: 9526 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk OBJECT_INSTANCE_BEGIN: 140177196501112 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 100, 1 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:5:2:140177196501456 ENGINE_TRANSACTION_ID: 9526 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk OBJECT_INSTANCE_BEGIN: 140177196501456 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 100, 1 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:5:4:140177196501456 ENGINE_TRANSACTION_ID: 9526 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk OBJECT_INSTANCE_BEGIN: 140177196501456 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 100, 8 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:5:6:140177196501456 ENGINE_TRANSACTION_ID: 9526 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk OBJECT_INSTANCE_BEGIN: 140177196501456 LOCK_TYPE: RECORD LOCK_MODE: S,GAP LOCK_STATUS: GRANTED LOCK_DATA: 170, 3 6 rows in set (0.00 sec)Uk Update
Seq A 1 update users set uk = 170 where uk = 150;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0더보기mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:1075:140177196503760 ENGINE_TRANSACTION_ID: 9524 THREAD_ID: 47 EVENT_ID: 44 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140177196503760 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:5:4:140177196500768 ENGINE_TRANSACTION_ID: 9524 THREAD_ID: 47 EVENT_ID: 44 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: uk OBJECT_INSTANCE_BEGIN: 140177196500768 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 150, 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140177290968280:9:4:16:140177196501112 ENGINE_TRANSACTION_ID: 9524 THREAD_ID: 47 EVENT_ID: 44 OBJECT_SCHEMA: sample OBJECT_NAME: users PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140177196501112 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 3 rows in set (0.00 sec)4. 마무리
Insert 시 Duplicate Key 에러가 발생하면 S, X lock이 요청되는 것을 확인했습니다.
하지만, 요청 시에는 REC_NOT_GAP Lock으로 시작되다가, Rollback 후에 잠금이 해제되면 Gap Lock으로 변경되는 이유는 아직 명확히 파악하지 못했습니다.
또한 Pk Update 시 Uk에만 Gap Lock이 걸리는 이유또한 불명확합니다.
의도치 않은 잠금이 많고 관련 문서를 찾기가 어렵습니다..
이런 Lock들로 인해서 동시성 성능이 저하되고 Dead Lock이 발생할 가능성도 있습니다.
Dead Lock은 심한 버그라기 보다는, 이를 대비해 복구 메커니즘을 잘 준비하는 것이 더 나은선택입니다.
https://github.com/mysql/mysql-server/blob/f8cdce86448a211511e8a039c62580ae16cb96f5/storage/innobase/row/row0ins.cc#L1930 Insert 시 Duplicate Key 에러가 발생하면 S, X Lock을 걸어야 하는 이유는 중복 키 오류가 발생하는 상황에서 예상치 못한 결과를 방지하기 위해서로 생각됩니다.
중복 키 오류가 발생하는 INSERT 쿼리를 실행할 때, 해당 INSERT를 동일한 오류로 다시 시도하면 실패할 것으로 예상됩니다.
그러나, 다른 세션이 충돌을 일으킨 행을 업데이트하여 고유 값을 변경한다면 상황이 달라집니다.
이 경우, INSERT를 다시 시도하면 성공할 수도 있습니다. 이는 예상치 못한 결과로, 데이터의 무결성에 영향을 미칠 수 있습니다.
이를 방지하기 위해서 Duplicate Key에러 시 잠금을 획득한다고 가설을 생각해볼 수 있습니다.
지난 글에 이어서 계속해서 Lock을 공부하고 있는데 다음에는 Foreign key로 인해 발생하는 Lock을 살펴보겠습니다.
- https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html
- https://gisungcu.tistory.com/647
- https://singun.github.io/2019/03/10/mysql-innodb-locking/
- https://bugs.mysql.com/bug.php?id=52020
- https://stackoverflow.com/questions/48393996/performance-of-insert-on-duplicate-key-update-compared-to-update
- https://stackoverflow.com/questions/51649959/mysql-5-7-deadlock-on-rollback-but-not-on-commit-when-using-on-duplicate-key
- https://velog.io/@woodyn1002/%EA%B8%B0%EC%B4%88-%EA%B3%B5%EB%B6%80-MySQL-%EC%A0%95%EB%A6%AC#sql%EB%AC%B8%EC%97%90-%EC%9D%98%ED%95%9C-lock%EB%93%A4
- https://stackoverflow.com/questions/63270714/why-mysql-innodb-set-an-s-or-x-next-key-lock-on-the-duplicate-index-record-when
- https://stackoverflow.com/questions/23234449/why-does-update-of-primary-key-lock-on-unique-key-in-innodb
'글또' 카테고리의 다른 글
HikariCP와 Slow Query로 인한 Commit 성공과 Rollback 실패 (0) 2024.09.19 Lock에 대해 알아보기: Gap Lock (0) 2024.09.01 재고 정합성이 걱정되어서 써보는 글(MVCC) (3) 2024.08.21 Galera Cluster 알아보기 (0) 2024.06.19 IP White List: 보안 취약점과 강화 방안 (0) 2024.06.19