외래키와 잠금

MySQL 외래 키(Foreign Key)란?

MySQL에서 외래 키(Foreign Key, FK)는 한 테이블의 컬럼이 다른 테이블의 기본 키(Primary Key) 또는 고유 키(Unique Key)를 참조하도록 설정하는 제약 조건입니다. 이를 통해 데이터 무결성을 유지하고, 테이블 간의 일관성을 보장할 수 있습니다.

다만, MySQL에서는 InnoDB 스토리지 엔진에서만 외래 키를 지원하며, 성능 및 유지보수 이슈로 인해 실무에서는 외래 키를 사용하지 않는 경우도 많습니다.

 

실무에서 외래 키를 사용하지 않는 이유

1. 수동 데이터 적재 및 긴급 조치의 어려움
   외래 키가 설정된 경우, 부모 테이블과 자식 테이블 간의 참조 무결성이 강제됩니다.
   하지만 수동으로 데이터를 적재하거나 긴급 조치가 필요한 경우, 복잡한 관계로 인해 데이터 수정 및 삭제가 어려워질 수 있습니다.
   
   
2. 잠금(Lock) 전파로 인한 성능 저하
   외래 키가 설정된 테이블에서 삭제(DELETE) 또는 수정(UPDATE) 연산이 발생하면, 관련된 모든 테이블에 잠금이 전파될 수 있습니다. 심할 경우 **데드락(Deadlock)**이 발생할 위험이 있습니다.

 

외래 키와 잠금

 

InnoDB의 외래키는 아래와 같은 특징을 가지고 있습니다.

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에 잠금 대기가 발생합니다.
• 외래 키와 연관되지 않은 칼럼의 변경은 최대한 잠금 대기를 발생하지 않으려고 하는 특징이 있습니다.

 

테이블의 변경이 발생하는 경우에 잠금 대기가 발생한다? 이게 무슨 말일까요?

이를 이해하기 위해 tb_parent와 tb_child를 사용해서 잠금 대기 상황을 예시로 들어보겠습니다.

 

 

tb_child 테이블은 tb_parent 테이블에 대한 FK를 가지고 있습니다.

각각의 테이블에서 변경이 발생해 어떻게 대기가 발생하는 지 살펴보겠습니다.

 

자식 테이블의 변경에 대기하는 경우

 

 

1. 부모 테이블의 id = 2 레코드에 수정이 발생
   
   커넥션 - 1이 부모 테이블 id = 2인 레코드에 배타 락 휙득
   
   
2. 자식 테이블의 id = 100인 레코드가 FK인 pid를 2로 변경
   
   커넥션 - 2에서 부모 테이블 id = 2인 레코드가 존재하는 지 공유 락을 휙득하려고 함.

 

부모 테이블 id = 2인 레코드는 현재 잠금 대기 중인 상태이기 때문에 부모 테이블에 id = 2인 레코드가 존재하는 지 확인 불가능

 

왜 확인이 불가능할까?

FK 검증 시 **공유 락(S Lock)**을 사용하여 해당 부모 레코드가 다른 트랜잭션에서 변경되지 않도록 보호하려고 해. 하지만 현재 부모 테이블의 id = 2 레코드는 이미 배타 락(X Lock)이 걸려 있으므로, 공유 락(S Lock) 요청이 블로킹됨
→ 결과적으로 FK 검증을 위한 확인이 불가능해짐.

 

 

부모 테이블의 변경 작업이 대기하는 경우

 

1. 자식 테이블의 id = 100인 레코드에 수정 발생
   
   커넥션 - 1에서 id = 100인 레코드에 배타 락 휙득
   
   
2. 부모 테이블의 id = 1인 레코드를 삭제하려고 할 시에 외래키 속성(ON DELETE CASCADE) 때문에 id = 100인 레코드도 부모 레코드가 삭제될 시에 같이 삭제되어야함.
   
   자식 테이블의 id = 100인 레코드에 배타 락이 걸려 있어 다른 트랜잭션에서 삭제 불가능

 

지금까지 FK의 특징에 대해서 알아보았습니다. FK에 대해 공부하며 앞으로 FK의 도입이 적절한 지를 신중히 고민해 검토해야겠다는 생각이 들었습니다. 감사합니다.