-
Lock에 대해 알아보기: Gap Lock글또 2024. 9. 1. 13:55
0. 들어가며
안녕하세요.
오늘은 Gap Lock에 대해 알아보려고 합니다.
Gap Lock에 관심을 갖게 된 이유는 재고 관련 Locking을 공부하다가 궁금한 점이 생겼기 때문입니다.
재고 정합성을 위해 SELECT FOR UPDATE를 사용하고 있었는데, 이에 대해 참고 자료를 살펴보던 중 SELECT FOR UPDATE를 사용할 때 발생할 수 있는 Deadlock에 관한 설명을 보았습니다.
이 Deadlock은 단순한 자원 경합에 의한 것이 아니라, MySQL의 고유한 Lock 특성으로 인해 발생하는 것이었습니다.
그래서 이 문제의 원인인 Gap Lock에 대해 더 깊이 알아보고 공부해 보려고 합니다.
- Gap Lock 이란 무엇인가?
- Gap Lock 의 필요이유는 무엇인가?
- PK, UK
- 예제
- 당근 글 분석 1
- 당근 글 분석 2
- Pk, Uk가 업데이트 대상이 되면 Gap Lock이 걸릴까?
- 마무리
1. Gap Lock이란 무엇인가?
https://medium.com/daangn/mysql-gap-lock-%EB%8B%A4%EC%8B%9C%EB%B3%B4%EA%B8%B0-7f47ea3f68bc Gap Lock은 Index 사이의 빈 공간에 대해 Lock을 걸어, 해당 공간에 새로운 데이터가 INSERT 되지 못하도록 하는 Lock 메커니즘입니다.
2. Gap Lock의 필요 이유는 무엇인가?
단순히 Gap Lock이 무엇인지 알게 되었습니다.
하지만 왜 Gap Lock이 필요할까요? 이 질문을 해결하기 위해 먼저 Gap Lock이 언제 걸리는지를 알아보겠습니다.
- Gap Lock이 걸리는 SQL
- Locking Read (SELECT FOR UPDATE, SELECT FOR SHARE), UPDATE, DELETE
- INSERT INTO SELECT ~, UPDATE WHERE SELECT... (DML + SubQuery)
- Secondary Index를 사용하는 경우
- 범위 검색을 사용하는 경우
- Primary Key(PK) 또는 Unique Key(UK)를 사용하지만, 해당 키에 해당하는 값이 없는 Row를 조건으로 사용하는 경우
위의 쿼리와 조건들이 결합될 때 Gap Lock이 발생할 수 있습니다.
그럼 이 쿼리들과 행위들의 공통점은 무엇일까요?
바로 Gap Lock이 없을 경우, MVCC(Multi-Version Concurrency Control) 환경에서도 Phantom Read가 발생할 수 있는 쿼리라는 점입니다.
(사실 정확히 말하면 Phantom Read라고 부르기 어렵지만, WHERE 조건을 통해 다른 트랜잭션의 커밋 내용을 읽을 수 있다는 점에서 그렇게 표현했습니다. MVCC에서 DML 문장이 다른 트랜잭션의 커밋 내용을 읽는 것은 의도된 동작입니다.)
Seq A B 1 mysql> select * from tb_gaplock where id BETWEEN 1 AND 3 for update; 2 mysql> insert into tb_gaplock(id,name) values (2, ' Matt2 '); 3 COMMIT; 4 mysql> select * from tb_gaplock where id BETWEEN 1 AND 3 for update; Phantom Read 방지
예를 들어, 위와 같이 Locking Read를 사용할 때, 5번 SELECT 쿼리에서 새로운 Row인 "Matt2"를 읽게 됩니다.
Gap Lock을 사용하면 이러한 Phantom Read를 방지할 수 있는 것 입니다.
Replica 환경에서의 일관성 유지
추가적으로, Replica를 사용하는 환경에서 Source DB와 Replica DB 사이의 발생할 수 있는 부정합을 막기 위해서도 Gap Lock이 필요합니다.단점
Gap Lock에는 몇 가지 단점이 있습니다.
- 동시성 문제: Gap Lock을 사용하면 충돌이 없는 INSERT문도 동시에 처리되지 않을 수 있습니다. 이는 일관성과 동시성 사이에서 트레이드오프(trade-off)를 발생시킵니다. 동시성을 높여야 하는 서비스에서는 이러한 이유로 Read Committed 격리 수준을 선택할 수 있습니다.
- 데이터 양의 영향: 데이터가 적을수록 Gap Lock의 범위가 넓어질 수 있습니다. 예를 들어, 빈 테이블에 id가 30인 Row가 하나만 있고 이 Row를 Update한다고 하면, 30까지의 모든 간격이 Gap Lock으로 잠기게 됩니다. 즉, 데이터가 적을수록 Gap Lock의 범위가 상대적으로 넓어집니다.
3. PK와 UK
Primary Key(PK)와 Unique Key(UK)의 경우, Row에 해당 값이 존재하면 Gap Lock이 걸리지 않습니다.
범위 검색이 아닌 단일 값 비교(equal 비교)에서는, PK와 UK는 단일 값을 보장하기 때문에 중복 값이 들어올 가능성이 없어 Gap Lock을 사용할 필요가 없습니다.
그러나 값이 없을 경우에는 상황이 다릅니다.
해당 값이 없을 때는 X Lock을 걸 수 없기 때문에 PK나 UK를 사용하더라도 Gap Lock을 사용하여 Insert를 막아야 합니다.
따라서, 단일 값이 유지되는 PK나 UK의 경우에는 값이 존재할 때는 Gap Lock을 잡을 필요가 없지만, 값이 존재하지 않을 때는 Insert를 막기 위해 Gap Lock을 잡아야 합니다.
그래서 Secondary Index를 사용하는 경우에는 중복 값을 방지하기 위해 Gap Lock을 거는 것입니다.
4. 예제
이제부터 예제를 살펴보겠습니다.
- Secondary Index 사용 시 Gap Lock 발생 여부
- 문자형 index
- 정수형 index
- Primary Key(PK)와 Unique Key(UK) 값이 있을 때의 Gap Lock
- Equals(=) 비교
- 범위 비교
- PK, UK 값이 없을 때의 Gap Lock
- Equals(=) 비교
- 범위 비교
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);
💡 LOCK_MODE 데이터 확인
X : 넥스트 키 락
X,REC_NOT_GAP : 레코드 락
X,GAP : 갭 락
IX : 테이블 락(인텐트 락, X LOCK 생성시)
IS : 테이블 락(인텐트 락, S LOCK 생성시)
4-1. Secondary Index는 Gap Lock을 사용합니다.
문자형 index
Primary Key(PK)나 Unique Key(UK)가 아닌 Index를 사용할 때 Gap Lock이 발생합니다.
예를 들어, name이 "Esther"인 레코드를 기준으로 잠금을 설정하면, "Esther"와 "Matt" 사이의 빈 간격이 잠깁니다. 이로 인해 "Esther" 이후부터 "Matt"까지의 값들은 새로 Insert 할 수 없습니다.
이 경우, "A"부터 "M"까지의 값은 Gap Lock에 의해 Insert가 차단되며, 문자열 정렬 상 "Matt" 다음의 값부터는 Insert가 가능합니다.
Seq A B 1 mysql> select * from tb_gaplock where name = 'Esther' for update;
+----+--------+------+
| id | name | num |
+----+--------+------+
| 3 | Esther | 3 |
+----+--------+------+
1 row in set (0.01 sec)2 mysql> insert into tb_gaplock(id,name,num) values (1~,'A',21);
~~
mysql> insert into tb_gaplock(id,name,num) values (1~,'M',21);
Wait..mysql> insert into tb_gaplock values (21,'O',1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_gaplock(id,name,num) values (5,'Matt',21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_gaplock(id,name,num) values (2,'P',21);
Query OK, 1 row affected (0.00 sec)
mysql> insert into tb_gaplock(id,name,num) values (4,'Mb',21);
Query OK, 1 row affected (0.00 sec)더보기mysql> select * from tb_gaplock where name = 'Esther' for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:1068:140175384564432 ENGINE_TRANSACTION_ID: 4413 THREAD_ID: 48 EVENT_ID: 55 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140175384564432 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:2:5:2:140175384561440 ENGINE_TRANSACTION_ID: 4413 THREAD_ID: 48 EVENT_ID: 55 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_name OBJECT_INSTANCE_BEGIN: 140175384561440 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 'Esther', 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:2:4:11:140175384561784 ENGINE_TRANSACTION_ID: 4413 THREAD_ID: 48 EVENT_ID: 55 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140175384561784 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:2:5:3:140175384562128 ENGINE_TRANSACTION_ID: 4413 THREAD_ID: 48 EVENT_ID: 55 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_name OBJECT_INSTANCE_BEGIN: 140175384562128 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 'Matt', 1 4 rows in set (0.00 sec)
정수형 Index
문자열 Index와 달리, 정수형 Index를 사용할 때 Gap Lock의 범위를 보다 쉽게 예측할 수 있습니다.
예를 들어, 15에서 20까지의 값을 Gap Lock으로 잠그는 것을 확인할 수 있습니다.
Seq A B 1 mysql> select * from tb_gaplock where num = '15' for update;
+----+------+------+
| id | name | num |
+----+------+------+
| 15 | 15 | 15 |
+----+------+------+
1 row in set (0.00 sec)2 mysql> insert into tb_gaplock values (18,'Pee', 16);
wait..3 mysql> insert into tb_gaplock values (18,'Pee', 18);
wait..4 mysql> insert into tb_gaplock values (18,'Pee', 21);
Query OK, 1 row affected (0.00 sec)더보기1까지 실행 후 select * from tb_gaplock where num = '15' for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:1068:140175384564432 ENGINE_TRANSACTION_ID: 4385 THREAD_ID: 48 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140175384564432 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:2:6:10:140175384561440 ENGINE_TRANSACTION_ID: 4385 THREAD_ID: 48 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_num OBJECT_INSTANCE_BEGIN: 140175384561440 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 15, 15 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:2:4:13:140175384561784 ENGINE_TRANSACTION_ID: 4385 THREAD_ID: 48 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140175384561784 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 15 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140175486078168:2:6:11:140175384562128 ENGINE_TRANSACTION_ID: 4385 THREAD_ID: 48 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: idx_num OBJECT_INSTANCE_BEGIN: 140175384562128 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 20, 20 4 rows in set (0.00 sec)
4-2. PK, UK 값이 있을 경우의 Gap Lock 동작
Equals 비교
PK와 UK를 사용하는 단일 값 비교에서는 Gap Lock이 사용되지 않습니다.
따라서 해당 값의 앞뒤로 새로운 값을 Insert할 수 있습니다.
Seq A B 1 mysql> select * from tb_gaplock where id = '3'
for update;
+----+--------+------+
| id | name | num |
+----+--------+------+
| 3 | Esther | 3 |
+----+--------+------+
1 row in set (0.00 sec)2 mysql> insert into tb_gaplock(id,name,num) values (2,'peee',21);
Query OK, 1 row affected (0.00 sec)3 mysql> insert into tb_gaplock(id,name,num) values (4,'peee',21);
Query OK, 1 row affected (0.02 sec)더보기mysql> select * from tb_gaplock where id = '3' for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5407 THREAD_ID: 48 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:13:140513143473952 ENGINE_TRANSACTION_ID: 5407 THREAD_ID: 48 EVENT_ID: 28 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 2 rows in set (0.00 sec)
범위 비교
범위 비교를 수행할 때는 Gap Lock이 사용됩니다.
예를 들어, 6부터 15까지와 20까지의 값을 잠그는 것을 확인할 수 있습니다.
범위 검색에서는 Gap Lock을 적용하여 특정 범위 내의 값들을 차단하고 새로운 값의 Insert를 방지합니다.
Seq A B 1 mysql> select * from tb_gaplock where 6 <id an
d id < 16 for update;
+----+------+------+
| id | name | num |
+----+------+------+
| 15 | 15 | 15 |
+----+------+------+
1 row in set (0.01 sec)2 mysql> insert into tb_gaplock values (4, 'sss',1);
Query OK, 1 row affected (0.00 sec)3 mysql> insert into tb_gaplock values (7, 'sss',1);
wait..4 mysql> insert into tb_gaplock values (17, 'sss',1);
wait..5 mysql> insert into tb_gaplock values (21, 'sss',1);
Query OK, 1 row affected (0.00 sec)더보기mysql> select * from tb_gaplock where 6 <id and id < 16 for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140319240580312:1068:140319131751120 ENGINE_TRANSACTION_ID: 2380 THREAD_ID: 49 EVENT_ID: 78 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140319131751120 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140319240580312:2:4:5:140319131748128 ENGINE_TRANSACTION_ID: 2380 THREAD_ID: 49 EVENT_ID: 78 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140319131748128 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 15 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140319240580312:2:4:6:140319131748472 ENGINE_TRANSACTION_ID: 2380 THREAD_ID: 49 EVENT_ID: 78 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140319131748472 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 20 6~15~20 잠근다
4-3. PK, UK 값이 없을 경우의 Gap Lock 동작
Equals 비교
값이 존재하지 않는 조건을 사용할 경우 Gap Lock이 적용됩니다.
예를 들어, 3부터 6 사이의 값을 잠그는 경우를 들 수 있습니다.
PK나 UK가 없을 때도 값이 없으면 Gap Lock이 사용되어, 해당 범위 내의 새로운 Insert가 차단됩니다.
Seq A B 1 mysql> select * from tb_gaplock where id = '4'
for update;
Empty set (0.00 sec)2 insert into tb_gaplock values (2, 'sss',1);
Query OK, 1 row affected (0.01 sec)3 mysql> insert into tb_gaplock values (4, 'sss',1);
wait4 mysql> insert into tb_gaplock values (7, 'sss',1);
Query OK, 1 row affected (0.00 sec)더보기mysql> select * from tb_gaplock where id = '4' for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5414 THREAD_ID: 48 EVENT_ID: 36 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:4:140513143473952 ENGINE_TRANSACTION_ID: 5414 THREAD_ID: 48 EVENT_ID: 36 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 6 2 rows in set (0.00 sec)
범위 비교
범위 비교를 수행하면 Gap Lock이 적용됩니다.
예를 들어, 6부터 15까지의 범위를 잠그는 경우를 확인할 수 있습니다.
이 경우 Gap Lock이 해당 범위에 적용되어, 범위 내의 값들이 차단되고 새로운 값의 Insert가 방지됩니다.
Seq A B 1 mysql> select * from tb_gaplock where 6 <id an
d id < 12 for update;
Empty set (0.00 sec)2 mysql> insert into tb_gaplock values (4, 'sss',1);
Query OK, 1 row affected (0.00 sec)3 mysql> insert into tb_gaplock values (7, 'sss',1);
wait..더보기mysql> select * from tb_gaplock where 6 <id and id < 12 for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5417 THREAD_ID: 48 EVENT_ID: 45 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:5:140513143473952 ENGINE_TRANSACTION_ID: 5417 THREAD_ID: 48 EVENT_ID: 45 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 15 2 rows in set (0.00 sec)
5. 당근 글 분석 1MySQL Gap Lock 다시보기
우리가 일반적으로 알고 있는 데이터베이스 서버의 잠금(Lock)은 레코드 자체에 대한 잠금(Record Lock)이에요. 어떤 트랜잭션에서 레코드를 변경하기 위해서는 그 레코드를 잠그고, 그 동안은 다른
medium.com
Insert Intention Gap Lock
먼저, Insert Intention Gap Lock에 대해 알아보겠습니다.
INSERT SQL은 기본적으로 Gap Lock을 걸지 않습니다.
대신, Insert Intention Gap Lock이라는 특수한 형태의 Gap Lock을 사용합니다.
이 Lock은 범위에 대해 적용되지만, 범위에 대해서는 Insert가 가능합니다.
예제
당근 예제에서는 Gap Lock에 의해서 흔히 발생할 수 있는 데드락을 소개했습니다.
Seq A B 1 mysql> select * from tb_gaplock where id = 2 for update;
Empty set (0.00 sec)2 mysql> select * from tb_gaplock where id = 2 for update;
Empty set (0.00 sec)3 mysql> insert into tb_gaplock(id,name) values
(2,'Matt2');
wait..4 mysql> insert into tb_gaplock(id,name) values (2,'Matt2');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction5 Query OK, 1 row affected (6.55 sec) https://stackoverflow.com/questions/65606686/is-insert-intention-lock-a-special-type-of-gap-lock-and-can-co-exist 기본 예제: PK 값이 없을 때의 Gap Lock 동작을 다룹니다.
- Select for Update를 실행할 때, 값이 없으므로 Gap Lock이 적용됩니다. Gap Lock은 서로 허용되기 때문에, A와 B 트랜잭션은 서로 잠금 대기 없이 실행됩니다.
- Insert를 시도합니다.
- Insert는 Insert Intention Gap Lock을 획득하려고 시도하지만, 이 Lock은 Gap Lock과 호환되지 않기 때문에 B 트랜잭션의 Gap Lock을 기다리게 됩니다.
- B 트랜잭션에서 Insert를 시도할 때, Insert Intention Gap Lock을 얻으려고 하지만 A가 보유한 Gap Lock 때문에 대기합니다. 이때 데드락 감지로 인해 B는 롤백됩니다.
- A 트랜잭션의 Insert는 Lock을 획득하고 실행됩니다.
결국, Select for Update나 Update Where, Delete Where에서 PK를 사용해도 없는 행에 대해 Gap Lock이 적용된다는 것을 확인할 수 있습니다.
값이 없는 경우 Select for Update가 서로 잠금 대기 없이 실행될 수 있었던 이유는 Gap Lock이 S Lock이기 때문입니다.
이후 서로 Insert Intention Gap Lock를 획득하려하면서 데드락이 발생합니다.
더보기---------------------------------------1번까지 실행----------------------------------------- 1. a-mysql> select * from tb_gaplock where id = 2 for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5448 THREAD_ID: 48 EVENT_ID: 72 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:13:140513143473952 ENGINE_TRANSACTION_ID: 5448 THREAD_ID: 48 EVENT_ID: 72 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 2 rows in set (0.00 sec) ---------------------------------------2번까지 실행----------------------------------------- 2. b-mysql> select * from tb_gaplock where id = 2 for update; mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140492056039424:1068:140491937082016 ENGINE_TRANSACTION_ID: 2832 THREAD_ID: 48 EVENT_ID: 12 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140491937082016 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140492056039424:2:4:11:140491937079104 ENGINE_TRANSACTION_ID: 2832 THREAD_ID: 48 EVENT_ID: 12 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140491937079104 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140492056038616:1068:140491937075920 ENGINE_TRANSACTION_ID: 2831 THREAD_ID: 49 EVENT_ID: 14 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140491937075920 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140492056038616:2:4:11:140491937072928 ENGINE_TRANSACTION_ID: 2831 THREAD_ID: 49 EVENT_ID: 14 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140491937072928 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 4 rows in set (0.00 sec) ---------------------------------------3번까지 실행----------------------------------------- 3. a-mysql> insert into tb_gaplock(id,name) values (2,'Matt2'); waiting.. mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694499624:1068:140505627296256 ENGINE_TRANSACTION_ID: 4915 THREAD_ID: 48 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140505627296256 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694499624:2:4:11:140505627293344 ENGINE_TRANSACTION_ID: 4915 THREAD_ID: 48 EVENT_ID: 34 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140505627293344 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:1068:140505627290272 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140505627290272 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:2:4:11:140505627287360 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140505627287360 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:2:4:11:140505627287704 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140505627287704 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: WAITING LOCK_DATA: 3 5 rows in set (0.00 sec) ---------------------------------------4번까지 실행----------------------------------------- 4. b-mysql> insert into tb_gaplock(id,name) values (2,'Matt2'); ERROR 1213 (40001): Deadlock found when trying to get lock; try res tarting transaction 5.Query OK, 1 row affected (1.13 sec) mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:1068:140505627290272 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140505627290272 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:2:4:11:140505627287360 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140505627287360 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:2:4:37:140505627287360 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 47 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140505627287360 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140505694498816:2:4:11:140505627287704 ENGINE_TRANSACTION_ID: 4914 THREAD_ID: 47 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140505627287704 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: GRANTED LOCK_DATA: 3 4 rows in set (0.00 sec)
당근 예제 변형
LOCKING READ 없이 DELETE 쿼리 사용
- 값이 없는 경우 DELETE 쿼리를 사용할 때 Gap Lock이 적용됩니다.
- 이로 인해 INSERT 시 Insert Intention Lock 때문에 데드락이 발생합니다.
Seq A B 1 mysql> select * from tb_gaplock where id = 2;
Empty set (0.00 sec)2 mysql> select * from tb_gaplock where id = 2;
Empty set (0.00 sec)3 mysql> delete from tb_gaplock where id = 2;
Query OK, 0 rows affected (0.00 sec)4 mysql> delete from tb_gaplock where id = 2;
Query OK, 0 rows affected (0.00 sec)5 mysql> insert into tb_gaplock(id,name) values (2,'Matt2');
wait..6 insert into tb_gaplock(id,name) values (2,'Matt2');
ERROR 1213 (40001): Deadlock found when trying to get lock; try r
estarting transaction7 Query OK, 1 row affected (3.28 sec) 더보기a-mysql> delete from tb_gaplock where id = 2; Query OK, 0 rows affected (0.00 sec) b-mysql> delete from tb_gaplock where id = 2; Query OK, 0 rows affected (0.00 sec) -----------------------------------------4번까지 실행--------------------------------------- mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234137896:1076:140513143489024 ENGINE_TRANSACTION_ID: 5453 THREAD_ID: 49 EVENT_ID: 83 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143489024 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234137896:10:4:13:140513143486112 ENGINE_TRANSACTION_ID: 5453 THREAD_ID: 49 EVENT_ID: 83 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143486112 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5452 THREAD_ID: 48 EVENT_ID: 87 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:13:140513143473952 ENGINE_TRANSACTION_ID: 5452 THREAD_ID: 48 EVENT_ID: 87 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 4 rows in set (0.00 sec) -----------------------------------------5번까지 실행--------------------------------------- a-insert mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234137896:1076:140513143489024 ENGINE_TRANSACTION_ID: 5453 THREAD_ID: 49 EVENT_ID: 83 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143489024 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234137896:10:4:13:140513143486112 ENGINE_TRANSACTION_ID: 5453 THREAD_ID: 49 EVENT_ID: 83 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143486112 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5452 THREAD_ID: 48 EVENT_ID: 87 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:13:140513143473952 ENGINE_TRANSACTION_ID: 5452 THREAD_ID: 48 EVENT_ID: 87 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:13:140513143474296 ENGINE_TRANSACTION_ID: 5452 THREAD_ID: 48 EVENT_ID: 88 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143474296 LOCK_TYPE: RECORD LOCK_MODE: X,GAP,INSERT_INTENTION LOCK_STATUS: WAITING LOCK_DATA: 3 5 rows in set (0.01 sec)
SELECT 쿼리 사용하고 DELETE 쿼리가 없는 경우
- SELECT 쿼리를 사용하고 DELETE 쿼리가 없는 경우, 첫 번째 INSERT는 성공하고 다른 하나는 잠금 대기 상태에 있습니다.
- 이는 첫 번째 트랜잭션이 Record Lock을 얻었기 때문에 두 번째 트랜잭션이 대기하게 됩니다.
Seq A B 1 mysql> select * from tb_gaplock where id = 2;
Empty set (0.00 sec)2 mysql> select * from tb_gaplock where id = 2;
Empty set (0.00 sec)3 mysql> insert into tb_gaplock(id,name) values
(2,'Matt2');
Query OK, 1 row affected (0.00 sec)4 mysql> insert into tb_gaplock(id,name) values
(2,'Matt2');
wait더보기3번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5458 THREAD_ID: 48 EVENT_ID: 94 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL 1 row in set (0.00 sec) 4번까지 실행. mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234137896:1076:140513143489024 ENGINE_TRANSACTION_ID: 5459 THREAD_ID: 49 EVENT_ID: 90 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143489024 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234137896:10:4:16:140513143486112 ENGINE_TRANSACTION_ID: 5459 THREAD_ID: 49 EVENT_ID: 90 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143486112 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 2 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:1076:140513143476944 ENGINE_TRANSACTION_ID: 5458 THREAD_ID: 48 EVENT_ID: 94 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 140513143476944 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140513234136280:10:4:16:140513143473952 ENGINE_TRANSACTION_ID: 5458 THREAD_ID: 49 EVENT_ID: 90 OBJECT_SCHEMA: sample OBJECT_NAME: tb_gaplock PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 140513143473952 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 4 rows in set (0.00 sec)
6. 당근 글 분석 2
MySQL Gap Lock (두번째 이야기)
Why ?
medium.com
이외에도 테이블의 데이터 페이지에서 마지막 레코드를 조회할 때, Gap Lock의 범위에 supremum pseudo-record가 포함될 수 있습니다.
이는 해당 데이터 페이지의 최대 범위를 의미하므로, 데이터 페이지 끝에 대한 Insert가 차단되는 문제가 발생할 수 있습니다.
Index Record가 어느 데이터 페이지에 위치할지, 혹은 가장 마지막 레코드일지까지 예측하며 코딩하기 어렵기 때문에 Gap Lock은 까다로운 상황을 초래할 수 있습니다.
따라서 이러한 상황을 사전에 대비하기보다는, 발생할 수 있는 Deadlock에 대한 복구 방법을 마련하는 것이 더 현실적인 접근일 수 있습니다.
7. Pk, Uk가 업데이트 대상이 되면 Gap Lock이 걸릴까?
https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html Gap Lock이 Using Index에서 발생하는 것인지, 아니면 Update나 Insert 대상에서 발생하는 것인지 궁금합니다.
문서를 참고해보면, WHERE 조건에서 사용되는 조건을 기반으로 Gap Lock이 잡히는 것 같습니다.
그렇다면 PK(Primary Key)나 UK(Unique Key)가 업데이트 대상이 될 경우에도 Gap Lock이 발생할까요?
이 경우 Record Lock을 획득하려고 서로 기다리게 되며, Gap Lock은 발생하지 않습니다.
앞서 언급한 것처럼, Update 대상이 되는 것은 다른 트랜잭션에 Phantom Read를 일으킬 수 있지만, 현재 트랜잭션에서는 영향을 미치지 않기 때문이라고 생각됩니다.
다만, INSERT ON DUPLICATE KEY UPDATE에서는 Gap Lock이 대상에서 발생하는 것 같습니다. 이 부분은 추가적으로 확인해볼 예정입니다.
Seq A B C 1 update tb_gaplock set id = 17 where id = 15; 2 update tb_gaplock set id = 17 where id = 15;
wait ..3 update tb_gaplock set id = 17 where id = 15;
wait..4 rollback; 5 6 Query OK, 0 rows affected (0.01 sec);
rollback;7 Query OK, 0 rows affected (0.01 sec) 롤백이 아닌 커밋 시에는 낙관적 잠금이 되어 Affected Rows가 0이 될 수 있습니다.
더보기1번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:1068:140132904653520 ENGINE_TRANSACTION_ID: 7472 THREAD_ID: 48 EVENT_ID: 45 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 *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:2:4:13:140132904650528 ENGINE_TRANSACTION_ID: 7472 THREAD_ID: 48 EVENT_ID: 45 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: 15 2 rows in set (0.00 sec) 2번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:1068:140132904659616 ENGINE_TRANSACTION_ID: 7477 THREAD_ID: 49 EVENT_ID: 39 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:13:140132904656704 ENGINE_TRANSACTION_ID: 7477 THREAD_ID: 49 EVENT_ID: 39 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: 15 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:1068:140132904653520 ENGINE_TRANSACTION_ID: 7472 THREAD_ID: 48 EVENT_ID: 45 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:13:140132904650528 ENGINE_TRANSACTION_ID: 7472 THREAD_ID: 48 EVENT_ID: 45 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: 15 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: 7478 THREAD_ID: 52 EVENT_ID: 43 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:13:140132904662688 ENGINE_TRANSACTION_ID: 7478 THREAD_ID: 52 EVENT_ID: 43 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: 15 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:1068:140132904659616 ENGINE_TRANSACTION_ID: 7477 THREAD_ID: 49 EVENT_ID: 39 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:13:140132904656704 ENGINE_TRANSACTION_ID: 7477 THREAD_ID: 49 EVENT_ID: 39 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: 15 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982881496:1068:140132904653520 ENGINE_TRANSACTION_ID: 7472 THREAD_ID: 48 EVENT_ID: 45 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:13:140132904650528 ENGINE_TRANSACTION_ID: 7472 THREAD_ID: 48 EVENT_ID: 45 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: 15 6 rows in set (0.00 sec) 4번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982883112:1068:140132904665600 ENGINE_TRANSACTION_ID: 7478 THREAD_ID: 52 EVENT_ID: 43 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:13:140132904662688 ENGINE_TRANSACTION_ID: 7478 THREAD_ID: 52 EVENT_ID: 43 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: 15 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 140132982882304:1068:140132904659616 ENGINE_TRANSACTION_ID: 7477 THREAD_ID: 49 EVENT_ID: 39 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:13:140132904656704 ENGINE_TRANSACTION_ID: 7477 THREAD_ID: 49 EVENT_ID: 39 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: GRANTED LOCK_DATA: 15 4 rows in set (0.00 sec)
8. 마무리
MySQL은 데이터의 무결성을 보장하기 위해 다양한 Lock 메커니즘을 사용합니다.
우리가 명시적으로 설정한 Lock 외에도, MySQL 내부적으로 여러 Lock이 걸리며 이들은 복잡하게 상호작용합니다.
이러한 Lock 메커니즘을 이해하지 못하면 예기치 못한 Deadlock에 직면할 수 있습니다.
특히 Gap Lock은 Deadlock을 유발할 수 있으며, 동시성 저하를 초래할 수 있습니다.
이를 방지하기 위해 격리 수준을 조정할 수 있지만, 각 격리 수준의 장단점을 충분히 인지해야 합니다.
예를 들어, Read Committed 격리 수준으로 변경하면 Gap Lock으로 인한 Deadlock의 위험을 줄이고 성능을 향상시킬 수 있습니다.
그러나 이 경우 Phantom Read가 발생할 수 있으므로, 사내 충분한 협의가 필요합니다.
오늘 다룬 PK(Primary Key)와 UK(Unique Key) 값의 존재 여부에 따라 달라지는 Lock과 데이터 페이지에 따라 잠기는 Lock의 동작 또한 흥미로웠습니다.
할 수 있다면 Where 절에 Pk, Uk 기반으로 DML을 작성하는 것이 좋아보입니다.
추가로 앞에서 말했지만 Gap Lock을 예방하도록 쿼리를 짜는 것은 좋지만 모두 다 예방할 수는 없다고 생각합니다.
값이 없을 경우가 있기 때문입니다.
그래서 Deadlock에 대한 복구 방법을 마련하는 것이 더 현실적인 접근이라고 생각합니다.
다음 글에서는 INSERT 시 발생할 수 있는 Deadlock 상황에 대해 더 다뤄보겠습니다.
참고
- https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html
- https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html
- https://stackoverflow.com/questions/74972075/gap-locks-dont-appear-in-data-locks-table-whats-happening
- https://gisungcu.tistory.com/650
- https://medium.com/@im_zero/innodb-%EB%9D%BD-%ED%85%8C%EC%8A%A4%ED%8A%B8-ab712d19b74f
- https://pius712.tistory.com/5
- https://incheol-jung.gitbook.io/docs/q-and-a/spring/feat.-tmi
- https://dkswnkk.tistory.com/749
- https://taes-k.github.io/2020/05/17/mysql-transaction-lock/
'글또' 카테고리의 다른 글
글또10기 삶의 지도 (0) 2024.10.12 HikariCP와 Slow Query로 인한 Commit 성공과 Rollback 실패 (0) 2024.09.19 Lock에 대해 알아보기: Gap Lock 2 (0) 2024.08.30 재고 정합성이 걱정되어서 써보는 글(MVCC) (3) 2024.08.21 WMS 프로젝트 (0) 2024.07.18