MySQL에서 외래키는 InnoDB 스토리지 엔진에서만 생성할 수 있으며, 외래키 제약이 설정되면 자동으로 연관되는 테이블의 컬럼에 인덱스까지 생성된다. 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스를 삭제할 수 없다.
InooDB의 외래키 관리에는 중요한 두가지 특징이 있다.
// connection 1
begin;
update tb_parent
set fd = 'changed-2'
where id = 2;
--
----
rollback;
--
// connection 2
--
begin;
update tb_child set pid=2 where id= 100
---
---
Query OK, 1 row affected (3.00 sec)
이 작업에서 1번 커넥션에서 먼저 트랜잭션을 시작하고 부모 테이블에서 id=2인 레코드에 update를 실행한다. 이 과정에서 1번 커넥션이 tb_parent 테이블에서 id=2인 레코드에 대해 쓰기 잠금을 획득한다. 그리고 2번 커넥션 자식 테이블의 외래키 컬럼인 pid를 2로 변경하는 쿼리를 실행해보자.
이 쿼리는 부모 테이블의 변경 작업이 완료될 때까지 대기한다. 다시 1번 커넥션에서 ROLLBACK이나 COMMIT으로 트랜잭션을 종료하면 2번 커넥션의 대기중인 작업이 즉시 처리되는 것을 확인할 수 있다.
즉 자식 테이블의 외래 키 컬럼의 변경은 부모 테이블의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때 까지 기다리게 되는 것이다. 이것이 InnoDB의 외래키 관리의 첫 번째 특징에 해당한다.
만약 자식 테이블의 외래키가 아닌 컬럼의 변경은 외래키로 인한 잠금확장이 발생하지 않는다. 이는 InnoDB의 외래키의 두번째 특징에 해당한다.