8.10 외래키

8.10 외래키

MySQL에서 외래키는 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,
    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');

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

• 커넥션 1이 부모 테이블 레코드에 대해 쓰기 잠금을 획득
• 커넥션 2이 자식 테이블의 외래키 컬럼을 변경하려하자 부모 테이블의 변경 작업이 완료 될때까지 대기
• 커넥션 1이 작업을 완료(rollback, commit) 하자 커넥션 2의 쿼리가 실행됨

즉 자식 테이블의 외래키 컬럼의 변경은 부모테이블의 확인이 필요함.

=> 부모테이블에 잠금이 걸려있으면 대기가 발생

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

원래대로 되돌린 후..

• 외래키를 정의할때 추가한 특성(on delete cascade) 때문에 부모레코드 삭제 시 자식 레코드를 삭제하도록 작동해서 대기가 발생

외래키의 고려사항은 부모테이블에 참조키가 있는지 확인하는 체크 작업 때 읽기 잠금을 걸어야한다는 점

이렇게 잠금이 다른 테이블로 확장되면 전체적으로 쿼리의 동시처리에 영향을 미침