외래키

MySQL의 외래키

• InnoDB 스토리지 엔진에서만 생성할 수 있다.
• 외래키 제약이 설정되면 자동으로 연관되는 테이블의 칼럼에 인덱스까지 생성된다.
• 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.

외래키의 특징

• 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
• 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

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

자식 테이블에서 부모 테이블의 id에 외래키를 걸었을 때, 다음과 같은 상황이다.

1. 1번 커넥션 > 트랜잭션을 시작하고, 부모 테이블에서 id가 1인 레코드에 UPDATE를 실행한다.
   • 1번 커넥션이 부모 테이블에서 id가 1인 레코드에 대해 쓰기 잠금을 획득
2. 2번 커넥션 > 자식 테이블의 외래키 칼럼을 변경하는 쿼리 실행
   • 이 쿼리는 부모 테이블의 변경 작업이 완료될 때까지 대기
3. 1번 커넥션 > ROLLBACK 이나 COMMIT 으로 트랜잭션 종료
4. 2번 커넥션의 대기 중이던 작업이 즉시 처리된다.

정리

InnoDB의 외래키 관리의 첫 번째 특징

• 자식 테이블의 외래 키 칼럼의 변경(INSERT, UPDATE)는 부모 테이블의 확인이 필요
• 위 상황에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때까지 대기

InnoDB의 외래키 관리의 두 번째 특징

• 자식 테이블의 외래키가 아닌 칼럼의 변경은 외래키로 인한 잠금 확장(위 예제)이 발생하지 않는다.

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

자식 테이블에서 부모 테이블의 id에 외래키를 걸었을 때, 다음과 같은 상황이다.

1. 1번 커넥션 > 자식 테이블의 외래키 칼럼을 변경하는 쿼리 실행
   • 1번 커넥션이 자식 테이블에서 해당 레코드에 대해 쓰기 잠금을 획득
2. 2번 커넥션 > 부모 테이블에서 id가 1인 레코드를 DELETE
   • 이 쿼리는 자식 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 대기
   • 자식 테이블이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 작동하기 때문이다.
3. 1번 커넥션 > ROLLBACK 이나 COMMIT 으로 트랜잭션 종료
4. 2번 커넥션의 대기 중이던 작업이 즉시 처리된다.

정리

• 데이터베이스에서 외래 키를 물리적으로 생성하려면 이러한 현상으로 인한 잠금 경합까지 고려해 모델링을 진행하는 것이 좋다.
• 물리적으로 외래키를 생성하면, 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 있는지 확인한다.
• 물리적인 외래키의 고려 사항은 이러한 체크를 위해 연관 테이블에 읽기 잠금을 걸어야 한다는 것이다.
• 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미친다.

Reference

참고 서적

📔 Real MySQL 8.0