[MySQL] 외래키(Foreign Key)와 데드락(DeadLock)

 

안녕하세요. 오늘은 외래키(Foreign Key)를 사용할 때, 발생할 수 있는 데드락(Dead Lock)에 관해 글을 작성해보았습니다.

우선 외래키(Foreign Key)와 데드락(Dead Lock)란 무엇일까요?

데드락이란, 둘 이상의 프로세스가 다른 프로세스가 점유하고 있는 자원을 서로 기다릴 때 무한 대기에 빠지는 상황을 일컫습니다.

 

외래키란, 외래키는 두 테이블을 서로 연결하는 데 사용되는 키입니다. 외래키가 포함된 테이블을 자식 테이블이라고 하고 외래키 값을 제공하는 테이블을 부모 테이블이라고 합니다.

 

Real MySQL 3장을 보면,

• "외래키는 부모테이블이나 자식 테이블에 데이터가 있는지 체크하는 작업이 필요하므로 잠금이 여러 테이블로 전파되고, 그로인해 데드락이 발생할 수 있다. 그래서 실무에서는 잘 사용하지 않는다."

라는 문장이 있습니다.

외래키를 사용할 때 데드락이 발생하는 경우에 관해 알아볼까요?

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

작업 순서는 위에서 아래로 진행되는 가정하에 테이블을 작성하였습니다. 

Connection 1	Connection 2
UPDATE parent SET address='seoul' WHERE id=3;
	UPDATE child SET pid=3 WHERE id=100;
ROLLBACK;
	Query OK, 1 row affected (2.00 sec)

Connection 1에서 먼저 트랙잭션을 시작하고 부모 테이블(parent)에서 id=3인 레코드에 UPDATE를 실행합니다. 이 과정에서 1번 커넥션이 부모 테이블(parent)에서 id=3인 레코드에 대해 쓰기 잠금을 획득합니다.

 

그리고 Conncetion 2에서 자식 테이블(child)의 외래키 칼럼(부모의 키를 참조하는 칼럼)인 pid를 3으로 변경하는 쿼리를 실행해보겠습니다. 이 쿼리는 부모 테이블의 변경 작업이 완료될 때까지 대기합니다.

 

다시 Connection 1에서ROLLBACK나 COMMIT으로 트랜잭션을 종료하면 Connection 2의  대기 중이던 작업이 즉시 처리되는 것을 확인할 수 있습니다.

 

즉, 자식 테이블(child)의 외래키 칼럼 변경(INSERT, UPDATE)은 부모 테이블(parent)의 확인이 필요한데, 이 상태에서 부모 테이블의 해당 레코드가 쓰기 잠금이 걸려있으면 해당 쓰기 잠금이 해제될 때까지 기다리게 되는 것입니다.

 

만약 자식 테이블의 외래키(pid)가 아닌 컬럼의 변경은 외래키로 인한 잠금 확장이 발생하지 않습니다.

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

Connection 1	Connection 2
UPDATE child SET address='seoul' WHERE id=3;
	DELETE FROM parent WHERE id=1;
ROLLBACK;
	Query OK, 1 row affected (4.00 sec)

변경하는 테이블의 순서만 변경한 같은 종류의 예제입니다. 첫번째 커넥션에 부모 키 "3"을 참조하는 자식 테이블의 레코드를 변경하면 child 테이블에 대해 쓰기 잠금을 획득합니다.

 

이 상태에서 Connection 2에서 parent 테이블의 레코드를 삭제하려면 이 쿼리는 child 테이블의 레코드에 대한 쓰기 잠금이 해제될 때까지 기다려야 합니다. 이는 자식 테이블(child)이 생성될 때 정의된 외래키의 특성(ON DELETE CASCADE) 때문에 레코드가 삭제되면 자식 레코드도 동시에 삭제되도록 작동하기 때문입니다.

 

데이터베이스에서 외래 키를 물리적으로 생성하려면 이러한 현상으로 인한 잠금 경합까지 고려해 개발을 진행하는 것이 좋습니다. 이처럼 물리적으로 외래키를 생성하면 자식 테이블에 레코드가 추가되는 경우 해당 참조키가 부모 테이블에 있는지 확인한다는 것은 이미 다들 알고 있을 것입니다.

 

하지만 물리적인 외래키의 고려 사항은, 이러한 체크 작업이 아니라 이런 체크를 위해 연관 테이블에 읽기 잠금을 걸어야 한다는 것입니다. 또한 이렇게 잠금이 다른 테이블로 확장되면 그만큼 전체적으로 쿼리의 동시 처리에 영향을 미치게 됩니다.