mysql 자동 데드락(deadlock) 감지기능

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다.
InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그중 하나를 강제로 종료시킨다.

이때 어느 트랙잭션을 먼저 강제 종료할 것인지를 판단하는 기준은 트랜잭션의 undo log 양이며, undo log 레코드를 더 적게 가진 트랜잭션이 일반적으로 rollback 의 대상이 된다.
트랜잭션이 undo record를 적게 가졌다는 이야기는 rollback을 해도 undo 처리를 해야할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 Mysql 서버의 부하도 덜 유발하기 떄문이다.

참고로 InnoDB 스토리지 엔진은 상위 레이어인 Mysql 엔진에서 관리되는 테이블 잠금 (LOCK TABLES 명령으로 잠긴 테이블)은 볼 수가 없어서 데드락 감지가 불확실할 수도 있는데,
innodb_table_locks 시스템 변수를 활성화하면 InnoDB 스토리지 엔진 내부의 레코드 잠금뿐만 아니라 테이블 레벨의 잠금까지 감지할 수 있게 된다.
특별한 이유가 없다면 innodb_table_locks 시스템 변수를 활성화하자.

 

#시스템 변수 설정 확인
mysql> show global variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

일반적인 서비스에서는 데드락 감지 스레드가 트랜잭션의 잠금 목록을 검사해서 데드락을 찾아내는 작업은 크게 부담되지 않는다.
하지만 동시 처리 스레드가 매우 많아지거나 각 트랜잭션이 가진 잠금의 개수가 많아지면 데드락 감지 스레드가 느려진다.
데드락 감시 스레드는 잠금 목록을 검사해야하기 떄문에 잠금 상태가 변경되지 않도록 잠금 목록이 저장된 리스트(잠금 테이블)에 새로운 잠금을 걸고 데드락 스레드를 찾게 된다.
데드락 감지 스레드가 느려지면 서비스 쿼리를 처리 중인 스레드는 더는 작업을 진행하지 못하고 대기하면서 서비스에 악영향을 미치게 된다.
이렇게 동시 처리 스레드가 매우 많은 경우 데드락 감지 스레드는 더 많은 CPU 자원을 소보할 수도 있다.

 

#시스템 변수 설정 확인
mysql> show global variables like 'innodb_deadlock%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_deadlock_detect | ON    |
+------------------------+-------+
1 row in set (0.01 sec)

이런 문제점을 해결하기 위해 Mysql 서버는 innodb_deadlock_detect 시스템 변수를 제공하며, innodb_deadlock_detect을 off로 설정하면
데드락 감지 스레드는 더는 작동하지 않게 된다.
데드락 감지 스레드가 작동하지 않으면 InnoDB 스토리지 엔진 내부에서 2개 이상의 트랜잭션이 상대방이 가진 잠금을 요구하는 상황(데드락 상황)이 발생해도
누군가가 중재를 하지 않기 때문에 무한정 대기하게 될 것이다.

하지만 innodb_lock_wait_timeout 시스템 변수를 활성화하면 이런 데드락 상황에서 일정 시간이 지나면
자동으로 요청이 실패하고 에러 메시지를 반환하게 된다. innodb_lock_wait_timeout 은 초 단위로 설정할 수 있으며, 잠금을 설정한 시간동안 
획득하지 못하면 쿼리는 실패하고 에러를 반환한다.
데드락 감지 스레드가 부담되어 innodb_deadlock_detect를 off로 설정해서 비활성화하는 경우라면 innodb_lock_wait_timeout을 기본값인 50초보다 훨씬 낮은
시간으로 변경해서 사용할 것을 권장한다.

ex)
구글(google.com)에서는 pk 기반의 조회 및 변경이 아주 높은 빈도로 실행되는 서비스가 많았는데, 이런 서비스는 매우 많은 트랜잭션을 동시에 실행하기 때문에
데드락 감지 스레드가 상당히 성능을 저하시킨다는 것을 알아냈다. 그리고 Mysql 서버의 소스코드를 변경해 데드락 감지 스레드를 활성화 또는 비활성화할 수 있게 변경해서 사용했다.
이 기능의 필요성을 인지하고 오라클에 이 기능을 요청해서 Mysql 서버에 추가된 것이다. 
만약 PK 또는 secondary index를 기반으로 매우 높은 동시성 처리를 요구하는 서비스가 있다면 innodb_deadlock_detect를 비활성화해서 
성능비교를 해보는 것도 새로운 기회가 될 것이다.

 

정리하면
innodb_deadlock_detect는 즉시 데드락 감지해서 처리하고
innodb_lock_wait_timeout은 설정된 시간(default 50초)만큼 기다렸다가 처리한다.

구글관련 예시를 저자가 들고 있긴 한데 좀 찾아보니 관련 시스템 변수를 직접 테스트한 내용도 있어서 추가한다.

#참조 
https://fromdual.com/innodb-deadlock-detect-rather-hands-off

innodb_deadlock_detect - Rather Hands off!

 

#요약
INNODB_DEADLOCK_DETECT - 차라리 손을 떼!


최근에 우리는 새로운 고객이 있었는데, 그는 때때로 그가 이해하지 못하는 엄청난 데이터베이스 문제를 겪었습니다. 
MySQL 구성 파일(my.cnf)을 검토한 결과 이 고객이 Innodb_deadlock_detect를 비활성화한 것으로 확인되었습니다.

우리는 지금까지 이것을 하지 말라고 충고했지만, 나는 실제로 이 문제를 우연히 발견한 적이 없기 때문에, 
나는 nodb_deadlock_detect의 MySQL 변수에 대해 조금 더 조사했습니다.


InnoDB Deadlock 탐지를 비활성화하는 기능은 WebScale용 Facebook에서 개발되었습니다
.
..
...
즉, InnoDB Deadlock 탐지를 비활성화하는 것은 흥미롭습니다. 
만약 당신이 facebook 만큼이나 가지고 있다면 말이죠. 거의 또는 현재 충돌하지 않을 것으로 예상되는 짧고 작은 트랜잭션입니다.
그 경우엔 MySQL 변수 innodb_lock_wait_timeout을 매우 작은 값(몇 초)으로 설정하는 것이 좋습니다.

그러나 대부분의 고객들이 Facebook과 같은 크기를 가지고 있지 않고, 
동시에 발생하는 짧고 작은 거래가 많지 않고, 적지만 긴 거래(잠금이 많고 따라서 교착 상태가 발생할 확률이 높음)를 가지고 있기 때문에, 
저는 짐작해 볼 수 있습니다, 이 매개 변수를 비활성화한 것은 고객 시스템의 히크업(잠금이 쌓이는 중)의 원인이었습니다.
이는 max_connections를 초과하게 되고 마지막으로 전체 시스템이 고착됩니다.

따라서 InnoDB Deadlock 탐지를 활성화할 것을 강력히 권장합니다. 
(약 2주간의 광범위한 테스트 및 측정 후) 자신이 무엇을 하고 있는지 정확히 알고 있다는 점만 제외합니다.



#결론

구글이나 페이스북 같이 엄청난 동시성 트랙잭션을 처리하는 서버를 제외하고는
웬만하면 innodb_deadlock_detect=on 으로 두자!

끝.