8.10 외래 키
- 외래키는 MySQL에서 InnoDB 스토리지 엔진에서만 생성할 수 있다.
- 외래키가 설정되면 자동으로 인덱스까지 생성된다.
- InnoDB 외래키 관리의 두 가지 특징
- 테이블 변경(쓰기 잠금)이 발생하는 경우에만 잠금 경합(잠금 대기)이 발생한다.
- 외래키와 연관되지 않은 칼럼의 변경은 최대한 잠금 경합(잠금 대기)을 발생시키지 않는다.
- 아래 예시 테이블로 특징들을 살펴보고자 한다.
CREATE TABLE parent (
id INT NOT NULL,
fd VARCHAR(100) NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;
CREATE TABLE child (
id INT NOT NULL,
parent_id INT DEFAULT NULL,
fd VARCHAR(100) DEFAULT NULL,
PRIMARY KEY(id),
CONSTRAINT child_ibfk_1 FOREIGN KEY(parent_id)
REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=InnoDB
8.10.1 자식 테이블의 변경이 대기하는 경우
| 작업 번호 |
커넥션-1 |
커넥션-2 |
| 1 |
BEGIN; |
|
| 2 |
UPDATE parent set fd=’chaged-2’ WHERE id=2; |
|
| 3 |
|
BEGIN; |
| 4 |
|
UPDATE child SET parent_id=2 WHERE id=100; |
| 5 |
ROLLBACK |
|
| 6 |
|
Qeury OK, 1 row affected (3.04 sec) |
- 1번 커넥션이 먼저 트랜잭션을 시작하고 id=2인
parent에 대해 쓰기 잠금을 획득한다.
- 1번 커넥션이
parent 2번에 대해 쓰기 잠금을 가지고 있으므로 커넥션 2에서는 자식 테이볼 child의 외래키(parent_id)를 2로 변경하는 작업을 수행할 수 없어 기다리게 된다.
- 커넥션 1이 잠금을 해제한 후에야 커넥션 2의 쿼리가 정상적으로 수행된다.
- 즉 자식 테이블의 외래 키 칼럼의 변경(INSERT, UPDATE)은 부모 테이블 확인이 필요한데 이 때 부모 테이블 해당 레코드에 쓰기 잠금이 걸려 있으면 해제까지 기다려야 한다.
- 자식 테이블의 외래키가 아닌 다른 칼럼의 변경은 잠금을 기다리지 않아도 된다.
8.10.2 부모 테이블의 변경 작업이 대기하는 경우
| 작업 번호 |
커넥션-1 |
커넥션-2 |
| 1 |
BEGIN; |
|
| 2 |
UPDATE child set fd=’chaged-100’ WHERE id=100; |
|
| 3 |
|
BEGIN; |
| 4 |
|
DELETE FROM parent WHERE id=1; |
| 5 |
ROLLBACK |
|
| 6 |
|
Qeury OK, 1 row affected (6.09 sec) |
child 레코드 100번은 parent 1번을 외래키로 가져 참조 관계를 가지고 있다.
- 커넥션 1에서
child 100번에 쓰기 잠금을 획득한다.
- 커넥션 2에서
parent 1번을 삭제하는 쿼리를 실행한다.
parent와 child 관계는 ON DELETE CASCADE 제약이 걸려 있다.- 때문에 1번
parent를 삭제하려면 child 100번도 삭제해야 하는데 커넥션 1에서 child 100번에 쓰기 잠금을 가지고 있다.
- 커넥션 1이 잠금을 해제하고 나서야 커넥션 2의 삭제 작업이 정상 수행된다.
데이터베이스에 외래 키를 생성할 때 이러한 잠금 경합을 고려해 모델링하는 것이 좋다. 자식이 추가될 때 부모가 있는지 확인하는 등의 작업은 읽기 잠금을 유발하고 그만큼 잠금이 다른 테이블로 확장되어 전체적인 동시성 처리에 영향을 미친다.