Lock wait timeout exceeded 에러

최근에 MySQL에 마이그레이션 작업을 위해 대량의 데이터를 입력하던 중에 갑자기 모든 데이터가 Rollback 되는 현상이 있었습니다. 로그에는 Lock wait timeout 라고 적혀 있었는데요. 간단히 이 메세지에 대해서 알아보겠습니다.

이 Lock wait timeout excceded 메세지는 DBMS가 트랜젝션을 위해 잠금(Lock)한 시간이 제한 시간을 넘었다는 것인데 설정 파일에서 값을 지정하지 않았다면 기본 값은 50초 입니다.

이 에러 메세지가 나타나는 경우는 두 가지가 있을 수 있습니다. 첫 번째는 deadlock 상태가 됐을 경우이고 두 번째는 너무 오랜 시간 lock 이 걸려있는 경우 입니다.

첫 번째,

MySQL에서 서로 다른 storage engine 을 사용하는 테이블 끼리 작업을 수행했을 때 deadlock 상태에 빠지기 쉽습니다. 예컨데 동일한 데이터베이스에 각기 다른 storage engine을 사용해 테이블을 구성했다면 MyISAM 엔진을 사용한 테이블은 table lock 이고 InnoDB 엔진을 사용한 테이블은 row lock 이기 때문에 이러한 테이블 간에 deadlock 이 쉽게 발생할 수 있습니다.

두 번째,

만약 100개의 데이터를 INSERT 한다면 일반적으로 맨 앞에 BEGIN 을 실행하고 마지막에 COMMIT 을 실행할 것입니다. 그런데 100개의 데이터가 아니라 1,000개, 10,000개의 데이터를 한번의 트랜젝션으로 INSERT 또는 UPDATE를 하려 한다면 기본 제한 시간인 50초를 넘을 수 있기 때문에 에러 메세지를 출력한 후 전부 Rollback 될 것입니다.

해결 방법은 두 가지가 있을 것 같은데요. 첫 번째는 my.ini ( 또는 my.cnf ) 설정 파일을 열고 innodb_lock_wait_timeout 값을 충분하게 늘려 주는 것입니다. 이 방법은 아주 간단하게 문제를 해결 할 수는 있지만 위에서 언급했던 deadlock 같은 상황이 발생하면 큰 문제가 될 수 있으므로 신중하게 고려해야 하는 방법인 것 같습니다. 두 번째 방법은 적당한 수만큼 SQL 쿼리를 나누어 실행하는 것입니다. 예를 들면 1,000개의 INSERT 쿼리라면 100개씩 나누어서 실행하는 것입니다.