[데이터베이스][MySQL] 외래키와 잠금 대기

데이터베이스 글 목록

MySQL InnoDB에서 외래키는 부모 테이블과 자식 테이블 사이의 참조 무결성을 보장한다.

외래키 제약이 걸려 있으면 자식 테이블에 데이터를 추가하거나 수정할 때, 해당 값이 부모 테이블에 실제로 존재하는지 확인해야 한다. 반대로 부모 테이블의 레코드를 삭제하거나 외래키가 참조하는 값을 변경할 때는, 그 값을 참조하는 자식 레코드가 있는지 확인해야 한다.

이 과정에서 InnoDB는 필요한 레코드에 잠금을 획득하려고 하며, 이미 다른 트랜잭션이 쓰기 잠금을 가지고 있는 경우 잠금 대기가 발생할 수 있다.

InnoDB 외래키 관리 특징

외래키로 인한 잠금 대기는 보통 테이블의 변경 작업에서 발생한다.

단순 조회가 아니라 INSERT, UPDATE, DELETE처럼 참조 무결성 검사가 필요한 작업에서 부모 테이블 또는 자식 테이블의 레코드 잠금을 확인하게 된다.

핵심은 다음과 같다.

  • 외래키 제약은 부모 레코드와 자식 레코드 사이의 무결성을 보장한다.
  • 자식 테이블의 외래키 컬럼을 변경할 때는 부모 테이블의 참조 대상 레코드를 확인해야 한다.
  • 부모 테이블의 참조 대상 레코드를 삭제하거나 변경할 때는 자식 테이블의 참조 여부를 확인해야 한다.
  • 다른 트랜잭션이 관련 레코드에 배타락을 가지고 있으면 공유락이나 배타락 획득이 지연된다.
  • 외래키와 관련 없는 컬럼의 변경은 외래키 검증으로 인한 잠금 확장을 발생시키지 않는다.

즉, 외래키 때문에 무조건 테이블 전체가 잠기는 것은 아니다.

외래키 검증에 필요한 부모 또는 자식 레코드에 이미 충돌하는 잠금이 걸려 있을 때 잠금 대기가 발생한다.

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

자식 테이블의 외래키 컬럼을 변경하려면, 변경하려는 값이 부모 테이블에 존재하는지 확인해야 한다.

이때 부모 테이블의 해당 레코드에 다른 트랜잭션이 배타락을 가지고 있다면, 자식 테이블의 변경 작업은 부모 레코드를 확인할 수 없어 대기한다.

예를 들어 다음과 같은 상황을 생각할 수 있다.

-- connection 1
UPDATE parent
SET col1 = 'test'
WHERE id = 3;

connection 1은 부모 테이블의 id = 3 레코드를 수정하고 있으므로 해당 레코드에 쓰기 잠금, 즉 배타락을 획득한다.

이 상태에서 다른 커넥션이 자식 테이블의 외래키 값을 변경하려고 한다.

-- connection 2
UPDATE child
SET pid = 4
WHERE id = 1000;

자식 테이블의 pid가 외래키라면, InnoDB는 새로 참조하려는 부모 레코드가 존재하는지 확인해야 한다.

이 과정에서 부모 테이블의 참조 대상 레코드에 접근해야 하는데, 해당 부모 레코드가 다른 트랜잭션에 의해 배타락 상태라면 connection 2는 connection 1이 끝날 때까지 대기한다.

정리하면 다음과 같다.

  1. 자식 테이블의 외래키 컬럼을 INSERT 또는 UPDATE한다.
  2. InnoDB는 참조할 부모 레코드가 존재하는지 확인한다.
  3. 이때 부모 레코드에 공유락을 획득하려고 한다.
  4. 부모 레코드에 다른 트랜잭션의 배타락이 걸려 있으면 공유락을 획득할 수 없다.
  5. 따라서 자식 테이블의 변경 작업이 대기한다.

외래키와 관련 없는 컬럼 변경

자식 테이블의 외래키 컬럼이 아닌 일반 컬럼을 변경하는 경우에는 외래키 검증으로 인한 부모 레코드 잠금 대기가 발생하지 않는다.

예를 들어 child.pid가 외래키이고, child.col1만 변경한다면 pid의 참조 관계가 바뀌지 않는다.

따라서 부모 테이블에 해당 외래키가 존재하는지 다시 확인할 필요가 없다.

즉, 외래키와 연관되지 않은 컬럼 변경은 외래키로 인한 잠금 확장을 발생시키지 않는다.

다만 해당 자식 레코드 자체에는 UPDATE로 인한 배타락이 걸릴 수 있다. 이 잠금은 외래키 검증 때문이 아니라 일반적인 행 변경 잠금이다.

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

반대로 부모 테이블의 레코드를 삭제하거나, 자식 테이블이 참조하는 값을 변경하려는 경우에도 잠금 대기가 발생할 수 있다.

특히 ON DELETE CASCADE가 설정되어 있으면 부모 레코드를 삭제할 때 해당 부모를 참조하는 자식 레코드도 함께 삭제되어야 한다.

이때 자식 레코드가 다른 트랜잭션에서 수정 중이라면, 부모 레코드 삭제 작업은 자식 레코드에 필요한 잠금을 얻지 못해 대기한다.

예를 들어 다음과 같은 상황이다.

-- connection 1
UPDATE child
SET col1 = 'test'
WHERE id = 1000;

이때 child.id = 1000 레코드가 pid = 3으로 부모 테이블의 id = 3을 참조한다고 하자.

connection 1은 자식 레코드를 수정하고 있으므로 해당 자식 레코드에 배타락을 획득한다.

이 상태에서 다른 커넥션이 부모 레코드를 삭제하려고 한다.

-- connection 2
DELETE FROM parent
WHERE id = 3;

만약 외래키에 ON DELETE CASCADE가 설정되어 있다면, 부모 레코드 id = 3을 삭제하면서 이를 참조하는 자식 레코드도 함께 삭제해야 한다.

하지만 해당 자식 레코드는 이미 connection 1에서 수정 중이므로 배타락이 걸려 있다.

따라서 connection 2는 자식 레코드를 삭제하기 위한 잠금을 획득하지 못하고 대기하게 된다.

부모 삭제 시 잠금 흐름

부모 테이블의 삭제 작업이 대기하는 흐름은 다음과 같이 정리할 수 있다.

  1. connection 1이 자식 레코드를 수정한다.
  2. 자식 레코드에는 배타락이 걸린다.
  3. connection 2가 부모 레코드를 삭제하려고 한다.
  4. ON DELETE CASCADE 때문에 참조 중인 자식 레코드도 함께 삭제해야 한다.
  5. 자식 레코드에 이미 connection 1의 배타락이 있으므로 삭제에 필요한 잠금을 획득할 수 없다.
  6. 따라서 부모 레코드 삭제 작업이 대기한다.

외래키 제약은 부모와 자식 레코드의 무결성을 보장해야 하므로, 부모 레코드의 변경이 자식 레코드 변경과 충돌할 수 있다.

공유락과 배타락 관점

외래키 잠금 대기는 공유락과 배타락의 충돌로 이해할 수 있다.

자식 테이블의 외래키 컬럼을 변경하는 경우, InnoDB는 참조 대상 부모 레코드가 존재하는지 확인하기 위해 부모 레코드에 공유락을 획득하려고 한다.

하지만 부모 레코드가 이미 다른 트랜잭션에 의해 배타락 상태라면 공유락을 획득할 수 없다.

반대로 부모 레코드를 삭제하거나 변경하는 경우에는 참조 중인 자식 레코드와의 관계를 확인하거나, ON DELETE CASCADE처럼 자식 레코드까지 변경해야 할 수 있다.

이때 자식 레코드가 이미 다른 트랜잭션에 의해 배타락 상태라면 부모 레코드의 변경 작업이 대기한다.

즉, 외래키 잠금 대기는 단순히 부모 테이블과 자식 테이블 중 어디를 먼저 수정했는지의 문제가 아니다.

외래키 무결성 검증 또는 연쇄 동작을 위해 필요한 레코드에 호환되지 않는 잠금이 이미 존재하는지가 핵심이다.

요약

MySQL InnoDB의 외래키는 참조 무결성을 보장하기 위해 부모 테이블과 자식 테이블의 관련 레코드를 확인한다.

자식 테이블의 외래키 컬럼을 추가하거나 변경할 때는 부모 레코드가 존재하는지 확인해야 한다. 이때 부모 레코드가 배타락 상태라면 자식 테이블 변경 작업은 대기한다.

부모 테이블의 레코드를 삭제하거나 변경할 때는 해당 부모를 참조하는 자식 레코드를 확인해야 한다. 특히 ON DELETE CASCADE가 있으면 자식 레코드까지 변경해야 하므로, 자식 레코드가 배타락 상태라면 부모 테이블 변경 작업이 대기한다.

외래키와 관련 없는 컬럼 변경은 외래키 검증으로 인한 잠금 확장을 발생시키지 않는다.

결국 외래키 잠금 대기의 핵심은 참조 무결성 검증에 필요한 레코드에 공유락 또는 배타락을 획득하려 할 때, 이미 호환되지 않는 잠금이 존재하는지 여부이다.