MySQL 잠금 대기 시간 초과 오류

 

 

 

애플리케이션을 구동 시켰는데 뜻밖의 오류를 발견했다.

API가 정상적으로 작동하지 않았는데, 디버깅모드로 확인해보니 잠금 대기 시간 초과라는 오류를 처음 보게되었다.

Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1109)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1057)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1377)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1042)
at net.sf.log4jdbc.sql.jdbcapi.PreparedStatementSpy.executeUpdate(PreparedStatementSpy.java:1080)
at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:175)
... 77 common frames omitted

 

이 오류에 대한 원인은 세 가지로 볼 수 있다고 한다.

 

1. 다른 트랜잭션이 이미 해당 데이터에 대해 잠금을 설정한 경우, 현재 트랜잭션이 해당 데이터를 수정하려고 할 때
2. 잠금을 기다리는 동안 지정된 시간(타임아웃)을 초과
3. 데이터베이스의 잠금 전략이나 설정이 비관적 잠금을 지원하지 않는 경우

 

웹서칭을 해봤을 때 첫 번째와 두 번째 원인이 가장 유력한 것으로 확인되었다.

위 두가지 원인에 대한 해결 방법을 확인해볼 것이며, 애플리케이션의 사용 목적에 따라 해결 방법이 달라질 수 있을 것으로 보인다.

 

 

 

 

 

---

 

 

 

 

 

 

1. 트랜잭션에 의한 오류 해결 방법

 

트랜잭션의 오류일 경우 자바 파일을 수정해주어야 한다.

디버깅 모드로 확인 시 특정 메서드 내에서 오류가 발생하는 것을 확인할 수 있었는데,

@Transactional 어노테이션이 설정되어있는 메서드였다.

@Transactional(propagation = Propagation.REQUIRED, rollbackFor = {BizException.class, Exception.class})

 

 

위와 같이 설정되어있는 부분을 아래와 같이 _NEW 로 변경해주었다.

@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = {BizException.class, Exception.class})

 

 

_NEW와의 차이는 아래와 같고, 메서드의 사용 목적에 따라 적용 여부를 결정해주어야 할 것 같다.

 

옵션	설명
REQUIRES	기존 트랜잭션 사용: 현재 실행 중인 트랜잭션이 있으면 그 트랜잭션 내에서 실행됨. 만약 트랜잭션이 없다면 새로운 트랜잭션이 생성됨 트랜잭션 겹침: 여러 메서드가 호출될 때, 같은 트랜잭션 내에서 실행되므로, 트랜잭션이 롤백되면 모든 작업이 롤백됨
REQUIRES_NEW	새로운 트랜잭션 생성: 항상 새로운 트랜잭션을 생성함. 현재 실행 중인 트랜잭션이 있다 하더라도, 이를 일시 중지하고 새로운 트랜잭션을 시작함. 독립적인 트랜잭션: 새로운 트랜잭션이 롤백되더라도, 기존 트랜잭션은 영향을 받지 않음

 

 

 

 

 

 

 

---

 

 

 

 

 

 

2. 타임아웃 초과 해결 방법

 

이럴 경우 타임아웃 시간을 늘려주면 된다고 하는데, innodb_lock_wait_timeout 이 변수의 값을 늘려주면 된다고 한다.

 

 

먼저 MySQL 워크벤치를 켜준다.

이후 문제가 있는 서버에 접속해준 뒤, 상단 메뉴에서 Server - Status and System Variables를 클릭해준다.

 

 

 

이후 Status Variables - InnoDB/Stats 에서 Innodb_row_lock_time_avg 값을 확인해보면 정상적인 서버에 비해 비이상적으로 높은 수치인 것을 확인할 수 있다.

문제가 발생한 서버

 

정상적인 서버

 

 

 

 

위 값을 확인해보았다면, 이제 innodb_lock_wait_timeout 라는 변수의 값을 증가시켜줘야 한다.

워크벤치에서 간단하게 아래 명령어를 통해 현재 값을 확인 가능하지만, SET을 통해 세팅할 경우 서버 재시작 시 초기화 되므로 설정 파일의 값을 변경해주어야 한다.

(너무 많이 늘리면 정상적인 작동이 불가할 수 있기 때문에 점차 늘려가는 것을 추천한다!)

 

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

 

 

 

---

 

 

1. DB 서버에 ssh 접속이 가능한 경우

 

my.cnf 또는 my.ini 파일의 아래 값이 보통 기존의 경우 50이였을텐데, 원하는 만큼 늘려주면 된다.

[mysqld]
innodb_lock_wait_timeout = 600

 

 

---

 

 

2. AWS의 RDS로 구축한 경우 (ssh X)

 

RDS로 구축한 경우 SSH 서버에 MySQL을 따로 설치한 것이 아니기 때문에 SSH로 접속할 방법이 없다.

이럴 경우 AWS 콘솔 페이지에 접속해서 문제가 발생한 서버의 RDS 인스턴스를 클릭한다.

 

이후, 구성 탭에 보면 DB 인스턴스 파라미터 그룹 이라는 항목이 존재한다.

이걸 클릭해보면 파라미터들이 나오는데 여기서 innodb_lock_wait_timeout 항목을 찾은 후 변경해주면 된다.

 

 

 

---

 

 

 

위 두 가지 방법을 통해 세팅값을 변경해주었다면, 마지막으로 MySQL 서버 재시작이 필요하다.

ssh의 경우 아래 명령어를 실행해주면 되고, RDS의 경우 인스턴스 페이지에서 재시작 버튼을 클릭해주면 된다.

sudo service mysql restart