MySQL에서 외래키는 InnoDB엔진에서만 생성할 수 있다. 한번 외래키 제약이 설정되면 자동으로 연관 테이블 칼럼에 인덱스까지 생성된다. 이 외래키가 제거되지 않은 상태에서는 자동으로 생성된 인덱스도 제거할 수 없다.
InnoDB의 외래키에는 중요한 두 가지 특징이 있다.
위 작업에서 1번 커넥션이 작업번호 2번의 업데이트문을 실행하면 id가 2인 레코드에 대해 쓰기 잠금을 획득한다. 이후 2번 커넥션이 작업번호 4번을 실행한다. 이 작업은 외래키인 pid를 2로 변경하는 쿼리라서, 부모 테이블의 작업인 작업번호 2번이 끝날때까지 대기한다. 이때 커넥션 1번이 롤백이나 커밋으로 트랜잭션을 종료하면, 그제서야 커넥션 2번의 작업도 끝난다.
즉, 자식 테이블의 외래키 칼럼의 변경(INSERT, UPDATE)는 부모 테이블의 확인이 필요하다. 부모테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될때까지 기다린다. 이게 위의 특징 중 1번 특징이다.
그리고 자식 테이블의 외래키가 아닌 다른 컬럼의 변경은 외래키로 인한 잠금 대기가 발생하지 않는다. 이게 위의 2번 특징이다.
이번엔 자식테이블에 먼저 변경을 거는 예제이다. 1번 커넥션이 업데이트하는 행은 부모키 1을 참조하는 자식 테이블의 행이다. 이 행을 변경하면 자식 테이블의 해당 레코드에 대해 쓰기 잠금을 획득한다.
이 상태에서 2번커넥션이 부모 테이블에서 id가 1인 레코드를 삭제하려는 경우, 이 작업번호 4번 쿼리는 자식 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 한다. 이는 자식테이블에 정의된 외래키의 특성 때문에 부모 레코드가 삭제되면 자식 레코드도 동시에 삭제되는 식으로 동작하기 때문이다.
즉, 어느쪽이든 외래키에 쓰기작업이 일어나면 해당 외래키와 연관된 테이블의 행들은 쓰기 잠금이 걸린다.
따라서 DB에서 외래키를 물리적으로 생성하려면 위와 같은 잠금 경합까지 고려한 모델링을 진행하는 것이 좋다. 외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우, 해당 참조키가 부모 테이블에 있는지 확인하기도 한다. 이 뿐만 아니라 이러한 체크를 위해 연관 테이블이 읽기 잠금까지 건다. 또한 이러한 잠금이 다른 테이블로 확장되는 경우, 그 만큼 전체적으로 쿼리의 동시 처리에 영향을 미친다.