marinesnow34
0%

MySQL insert 충돌 시 어떤일이 일어날까?

작성일 In

서론

공식 문서를 보던 중에 insert 할 때 발생하는 신기한(?) 현상을 발견했습니다. 직접 실행해 보니 제가 예상했던 것과 다른 결과가 나와서 이번 기회에 알아보았습니다.

퀴즈

다음과 같은 상황에서 최종 결과는 무엇일까요?

하나의 테이블에 각각의 세션에서 동일한 키를 가지고 insert를 시도하는 상황입니다.

1
2
CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
INSERT INTO t1 VALUES(100); -- 편의상 100이라는 row를 넣었습니다.
1
2
3
-- 세션1
START TRANSACTION;
INSERT INTO t1 VALUES(1);
1
2
3
-- 세션2
START TRANSACTION;
INSERT INTO t1 VALUES(1);
1
2
3
-- 세션3
START TRANSACTION;
INSERT INTO t1 VALUES(1);
1
2
-- 세션1
ROLLBACK;

위 코드를 보면 세션 1, 2, 3이 동일한 key를 가지고 t1에 insert를 시도합니다. 그 후 lock을 점유하고 있는 세션1을 rollback을 합니다.

이때 세션2, 3은 어떻게 되고 어떤 lock을 가지게 될까요?

직관적으로 생각해 보면 한 세션은 row, X lock을 잡고, 다른 세션은 락을 대기하고 있을 것 같습니다.

하지만 정답은 최종적으로 세션2, 3중 하나만 성공하고 row, GAP 락이 걸립니다. 왜 그럴까요?

해설

실제 결과와 함께 설명하겠습니다.

  1. 세션1이 INSERT INTO t1 VALUES(1);을 실행하면 X lock이 걸립니다.
  2. 세션2가 INSERT INTO t1 VALUES(1);을 실행하면 duplicate-key error 때문에, S lock을 얻고 대기합니다.
  3. 세션3도 세션2와 같은 상황입니다. S lock을 얻고 대기합니다.
    1.png
  4. 세션1이 ROLLBACK;을 실행하면 lock이 해제됩니다.
  5. 세션 2,3 이 가진 S, row lock은 row가 삭제되면서 GAP lock으로 변경됩니다.
  6. 세션2와 세션3은 X lock을 얻으려고 시도합니다. 하지만 서로가 가진 S lock 때문에 X lock을 얻을 수 없습니다. 따라서 데드락이 발생합니다.
  7. InnoDB는 데드락을 감지하고, 데드락이 발생한 세션을 롤백합니다. 따라서 세션3이 롤백됩니다.
  8. 세션2는 INSERT INTO t1 VALUES(1);을 재시도 하면서 GAP lock을 얻습니다.
  9. 최종적으로 남은 lock은 해당 row의 lock과 다음 GAP의 lock입니다.
    2.png

참고로 세션 1이 insert한 후에 세션 2,3이 insert를 시도할 때 S lock을 획득하는 이유는 공식 문서에 다음과 같이 설명되어 있습니다.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set.

세션 1이 insert를 했기 때문에 세션 2,3에서 중복키 오류가 발생하고, 이때 S lock을 획득합니다.

단순 조회에는 영향이 없습니다. 하지만 (-∞, 100)범위에 S 혹은 X lock을 획득하려고 시도하는 경우에는 대기하게 됩니다.

만약에 유사하게 테이블의 마지막 값보다 큰 값(예를 들어 101)을 insert하는 상황에서 충돌이 나고 rollback 된다면 마지막 값(100) 보다 큰 모든 값에 대해서 lock이 걸립니다.

결론

만약 실제 서비스에서 특정 row에만 영향이 간다고 생각했는데 의도하지 않은 GAP락이 걸리면 전체 서비스에 치명적일 수 있습니다.

흔히 발생하는 케이스는 아니지만 절대 발생하지 않는 것은 아니기에 알고 있으면 미리 방지할 수 있습니다.