-
Lock에 대해 알아보기: Foreign key카테고리 없음 2024. 9. 5. 23:04
0. 들어가며
안녕하세요.
이번에는 Foreign Key로 인해 발생하는 Lock들에 대해 알아보겠습니다.
사실, 재고 관리와 관련된 Gap Lock을 공부하다가 이 주제까지 다다르게 되었습니다.
현업에서는 성능상의 이유로 Foreign Key를 사용하지 않는 경우가 있습니다.
데이터의 일관성보다는 성능상의 이점을 더 중시하는 선택입니다.
관리되는 테이블이 적거나, 데이터베이스 관리자가 있고 데이터 일관성을 충분히 유지할 수 있다고 판단하는 경우라고 생각됩니다.
(휴먼 에러는..)
그럼 이제 Foreign Key를 사용했을 때 발생하는 Lock에 대해서 알아보겠습니다.
- Foreign Key란
- Foreign Key 사용 시 걸리는 Lock
- 예제
- 데드락
- 마무리
1. Foreign Key란
Foreign Key란 두 테이블 간의 관계를 설정하는 데 사용되는 키를 말합니다.
Foreign Key가 포함된 테이블은 자식 테이블이라고 하며, Foreign Key 값을 제공하는 테이블을 부모 테이블이라고 합니다.
이를 통해 데이터베이스에서 참조 무결성을 유지할 수 있습니다.
"Foreign Key는 부모테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로인해 데드락이 발생할 수 있다. 그래서 실무에서는 잘 사용하지 않는다."
2. Foreign Key 사용 시 걸리는 Lock
https://dev.mysql.com/doc/refman/8.4/en/ansi-diff-foreign-keys.html Foreign Key 제약 조건을 확인할 때 관련된 테이블에 S Lock(공유 잠금)을 설정합니다.
이러한 잠금은 참조 무결성을 유지하기 위해 필요하며, 데이터의 일관성을 보장하는 역할을 합니다.
예를 들어, 자식 테이블에서 참조하는 부모 테이블의 레코드를 삭제하거나 업데이트할 때, 잠금이 걸리면서 다른 트랜잭션에서 해당 레코드에 대해 수정이 불가능해집니다.
즉, Foreign Key로 연결된 두 테이블 간의 참조 무결성을 유지하기 위해 S Lock이 설정될 수 있습니다.
3. 예제
CREATE TABLE parent ( id bigint not null primary key, name varchar(255) null ); CREATE TABLE child ( id bigint not null primary key, name varchar(255) null, parent_id bigint null, CONSTRAINT child_fk FOREIGN KEY (parent_id) REFERENCES parent (id) ); INSERT INTO parent (id, name) VALUES (1, 'Parent 1'); INSERT INTO parent (id, name) VALUES (2, 'Parent 2'); INSERT INTO parent (id, name) VALUES (3, 'Parent 3'); INSERT INTO child (id, name, parent_id) VALUES (1, 'Child 1', 1); INSERT INTO child (id, name, parent_id) VALUES (2, 'Child 2', 3);
3-1. Foreign Key를 WHERE 절에서 사용할 경우해당 부모 테이블에 S Lock(공유 잠금)은 걸리지 않습니다. 그러나, 이전에 설명한 것처럼 Secondary Index를 사용할 때는 Gap Lock이 발생할 수 있습니다
Seq A B 1 update child set name = 'ss' where parent_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 02 mysql> update parent set name = 'ss' where id = 1;
Query OK, 1 row affected (0.00 sec)더보기1번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:1080:139901110637264 ENGINE_TRANSACTION_ID: 6963 THREAD_ID: 48 EVENT_ID: 37 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110637264 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:5:2:139901110634272 ENGINE_TRANSACTION_ID: 6963 THREAD_ID: 48 EVENT_ID: 37 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: child_fk OBJECT_INSTANCE_BEGIN: 139901110634272 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 1, 1 *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:4:2:139901110634616 ENGINE_TRANSACTION_ID: 6963 THREAD_ID: 48 EVENT_ID: 37 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110634616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:5:3:139901110634960 ENGINE_TRANSACTION_ID: 6963 THREAD_ID: 48 EVENT_ID: 37 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: child_fk OBJECT_INSTANCE_BEGIN: 139901110634960 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3, 2 4 rows in set (0.00 sec)3-2. 자식 테이블의 Foreign Key를 업데이트할 경우
Foreign Key를 업데이트할 경우, 부모 레코드에 S Lock이 설정되는 것을 확인할 수 있었습니다.
Seq A 1 mysql> update child set parent_id = 2 where parent_id = 1;
Query OK, 1 row affected (0.01 sec)더보기1번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:1079:139901110637352 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110637352 LOCK_TYPE: TABLE LOCK_MODE: IS LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:1080:139901110637264 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110637264 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:5:2:139901110634272 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: child_fk OBJECT_INSTANCE_BEGIN: 139901110634272 LOCK_TYPE: RECORD LOCK_MODE: X LOCK_STATUS: GRANTED LOCK_DATA: 1, 1 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:4:4:139901110634616 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110634616 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:5:3:139901110634960 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: child_fk OBJECT_INSTANCE_BEGIN: 139901110634960 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 3, 2 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:14:5:4:139901110634960 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: child_fk OBJECT_INSTANCE_BEGIN: 139901110634960 LOCK_TYPE: RECORD LOCK_MODE: X,GAP LOCK_STATUS: GRANTED LOCK_DATA: 2, 1 *************************** 7. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:13:4:3:139901110635304 ENGINE_TRANSACTION_ID: 6965 THREAD_ID: 48 EVENT_ID: 41 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110635304 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 7 rows in set (0.00 sec)3-3. 부모 테이블에서 X Lock을 획득하고 자식 테이블에서 S Lock을 획득 하려는 경우
부모가 Update하면서 X Lock을 획득하는 경우, 자식의 Update는 S Lock을 획득하지 못해 대기할 수 있습니다. (2번)
Update나 Insert, DML이 제약조건을 검사하면서 S Lock을 획득하려 할 때 잠금 대기가 발생할 수 있습니다
Foreign Key 잠금이 걸린 경우에만 대기가 이루어지므로, 자식이 다른 Foreign Key로 Update하면 잠금 대기는 발생하지 않습니다. (3번)
Seq A B 1 mysql> update parent set name = '22' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 02 update child set parent_id = 1 where id = 2;
wait..3 mysql> update child set parent_id = 2 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0더보기2번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:1079:139901110637352 ENGINE_TRANSACTION_ID: 7063 THREAD_ID: 48 EVENT_ID: 69 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110637352 LOCK_TYPE: TABLE LOCK_MODE: IS LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:1082:139901110637264 ENGINE_TRANSACTION_ID: 7063 THREAD_ID: 48 EVENT_ID: 69 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110637264 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:16:4:3:139901110634272 ENGINE_TRANSACTION_ID: 7063 THREAD_ID: 48 EVENT_ID: 69 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110634272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:13:4:9:139901110634616 ENGINE_TRANSACTION_ID: 7063 THREAD_ID: 48 EVENT_ID: 69 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110634616 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 1 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:1079:139901110649344 ENGINE_TRANSACTION_ID: 7062 THREAD_ID: 50 EVENT_ID: 66 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110649344 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:13:4:9:139901110646432 ENGINE_TRANSACTION_ID: 7062 THREAD_ID: 50 EVENT_ID: 66 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110646432 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 6 rows in set (0.00 sec)3-4. 자식 테이블이 X Lock을 획득하고 부모 테이블이 Foreign Key제약 조건을 검사할 경우
반대로 자식 테이블에 Lock이 걸렸을 때, 만약 ON DELETE CASCADE와 같은 조건이 설정되어 있다면 부모는 Delete를 하지 못하고 기다리는 상황이 발생할 수 있습니다.
data_locks를 보면 B 트랜잭션이 Delete를 위해 PK의 X Lock을 대기하는 것을 볼 수 있습니다.
이는 Foreign Key 에 S Lock을 부여받고, 그에 상응하는 PK를 지우기 위해 X Lock을 대기하는 것으로 생각됩니다.
ALTER TABLE child DROP CONSTRAINT child_fk; ALTER TABLE child ADD CONSTRAINT child_fk FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE CASCADE;Seq A B 1 mysql> update child set name = '2' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 02 mysql> delete from parent where id = 1;
wait..3 Rollback; 4 Query OK, 1 row affected (11.03 sec) 더보기2번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:1081:139901110649520 ENGINE_TRANSACTION_ID: 7019 THREAD_ID: 50 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110649520 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:1081:139901110649432 ENGINE_TRANSACTION_ID: 7019 THREAD_ID: 50 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110649432 LOCK_TYPE: TABLE LOCK_MODE: IS LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:1079:139901110649344 ENGINE_TRANSACTION_ID: 7019 THREAD_ID: 50 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110649344 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:13:4:8:139901110646432 ENGINE_TRANSACTION_ID: 7019 THREAD_ID: 50 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110646432 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:15:5:2:139901110646776 ENGINE_TRANSACTION_ID: 7019 THREAD_ID: 50 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: child_fk OBJECT_INSTANCE_BEGIN: 139901110646776 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1, 1 *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204599592:15:4:2:139901110647120 ENGINE_TRANSACTION_ID: 7019 THREAD_ID: 50 EVENT_ID: 48 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110647120 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 1 *************************** 7. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:1081:139901110637264 ENGINE_TRANSACTION_ID: 7017 THREAD_ID: 48 EVENT_ID: 57 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139901110637264 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 8. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139901204597976:15:4:2:139901110634272 ENGINE_TRANSACTION_ID: 7017 THREAD_ID: 48 EVENT_ID: 57 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139901110634272 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 8 rows in set (0.00 sec)4. 위와 같은 논리로 데드락 상황을 추측해보겠습니다.
B가 A가 가진 X Lock을 기다리고 있는 동안, A가 다시 B의 X Lock을 기다리는 상황이 발생합니다.
데드락 감지로 인해 실패가 발생합니다.
Seq A B 1 mysql> update parent set name = 'ss' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 02 mysql> update child set parent_id = 1 wh
ere id = 2;
wait..3 mysql> update child set parent_id = 1 where
id = 2;
ERROR 1213 (40001): Deadlock found when tryi
ng to get lock; try restarting transaction4 Query OK, 1 row affected (35.50 sec)
Rows matched: 1 Changed: 1 Warnings: 0더보기2번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:1076:139752061856504 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139752061856504 LOCK_TYPE: TABLE LOCK_MODE: IS LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:1077:139752061856416 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139752061856416 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:11:4:3:139752061853504 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139752061853504 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:10:4:5:139752061853848 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139752061853848 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: WAITING LOCK_DATA: 1 *************************** 5. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129305816:1076:139752061850320 ENGINE_TRANSACTION_ID: 10054 THREAD_ID: 50 EVENT_ID: 56 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139752061850320 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 6. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129305816:10:4:5:139752061847328 ENGINE_TRANSACTION_ID: 10054 THREAD_ID: 50 EVENT_ID: 56 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139752061847328 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 6 rows in set (0.00 sec) 3번까지 실행 mysql> select * from performance_schema.data_locks\G *************************** 1. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:1076:139752061856504 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139752061856504 LOCK_TYPE: TABLE LOCK_MODE: IS LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 2. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:1077:139752061856416 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: NULL OBJECT_INSTANCE_BEGIN: 139752061856416 LOCK_TYPE: TABLE LOCK_MODE: IX LOCK_STATUS: GRANTED LOCK_DATA: NULL *************************** 3. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:11:4:3:139752061853504 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 49 EVENT_ID: 21 OBJECT_SCHEMA: sample OBJECT_NAME: child PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139752061853504 LOCK_TYPE: RECORD LOCK_MODE: X,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 2 *************************** 4. row *************************** ENGINE: INNODB ENGINE_LOCK_ID: 139752129306624:10:4:6:139752061854536 ENGINE_TRANSACTION_ID: 10055 THREAD_ID: 50 EVENT_ID: 57 OBJECT_SCHEMA: sample OBJECT_NAME: parent PARTITION_NAME: NULL SUBPARTITION_NAME: NULL INDEX_NAME: PRIMARY OBJECT_INSTANCE_BEGIN: 139752061854536 LOCK_TYPE: RECORD LOCK_MODE: S,REC_NOT_GAP LOCK_STATUS: GRANTED LOCK_DATA: 1 4 rows in set (0.00 sec)어떻게 회피해야할까?
사실 Foreign Key로 인해 발생하는 잠금은 회피하기가 쉽지 않습니다.
그렇기 때문에 이전 글에서 부터 언급한 것처럼 적절한 데드락 복구 메커니즘을 구현하는 것이 중요합니다.
또한 높은 동시성이 요구되는 환경에서는 Foreign Key를 사용하지 않는 방법도 고려할 수 있습니다.
또 테이블의 규모가 작고, 참조 무결성을 개발자가 직접 관리하는 방법도 있을 수 있습니다.
하지만 휴먼 에러는 언제든 발생할 수 있다는 점을 잊지 말아야 합니다.
그리고 트랜잭션의 크기를 최대한 작게 가져가면 잠금이 걸리는 시간을 줄일 수 있어 성능에 도움이 될 것입니다.
마무리
사실 저희 회사도 Foreign Key를 사용하지 않습니다.
높은 동시성이 요구되는 것은 아니지만, 규모가 작고 개발자가 직접 참조 무결성을 관리할 수 있기 때문에, 불필요한 데드락을 방지하는 것이 더 효율적이라고 판단하고 있습니다.
Locking Read에서 시작한 궁금증을 풀어가는 과정에서 Gap Lock과 Foreign Key로 인한 잠금 문제까지 공부하게 되었습니다.
역시, 공부는 스스로 궁금한 점이 있어야 더 흥미롭게 진행할 수 있는 것 같습니다.
또한, 글만으로는 이해가 부족할 때가 많아, 실제로 실습해보는 것이 중요하다고 느꼈습니다.
- https://dev.mysql.com/doc/refman/8.4/en/ansi-diff-foreign-keys.html
- https://dev.mysql.com/doc/refman/8.4/en/create-table-foreign-keys.html
- https://junghyungil.tistory.com/178
- https://m.blog.naver.com/parkjy76/220639066476
- https://taes-k.github.io/2020/05/17/mysql-transaction-lock/
- https://tecoble.techcourse.co.kr/post/2022-11-01-mysql-dead-lock/