[MySQL] 데이터베이스에서 데드락(Deadlock)

 

---

목차

1. 데드락(Deadlock)을 줄일 수 있는 방법
2. 데드락 상태 확인 명령어
3. 데드락 케이스별 분석

---

두개 이상의 트랜잭션들이 동시에 진행될때 서로가 서로에 대한 락을 소유한 상태로 대기 상태로 빠져서 더이상 진행하지 못하는 상황을 데드락(deadlock)이라고 한다.

• 데드락은 트랜잭션을 지원하는 데이터베이스에서는 자주 발생하는 문제이다.
• 멀티 스레드(Multi-threaded) 어플리케이션에서 발생하는 데드락은 해당 어플리케이션을 완전히 멈추게 해버리기 때문에 위험하다.
• 하지만 일반적인 DBMS (Database Management System)에서는 데드락 탐지(Deadlock detection) 기능을 제공하기때문에 데드락이 발견되면 자동으로 해소시켜준다 (실제 데드락 상황이 아닐지라도 락에 대한 대기시간이 설정된 시간을 초과하면 이것도 데드락으로 처리된다)
• 이 과정에서 작업중이던 트랜잭션들 중 일부가 취소되는 경우가 발생 할 수 있기때문에 어플리케이션 레벨에서 해당 트랜잭션을 재실행 하여 작업을 완수할 수 있도록 구성해야한다.

 

 

1. 데드락(Deadlock)을 줄일 수 있는 방법

 

 

 

• 트랜잭션(transaction)을 최대한 간결하게 만든다.
• 인덱스를 잘 구성해야한다. 더 적게 레코드를 스캔할 수록 더 적은 락이 걸린다.
• Locking read (SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE)를 사용시에 READ_COMMITTED와 같은 더 낮은레벨의 트랜잭션을 사용할 수 있는 상황이라면 적극적으로 사용한다.
• 트랜잭션 안에서 여러 데이터를 수정할때는 발생하는 Lock의 순서를 항상 순차적으로 만든다.
  • 즉 A, B, C 테이블을 수정시 각각의 트랜잭션에서 A -> B -> C 순서로 수정하면 데드락의 위험이 적다.
  • 예시) 어플리케이션 상의 특정 조건에따라 A -> C -> B 또는 C – B -> A 와 같이 각각 트랜잭션이 동시 실행되면서 다른순서로 데이터를 수정하게 된다면 데드락의 확률이 높아진다.
    • 일반적으로는 row 단위로 lock이 걸리지만 이해를 쉽게 하기 위해 테이블 단위 락을 가정
    • 첫번째 트랜잭션에서 A 테이블 락을 잡음
    • 두번째 트랜잭션에서 C 테이블 락을 잡음
    • 첫번째 트랙잭션에서 C 를 수정하려 하지만 두번째 트랜잭션에서 C 테이블 락을 먼저 잡았기때문에 대기 상태에 빠짐
    • 두번째 트랜잭션이 B 테이블 락을 잡고 수정 후, A 테이블 락을 잡으려 하지만 첫번쨰 트랜잭션이 락을 잡고있기떄문에 대기상태에 빠짐
    • 트랜잭션이 서로 물고물린 상태로 대기상태에 빠짐 (= 데드락)
• 하나의 구문에 여러 ROW가 포함되는 Batch INSERT ... ON DUPLICATE KEY UPDATE를 주의하라. 하나의 Batch 구문은 트랜잭션이 걸린 여러개의 구문 처럼 동작하기때문에 각각의 배치 Query에 포함된 데이터의 PK가 겹치게되면 데드락이 발생할 확률이 있다.
  • 예시) 아래 Deadlock Case 1 참고
• 트랜잭션들을 완전히 Serialize 한다
  • 예시)
    • 1줄의 데이터만 갖고있는 세마포어용 테이블을 생성
    • 각각의 트랜잭션들이 다른 테이블에 접근하기 전에 먼저 세마포어용 테이블을 업데이트하도록 한다.
    • 세마포어 테이블에 늦게 접근한 트랜잭션은 대기상태에 빠지기 때문에 각 트랜잭션들의 완전한 순차 실행이 보장된다.

 

 

2. 데드락 상태 확인 명령어

 

어떤 lock이 걸려있는지 상태를 확인하거나 최근에 발생했던 데드락에 대해 알고 싶으면 아래 명령어를 사용하면 된다.

SHOW ENGINE INNODB STATUS;

아래는 exclusive lock (lock_mode X) 때문에 대기중인 트랜잭션이 있을때의 예제 메시지이다.

------- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 770 page no 3 n bits 80 index PRIMARY of table `example_db`.`test` trx id 774317 lock_mode X locks rec but not gap waiting

 

 

 

 

3. 데드락 케이스 분석

 

Deadlock Case 1 : 

 

Upsert 동작(기존 데이터가 존재하면 UPDATE, 없으면 INSERT)의 경우 MySQL에서는 INSERT INTO … ON DUPLICATE KEY UPDATE 구문을 이용하여 DB차원에서 쿼리로 구현이 가능하기 때문에 매우 편리하다. 하지만 이 여러개의 Row에 대해서 한번에 Batch성으로 실행하게되면 데드락이 발생할 위험이 큰 편이다.

Query A:

INSERT INTO my_table (pk, name) VALUES(1, 'a'), (2,'b') ON DUPLICATE KEY UPDATE name=VALUES(name);

Query B:

INSERT INTO my_table (pk, name) VALUES(2, 'a'), (1,'b') ON DUPLICATE KEY UPDATE name=VALUES(name);

위의 두 쿼리가 DB상에서 다른 connection을 통해 들어와서 동시에 실행되면 각각의 쿼리는 2번의 INSERT를 트랜잭션으로 묶어놓은것 처럼 동작한다. 따라서 운이나쁘게 다음과 같은 순서로 실행되면 데드락이 발생한다.

• A에서 pk=1인 row upsert pk=1 lock 획득
• B에서 pk=2인 row upsert pk=2 lock 획득
• A에서 pk=2인 row upsert 시도 -> B에서 먼저 lock을 가져갔기때문에 대기
• B에서 pk=1인 row upsert 시도 -> A에서 먼저 lock을 가져갔기때문에 대기
• 데드락 발생

Upsert가 아닌 WHERE 조건에의해 여러 row들을 한번에 UPDATE 한다면 비슷한 이유로 데드락이 발생할 수 있다.

 

Deadlock Case 2 :

 

트랜잭션 A, B, C를 가정하고 아래 INSERT 상황과 DELETE 상황에서 어떻게 내부적으로 동작하는지 살펴보자.

 

INSERT 경쟁 상황

 

• A, B, C 순서로 같은 key값을 가지는 데이터를 INSERT 하기 위해 경쟁
  • A 에서 INSERT INTO table (pk) VALUES (3); 실행하면 A에서 exclusive lock 획득
  • B와 C에서도 동일한 INSERT 구문 실행한다. (B, C는 대기상태로 빠짐)
  • A를 rollback하는 순간 B, C가 경쟁 시작.
    • INSERT의 경우 exclusive lock을 획득 시도해야하지만, 해당 INSERT에서 duplicated key error가 발생하는 경우에는 해당 인덱스 레코드에 대해 일단 shared lock을 먼저 획득 시도하는 특성이 있다.
    • B, C가 동일 인덱스 레코드에 대해 shared lock을 먼저 획득한 후 exclusive lock을 잡으려고 하기 때문에 데드락이 발생. (B가 exclusive lock을 획득하려 해도 C가 획득한 shared lock에 의해 불가능, C -> B의 경우에도 vice versa)
    • 늦게 실행된 C가 deadlock처리되어 트랜잭션이 롤백되어 종료되면, B가 exclusive lock을 획득하여 실행된다.
  • A를 commit하면 B, C는 바로 duplicated key error가 발생하지만 여전히 트랜잭션은 열려있다.

결국 이런 INSERT 상황에서 선행 트랜잭션이 rollback하게되면 나머지 트랜잭션들 중 하나만 성공하고 나머지는 모두 데드락으로 강제 롤백 된다는 것을 알 수 있다. 이 케이스는 얼핏 봐서는 데드락이 발생하지 않을 것 같은 상황처럼 보이기 때문에 문제 발견이 쉽지 않으므로 기억해두는 것이 좋다.

 

DELETE 경쟁 상황

 

INSERT의 경우가 매우 특이한 경우라는 것을 강조하기위해서 DELETE에 대해서는 특이하지 않게 잘 동작하는 것을 확인해보자.

• A, B, C 순서로 같은 key값에 대한 DELETE로 경쟁시키는 경우
  • A가 exclusive lock 획득
  • B가 exclusive lock 획득시도(대기), C가 exclusive lock 획득시도(대기)
  • A가 commit 하는 경우
    • lock의 대상이었던 row가 사라짐
    • 때문에 B, C 모두 대기상태가 종료되면서 affected row=0 결과 리턴함 (B, C 트랜잭션은 계속 열려있음)
  • A가 rollback 하는 경우
    • B가 exclusive lock 획득 C는 계속 대기

 

 

INSERT, DELETE 상황 비교 분석

 

• INSERT의 경우 duplicated key error 가 발생시 shared lock을 획득 시도하기 때문에 하나 이상의 트랜잭션이 동시에 동일한 row에 대한 shared lock을 획득하게 된다. (shared lock은 여러명이 동시에 획득 가능하기 때문) 트랜잭션 A가 종료되면, 다시 트랜잭션 B가 exclusive lock을 획득 시도하려 하지만 트랜잭션C가 잡고있는 shared lock 때문에 exclusive lock을 획득하는것이 불가능하다. 반대의 경우도 마찬가지이므로 데드락 상황이다.

DELETE의 경우 exclusive lock을 획득 시도하기 때문에 한번에 하나의 트랜잭션만 lock을 획득가능하고 나머지는 대기한다. 따라서 데드락 상황이 생기지 않는다.

 

조치 방법

 

1. 쿼리 개선 : 위 방법대로
2. Isolation Level 조정
3. 스토리지엔진에서 자동 감지 옵션 조정

    - deadlock_search_depth_long
    - deadlock_search_depth_short
    - deadlock_timeout_long
    - deadlock_timeout_short