MySQL 데드락 자동 감지 및 비활성화 시 대응

데드락

데드락(Deadlock)은 데이터베이스에서 여러 트랜잭션이 서로가 가진 잠금을 기다리면서 무한정 대기하게 되어 데이터베이스의 작업이 더 이상 진행되지 않는 상황을 의미한다. 멀티 스레드(Multi-threaded) 어플리케이션에서 발생하는 데드락은 해당 어플리케이션을 완전히 멈추게 해버리기 때문에 위험하다.

따라서 데드락이 발생하면 어떻게 대응할 수 있을지를 알아야 한다. 데드락이 발생하면 어플리케이션 단에서 커넥션을 계속 물고 있고, 클라이언트 입장에서는 5초 동안 응답이 안오면 재시도할 가능성이 높다. 그러면 똑같은 레코드에 똑같은 이유로 데드락이 걸릴 확률이 높고, 커넥션 풀의 커넥션이 점점 부족해지는 문제가 발생할 가능성이 높아진다.

실제 데드락 상황이 아닐지라도 락에 대한 대기시간이 설정된 시간을 초과하면 데드락으로 처리하는 방식으로 일반적인 DBMS에서는 데드락 탐지(Deadlock detection) 기능을 제공한다. 이 과정에서 작업중이던 트랜잭션들 중 일부가 취소되는 경우가 발생할 수 있기 때문에 어플리케이션 레벨에서 해당 트랜잭션을 재실행하여 작업을 완수할 수 있도록 구성해야 한다.

 

락이란?

상품 구매 트랜잭션으로 보는 부정합 문제

데드락에 대해서만 설명했는데 락에 대해서 가볍게 설명하자면 트랜잭션 내에서 접근한 레코드에 대해서 다른 트랜잭션에서 읽기 또는 쓰기 작업을 제어함으로써 동시성을 제어하고 싶은 경우에 사용하는 것이다. 이러한 락에는 공유락(Shared Lock, S-Lock)과 배타락(Exclusive Lock, X-Lock)이 있으며 S-Lock끼리는 다른 트랜잭션에서의 접근을 허용하지만 S-Lock과 X-Lock 또는 X-Lock끼리의 접근은 허용하지 않는다. 이렇게 다른 트랜잭션에서 락을 가짐으로써 락 대기 상태로 있는데 이로 인해 발생할 수 있는 상황이 데드락이다. 자세한 내용은 위 블로그를 보자.

 

자동 데드락 감지

InnoDB 스토리지 엔진은 내부적으로 잠금이 교착 상태에 빠지지 않았는지 체크하기 위해 잠금 대기 목록을 그래프(Wait-for List) 형태로 관리한다. InnoDB 스토리지 엔진은 데드락 감지 스레드를 가지고 있어서 데드락 감지 스레드가 주기적으로 잠금 대기 그래프를 검사해 교착 상태에 빠진 트랜잭션들을 찾아서 그 중 하나를 강제 종료한다. 이 때 언두 로그 레코드를 더 적게 가진 트랜잭션이 일반적으로 롤백의 대상이 된다. 트랜잭션이 언두 레코드를 적게 가졌다는 이야기는 롤백을 해도 언두 처리를 해야 할 내용이 적다는 것이며, 트랜잭션 강제 롤백으로 인한 MySQL 서버의 부하도 덜 유발하기 때문이다. 이러한 데드락 감지 스레드는 innodb_deadlock_detect 시스템 변수를 통해 설정할 수 있다. 

 

테이블 잠금 감지

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

 

자동 데드락 감지 문제

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

 

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

 

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

 

자동 데드락 감지 예제

위 상품 테이블이 있다고 가정해보자.

 

transaction_a와 transaction_b가 동시에 트랜잭션을 시작했다고 가정해보자. 그러면 InnoDB 스토리지 엔진은 transaction_a에 대해 id BETWEEN 15 AND 18을 통해 레코드 락을 걸게 된다. transaction_b도 마찬가지로  20<=id<=23에는 레코드 락을 건다. 그리고 결과로 레코드가 네 개씩 조회되는 것을 확인할 수 있다.

 

이 때 transaction_a에서 SELECT ... FROM product WHERE id = 22 FOR UPDATE;를 실행하면 transaction_b에서 배타락을 가지고 있기 때문에 잠금 대기 상태인 것을 확인할 수 있다.

 

그리고 transaction_b에서도 SELECT ... FROM product WHERE id = 16 FOR UPDATE;를 실행하면 자동 데드락 감지로 인해 두 트랜잭션이 데드락 상태에 빠지지 않고 transaction_b에서 에러가 발생하면서 해당 트랜잭션의 락들이 반납되고, transaction_a에서 락을 가져가 조회되는 것을 확인할 수 있다.

 

자동 데드락 감지 비활성화 예제

SET GLOBAL innodb_deadlock_detect=off;
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

자동 데드락 감지 비활성화를 하려면 위 명령어를 통해 설정할 수 있다. 그리고 확인해보면 Value 컬럼이 OFF인 것을 확인할 수 있다.

 

SELECT * FROM information_schema.INNODB_TRX 조회

그리고 위 예시와 동일하게 transaction_a와 transaction_b를 시작하고 SELECT 쿼리까지 실행한 후 SELECT * FROM information_schema.INNODB_TRX;로 조회하면 두 trasaction에 대한 정보가 나오는 것을 확인할 수 있다.

 

그리고 계속해서 실행하다보면 서로가 가진 잠금을 기다리면서 무한정 대기하는 데드락 상황에 빠지게 되는데 두 트랜잭션 모두 trx_status가 LOCK WAIT, trx_requested_lock_id가 NULL이 아닌 것을 확인하여 Lock이 대기중임을 알 수 있다.

이렇게 무한정 대기하는 데드락 상황을 해결하기 위해 위에서 살펴본 innodb_lock_wait_timeout 시스템 변수로 지정한 시간만큼 대기하면 데드락 감지 기능을 활성화했을 때와 같은 에러가 발생하게 된다.

 

SELECT * FROM performance_schema.data_locks 조회

위 명령어를 실행하면 performance_schema.data_locks 테이블을 통해 InnoDB에서 발생한 락 정보를 조회할 수 있다.

LOCK_MODE 컬럼은 InnoDB에서 특정 레코드에 대한 락 모드를 나타내는 값을 말한다. S는 Shared Lock, X는 eXclusive Lock을 나타낸다. FOR UPDATE를 통해 배타락으로 조회한 레코드들에 대해서 다른 트랜잭션에서 배타락으로 접근하려고 하면 위를 보는 것처럼 LOCK_MODE 컬럼이 X, LOCK_STATUS가 WAITING인 것을 확인할 수 있다. 

 

performance_schema의 data_locks 테이블과 data_lock_waits 테이블 조인해서 잠금 대기 순서 조회

SELECT 
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id wating_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b
	ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
	ON r.trx_id = w.requesting_engine_transaction_id;

쿼리의 실행 결과를 보면 현재 대기 중인 스레드는 10번과 11번인 것을 확인할 수 있다. 그런데 11번 스레드는 10번 스레드를 기다리고 있고, 10번 스레드는 10번을 기다리고 있다. 이는 잠금 대기 큐의 내용을 그대로 보여주고 있고, 서로가 가지고 있는 락을 기다리기 때문에 발생하는 데드락인 것을 확인할 수 있다.

 

SHOW ENGINE INNODB STATUS 조회

SHOW ENGINE INNODB STATUS 명령어를 통해서도 TRANSACTION 부분을 통해 현재 세션들의 트랜잭션 목록들을 확인할 수 있다. 첫 트랜잭션을 살펴보면 총 7개의 락을 가지고 있고, heap size는 1128, 그 외에 MySQL 스레드 id, OS 스레드 핸들, query id 등의 정보를 확인할 수 있다. 또한 TRX HAS BEEN WAITING 40 SEC FOR THIS LOCK TO BE GRANTED를 통해 해당 락이 40초 동안 허용되기를 기다리고 있고 있는 것을 알 수 있다.

 

만약 인덱스가 없는 컬럼을 조건으로 락을 획득한다면?

이 부분은 락 조회하면서 실제로 인덱스가 걸려있지 않는 컬럼을 조건으로 배타락이든 공유락이든 획득하게 되면 테이블 전체 레코드에 대해서 락을 획득함으로써 다른 트랜잭션에서 접근하려는 것을 락 대기 상태로 만드는 것을 확인할 수 있다.(물론 공유락-공유락은 바로 접근이 가능하다.)

 

출처

Real MySQL 8.0 1권 : 개발자와 DBA를 위한 MySQL 실전 가이드

MySQL InnoDB lock & deadlock 이해하기