Foreign Key

외래키

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

DB 잠금 메커니즘에서, 외래키의 중요한 두 가지 특징이 있다.

1. 테이블의 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.

   • 데이터를 수정하는 작업(INSERT, UPDATE, DELETE)을 수행할 때만 잠금 경합이 일어난다.

   • 단순히 읽는 작업으로는 잠금 경합이 발생하지 않는다.

2. 외래 키와 연관되지 않은 컬럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.

   • 외래 키로 연결되지 않은 컬럼을 변경할 때, InnoDB 엔진이 가능한 잠금을 최소화하여 동시성을 향상시키기 위한 전략이다.

create table tb_parent (
	id int not null,
	fd varchar(100) not null, 
	primary key(id)
) ENGINE=InnoDB;

create table tb_child (
	id int not null,
	pid int default null, // parent.id 컬럼 참조
	fd varchar(100) default null,
	primary key (id),
	key ix_parent_id (pid),
	constraint child_ibfk_1 foreign key (pid) references tb_parent (id)
	on delete cascade
) ENGINE=InnoDB;

insert into tb_parent values (1, 'parent-1'), (2, 'parent-2');
insert into tb_child values (100, 1, 'child-100');

위와 테이블 정보와 DML 쿼리에서, 언제 자식 테이블의 변경이 잠금 대기를 하고, 언제 부모 테이블의 변경이 잠금 대기를 하는지 살펴보자.

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

1. 1번 커넥션에서 트랜잭션을 시작하고 부모(tb_parent) 테이블에서 id=2인 레코드에 UPDATE를 실행한다.

   • 1번 커넥션이 tb_parent 테이블에서 id=2 레코드에 쓰기 잠금을 획득한다.

2. 2번 커넥션에서 자식 테이블(tb_child)의 외래 키 컬럼인 pid를 2로 변경하는 쿼리를 실행

   • 이 쿼리(작업 번호 4)는 부모 테이블의 변경 작업이 완료될 때까지 기다린다.

3. 다시 1번 커넥션에서 ROLLBACK, COMMIT으로 트랜잭션을 종료하면 2번 커넥션의 대기 중이던 작업이 즉시 처리된다.

즉, 자식 테이블의 외래 키 컬럼의 변경(insert, update)은 부모 테이블의 확인이 필요하다. 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 잠금이 해제될 때까지 기다려야 한다. 이것이 InnoDB 외래 키 관리의 첫 번째 특징이다.

외래키 제약 조건은 데이터의 일관성과 무결성을 유지하기 위해 존재한다. 자식 테이블의 입장에서 보면,

• INSERT: 자식 테이블에 새 레코드를 삽입할 때, 외래키 값이 부모 테이블에 존재하는지 확인한다.

• UPDATE: 자식 테이블의 외래키 값을 변경할 때, 새로운 값이 부모 테이블에 있는지 확인한다.

• DELETE: 일반적으로 자식 테이블의 레코드 삭제는 부모 테이블 확인이 필요 없지만, 부모 테이블의 레코드를 삭제하는 경우에는 자식 테이블의 참조 여부를 확인한다.

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

1. 1번 커넥션에서 부모 키 ‘1’을 참조하는 자식 테이블의 레코드를 변경하면 tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다.

2. 2번 커넥션이 tb_parent 테이블에서 id=1 인 레코드를 삭제한다.

   • 이 쿼리(작업 번호4)는 tb_child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 대기한다.

   • 자식 테이블이 생성될 때 정의된 외래키의 특성(cascade) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제된다.

외래키 제약 조건이 DB 동시성과 성능에 영향을 미칠 수 있다. 위 예시들처럼,

자식 테이블의 레코드를 변경하면 부모 테이블의 관련 레코드에도 영향을 주면서 외래키로 연결된 테이블 간 잠금이 확장된다.

또, 외래키가 있으면 DB는 참조 무결성을 유지하기 위해 추가적인 검사를 실시하는데, 이 과정에서 연관된 테이블에 읽기 잠금을 걸게 된다.

따라서 DB에서 외래 키를 물리적으로 생성하는 경우에는 trade-off를 고려하여 모델링을 진행하는 것이 좋다.