외래키

외래키

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

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_parentid (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');

위와 같은 테이블에서 언제 자식 테이블의 변경이 잠금 대기를 하게 되고, 언제 부모 테이블의 변경이 잠금 대기를 하게 되는지 예제로 살펴보자.

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

작업번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_parent SET fd='changed-2' WHERE id-2;
3		BEGIN;
4		UPDATE tb_child SET pid=2 WHERE id=100;
5	ROLLBACK;
6		Query Ok, 1 row affected (3.04 sec)

이 작업에서 1번 커넥션에서 먼저 트랜잭션을 시작하고 부모(tb_parent) 테이블에서 id=2인 레코드에 UPDATE를 실행한다. 이 과정에서 1번 커넥션이 tb_parent 테이블에서 id=2인 레코드에 대해 쓰기 잠금을 획득한다. 그리고 2번 커넥션에서 자식 테이블(tb_child)의 외래키 칼럼(부모의 키를 참조하는 칼럼)인 pid를 2로 변경하는 쿼리를 실행해보자. 이 쿼리(작업번호 4번)는 부모 테이블의 변경 작업이 완료될 때까지 대가한다. 다시 1번 커넥션에서 ROLLBACK이나 COMMIT으로 트랜잭션을 종료하면 2번 커넥션의 대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있다. 즉 자식 테이블의 외래키 칼럼의 변경(INSERT, UDPATE)은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려 있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것이다. 이것이 InnoDB의 외래키 관리의 첫 번째 특징에 해당한다.

만약 자식 테이블의 외래키(pid)가 아닌 칼럼(tb_child 테이블의 fd 칼럼과 같은)의 변경은 외래키로 인한 잠금 확장(바로 위에서 살펴본 예제와 같은)이 발생하지 않는다. 이는 InnoDB의 외래키의 두 번째 특징에 해당한다.

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

작업번호	커넥션-1	커넥션-2
1	BEGIN;
2	UPDATE tb_child SET fd='changed-100; WHERE id=100;
3		BEGIN;
4		DELETE FROM tb_parent WHERE id=1;
5	ROLLBACK;
6		Query Ok, 1 row affected (6.09 sec)

변경하는 테이블의 순서만 변경해서 같은 예제를 만들어 봤다. 첫 번째 커넥션에서 부모 키 "1"을 참조하는 자식 테이블의 레코드를 변경하면 tb_child 테이블의 레코드에 대해 쓰기 잠금을 획득한다. 이 상태에서 2번 커넥션에서 tb_parent 테이블의 레코드를 삭제하려면 이 쿼리(작업번호 4번)는 tb_child이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되도록 작동하기 때문이다.

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

참고

• Real MySQL