MySQL Lock wait timeout exceed

java BatchUpdateException 문제 해결

MySQL 데이터베이스를 조작하는 Java Batch 프로그램에서 간헐적으로 “MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction” 예외가 발생하는 경험을 했습니다. 항상 나는 것은 아니고 100번 돌면 1번 정도 발생하네요. 

원인이 궁금해서 관련 지식을 찾아 봤습니다.

트랜잭션 수행 시간이 길다.

일반적으로 Lock timeout이 발생하는 것은 단일 트랜잭션의 수행 시간이 긴 경우입니다. 특히 배치성 작업은 트랜잭션 수행 시간에 특히 주의해야 합니다. 만일 트랜잭션 수행 시간이 길다면 트랜잭션을 분리하기 바랍니다.

1. innodb lock wait timeout 값이 작게 설정

설정 중에서 ‘innodb lock wait timeout’ 이 작게 세팅되어 있으면 timeout 예외가 발생합니다.

MySQL Workbench로 개발 DB에 접속해서 wait timeout 값을 확인해 보았습니다.      

SELECT @@innodb_lock_wait_timeout;

10 

10초로 설정되어 있네요.

wait timeout 설정을 변경해 보겠습니다.      

SET GLOBAL innodb_lock_wait_timeout = 20; 

을 실행하면 됩니다. 세션에만 적용하려면 ‘GLOBAL’을 ‘SESSION’으로 바꿔서 실행하면 됩니다.

2. Isolation level과 관련

RDBMS의 Isolation level과 관련해서 SELECT 문의 실행 시간이 오래 걸리는 경우에 timeout exceeded가 발생할 수 있습니다. 여러 isolation level 중에서 주의 깊게 봐야 할 것은 “READ COMMITTED”과 “REPEATABLE READ”입니다.

1) READ COMMITTED

READ COMMITTED는 commit 된 데이터만 읽는 것을 허용하는 레벨입니다. 읽기의 경우, SELECT 문장이 수행되는 동안 해당 데이터에 Shared lock이 걸리고 SELECT가 완료되면 Lock이 해제되는 것을 말합니다. 쓰기의 경우, 데이터를 변경하는 동안 다른 트랜잭션은 해당 데이터를 변경할 수 없고 wait 하는 것입니다. 정리하면 읽기는 SELECT, 쓰기는 트랜잭션 단위로 lock이 걸린다는 것입니다. Oracle의 Default level입니다.

2) REPEATABLE READ

REPEATABLE READ은 트랜잭션 첫 SELECT에서 해당 데이터에 Shared lock을 걸고 데이터의 Snapshot을 생성합니다. 이후 동일 트랜잭션 내의 SELECT는 Snapshot에서 읽게 됩니다. MySql의 Default level입니다.

아래 이미지를 보면 쉽게 이해가 될 것입니다. 출처는 Chunge’s DB입니다.

그렇다면 wait timeout과 isolation level 간의 어떤 상관관계가 있을까요?

Mysql은 Isolation level이 “REPEATABLE READ”입니다. 어떤 트랙잭션에서 SELECT를 해서 Snapshot을 만드는 시간이 오래 걸려 timeout 설정값을 넘기게 되면 Lock wait timeout exceeded이 발생하게 됩니다. snapshot 생성 시간 동안 lock을 걸기 때문입니다.

해결방법은 isolation level을 변경하는 것입니다.

아래와 같이 my.cnf를 수정하고 DB를 재시작하면 설정이 반영됩니다.      

$ vi /etc/my.cnf 
transaction-isolation = READ-COMMITTED 

Java 소스 코드 레벨에서도 isolation level을 지정할 수 있습니다.      

@Transactional(isolation = Isolation.READ_COMMITTED) 

정리

RDBMS에 대한 의존도가 높은 우리나라 IT 환경에서 Lock timeout 관리와 Slow query 관리는 개발자의 능력을 판별할 수 있는 좋은 척도입니다. 본문에서 언급한 3가지 방법은 가장 기초적인 방법이고 그 밖에도 전문 스킬이 많이 있습니다.

그전에 상태를 모니터링하는 것도 중요하다고 생각합니다. New Relic 같은 APM 툴이 큰 도움을 줄 수 있을 것 같습니다.