[MySQL] Lock wait timeout exceeded

기존에 정상적으로 동작하던 API에서 간헐적으로 Timeout Error가 발생하기 시작했다.

Cloudwatch Log에서 다음과 같은 에러 메시지를 확인하였고, 외부 연동 포인트가 아닌 DB에서 발생한 문제 임을 알게 되었다.

Lock wait timeout exceeded; try restarting transaction

Lock Timeout이 발생하는 원인

1. Transaction 수행 시간이 긴 경우

Lock Timeout이 발생하는 것은 하나의 Transaction이 처리되는 데에 시간이 오래 소요되는 경우이다.

 

실제로 에러가 간헐적으로 발생하는 API는 Bulk 요청이 주로 이뤄지는 API들이다.

Bulk 요청이기 때문에 Transaction 관리를 보다 철저하게 처리해주었는데, Transaction에 따라 발생한 DB Lock으로 인해 새로운 Query의 요청이 대기 상태에 있었다가 Timeout이 발생했을 수도 있다.

2. innodb lock wait timeout 값이 작게 설정된 경우

만약 DB를 설정하던 시점에 innodb lock wait timeout 값이 매우 작게 셋팅되어 있었다면, timeout이 발생할 우려가 크다.

3. isolation level의 이슈인 경우

Isolation Level은 Transaction에서 일관성 없는 데이터를 허용하는 수준에 대한 설정을 의미한다.

 

Isolation Level에는 다음의 4가지 단계가 있다.

• Read Uncommitted : Transaction이 끝나지 않은 상황에서 다른 Transaction의 변경사항에 대한 조회가 가능하다.
• Read Committed : Transaction이 끝나지 않은 데이터에 대한 조회(Select)의 경우 Shared Lock이 발생하며, Commit된 데이터만 조회가 가능하다.
• Repeatable Read : Transaction의 Id를 기준으로 생성된 Snapshot 범위 내에서 데이터를 조회하기 떄문에 내용이 항상 동일함을 보장해준다.
• Serializable : 가장 엄격한 Isolation Level로 모든 Transaction을 직렬화하여 처리한다.

Timeout Exceed가 발생 가능한 가장 유력한 상황은 위에서 Repeatable Read이다.

 

Repeatable Read는 Transaction의 첫 SELECT에서 해당 데이터에 Shared Lock을 걸고 데이터의 Shapshot을 생성 및 기록한다.

이후 동일 Transaction내의 SELECT는 Shanpshot에서 읽게 된다.

 

Transaction에서 Select를 해서 snapshot을 만드는 시간이 오래 걸려서 timeout 설정값을 넘기게 되면 Lock Wait Timeout Exceeded가 발생하게 된다.

Snapshot 생성 시간 동안 Lock을 걸게 되기 때문이다.

 

Lock Timeout을 해결하기 위한 방법

위에서 작성한 발생가능한 원인을 기준으로 2가지 해결 방법을 떠올릴 수 있을 것이다.

1. Code Level에서의 해결 방법

Transaction의 수행 시간이 길다는 것은 Code 레벨에서 Biz Logic을 처리하는 과정에서 불필요하게 Transaction을 크게 잡았을 수도 있다는 것이다.

적절한 단위 내에서 Transaction을 설정하는 것이 필요하기 때문에 반복적으로 Timeout이 발생하는 API나 Query가 있다면 해당 부분의 Biz Logic을 검토해보는 것이 좋다.

 

단순 코드 상의 누락 사항으로는 Transaction의 시작과 종료(Commit / Rollback)에 대한 처리가 부족했기 때문일 수도 있으니 코드 상의 오류는 없는지 확인해보아야 한다.

2. DB Level에서의 해결 방법

만약 코드 상의 문제가 없다면, 위에서 언급한 2가지의 설정값을 변경해주는 것이 좋다.

innodb lock wait timeout이 지나치게 작게 설정되어 있다면 아래의 명령어를 통해 설정을 변경해줄 수 있다.

SET GLOBAL innodb_lock_wait_timeout=20;

다음으로 Isolation Level을 조정하는 방법이 있다.

참고로 MySQL은 Repeatable Read가 Default Level이고, Oracle은 Read Committed가 Default Level이기 때문에 현재의 설정값을 변경해서 확인을 해보는 방법이 있다.