교착상태(Dead Lock)

Deep Dive to DB 시리즈는 현재 진행중인 DB DB Deep 스터디에서 학습하는 내용을 복습 및 정리하는 시리즈입니다.

교착상태는 두 개 이상의 트랜잭션이 특정 자원(테이블 또는 행)의 Lock을 획득한 채 다른 트랜잭션이 소유하고 있는 잠금을 요구하면 아무리 기다려도 상황이 바뀌지 않는 상태이다.

교착 상태의 빈도를 낮추는 방법

• 트랜잭션을 자주 커밋한다.
• 정해진 순서로 테이블에 접근한다.
• 읽기 잠금 획득(SELECT ~ FOR UPDATE)의 사용을 피한다.
• 한 테이블의 복수 행을 복수의 연결해서 순서 없이 갱신하면 교착 상태가 발생하기 쉽다. 이 경우에는 테이블 단위의 잠금을 획득해 갱신을 직렬화하면 동시성이 떨어지지만 교착 상태를 피할 수 있다.

이를 MySQL에서는 아래와 같은 밤법으로 식별할 수 있다.

MySQL에서 교착 상태 세션 식별

MySQL은 데드락을 감지하면 트랜잭션을 롤백해 데드락 상태에서 빠져나갈 수 있도록 기능을 제공한다.

• innodb_deadlock_detect 설정: On

  set global innodb_deadlock_detect=on;

기능을 활성화하고 위에서 진행한 내용을 동일하게 진행하면 데드락이 발생되는 쿼리에서 에러를 보여준다.

이렇게 데드락이 감지된 경우에는 InnoDB 모니터 정보를 통해 내용을 확인할 수 있다. 각 트랜잭션을 나타내고 각 트랜잭션의 락 정보 등이 담겨 있으며, 어떤 트랜잭션을 롤백 하여 데드락 상태에서 빠져나갔는지 확인이 가능하다. 아래는 링크의 블로그에서 실제로 SHOW ENGINE INNODB STATUS 명령을 사용하여 진행한 명령어의 일부를 가져온 것이다.

> show engine innodb status
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-05-03 10:40:12 7f221231d700
*** (1) TRANSACTION:
TRANSACTION 14268102582, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 2936, 37 row lock(s), undo log entries 2
MySQL thread id 750346761, OS thread handle 0x7f2215516700, query id 11209592031 smtp2 192.168.0.4 hyungdew updating
DELETE FROM hyungdew.dewdew
            WHERE status = 'complete'
            LIMIT 10000
...