Spring Boot 서비스 운영중 다음과 같은 에러가 발생하며, SQL문이 실행되지 않았다.
### Error updating database. Cause: java.sql.SQLException: (conn=1666666) Lock wait timeout exceeded; try restarting transaction
### The error may exist in class path resource [mappers/Mapper.xml]
### The error may involve com.test.sample.mappers.Mapper.updateColumns-Inline
### The error occurred while setting parameters
### SQL: UPDATE SAMPLETABLE SET UPD_ID = ?, UPD_DT = current_timestamp() WHERE ID = ?
### Cause: java.sql.SQLException: (conn=1666666) Lock wait timeout exceeded; try restarting transaction
; (conn=1666666) Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: (conn=1666666) Lock wait timeout exceeded; try restarting transaction
위 SQL문을 수행하기 위해 lock이 필요하여 기다리던 중 타임아웃이 발생했다는 뜻!
트랜잭션이 할당된 제한 시간 내에 행 또는 테이블에 잠금을 얻지 못할 때 발생한다. 동시에 같은 데이터에 액세스하는 여러 트랜잭션이 있는 다중 사용자 환경에서 발생할 수 있다.
아래 쿼리문을 실행하면 아래처럼 제한 시간을 확인할 수 있다.
현재 InnoDB를 사용중이라면 innodb_lock_wait_timeout의 default 값인 50sec만에 타임아웃이 일어나게 된다.
*DB 엔진 확인 방법은 맨 아래에 있음
SHOW VARIABLES LIKE '%timeout';
Timeout이 발생하는 원인과 해결방법
1. lock wait timeout 값이 작게 설정된 경우
lock wait timeout 값을 default 값보다 작게 설정해놓았을 경우
혹은 lock wait timeout 값에 비해 트랜잭션이 긴 경우
lock wait timeout 값 증가
-- SUPER 권한 필요
SET GLOBAL innodb_lock_wait_timeout = 300; -- 300초(5분)로 설정
SET SESSION lock_wait_timeout = 0; -- no limit다만, 잠금 대기시간 값을 증가시키면 트랜잭션의 대기 시간이 길어져 성능 문제가 발생할 수 있으므로 주의!
2. Transaction 수행시간이 긴 경우 ✨
수행시간이 긴 트랜잭션이 lock을 얻고 오랫동안 lock을 풀지 않으면, 동일한 lock을 얻고자 한 다른 트랜잭션은 타임아웃이 발생한다.
또는 처음에는 데이터가 적어서 문제가 되지 않다가 시간이 지나면서 데이터가 쌓여 문제 되는 경우가 종종 있다.
트랜잭션을 가능한 짧게 유지하여 충돌의 가능성을 줄이는 것이 좋다.
Transaction은 더 이상 쪼갤 수 없는 최소 작업 단위가 되어야 한다. 문제가 발생했을 경우 이전 상태로 rollback 할 수 있도록 설계한다.
응용 프로그램 로직 검토
트랜잭션을 올바르게 관리하고 리소스를 적절히 해제하는지 코드를 검토해볼 필요가 있다.
쿼리 최적화
쿼리 튜닝, 테이블 스페이스 사용으로 쿼리 실행시간을 줄임으로써 잠금경합을 감소시킬 수 있다.
명시적 잠금 사용
기본 잠금 동작에 의존하는 대신 명시적 잠금을 사용하여 잠금이 언제 어떻게 획득되는지 제어할 수 있다.
내가 겪은 문제상황
Spring에서 TransactionManager(@Transactional 역할)를 사용하며, impl단에서 여러 쿼리를 실행하는 메소드가 하나의 트랜잭션으로 수행되는 웹서비스를 운영중이었다.이때, [사용자가 몰림 + 다른 배치 프로그램에서 동일한 DB 사용 + 몇 년간 운영하며 데이터가 많이 적재됨]으로 여러 상황이 겹치면서 해당 에러가 발생했었다.
운영중이었기 때문에 대응 방법에 제약이 있었고, 우선은 임시로 다음과 같이 대처했다. 트랜잭션 수행시간 감소를 위해 쿼리를 튜닝하였고, 데이터 백업을 계획하였다. 또, 배치 프로그램 실행과 웹서비스 사용이 동시에 집중되지 않고 적절히 분배되도록 프로그램에 제한을 걸었다. 장기적으로는 프로그램 버전업을 기획 중이다.
3. Isolation level이 문제인 경우
-- 현재 DB의 격리레벨 조회 : 기본적으로 REPEATABLE-READ
-- MySQL 5.7.2 이하
SELECT @@GLOBAL.tx_isolation;
-- MySQL 8.0 이상
SELECT @@GLOBAL.transaction_isolation;Isolation Level은 Transaction에서 일관성 없는 데이터를 허용하는 수준에 대한 설정을 의미한다.
-
Read Uncommitted
: Transaction이 끝나지 않은 상황에서 다른 Transaction의 변경사항에 대한 조회가 가능하다. -
Read Committed
: Transaction이 끝나지 않은 데이터에 대한 조회의 경우 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가 발생하게 된다.
Default Level
- MySQL : Repeatable Read
- Oracle : Read Committed
4. 비정상 종료로 잠김
프로세스 목록을 조회하여 오랫동안 잠금을 갖고있는 프로세스 강제종료
-- 프로세스 목록 조회
SHOW PROCESSLIST;
-- PROCESS 권한 필요
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
SELECT * FROM information_schema.INNODB_TRX;
-- 프로세스 확인
SELECT * FROM information_schema.processlist where id = 175;
-- 프로세스 강제종료
kill 175;
cf) DB 엔진 확인 방법
SELECT engine
FROM information_schema.TABLES
WHERE table_name='테이블명'
AND table_schema='디비명';
-- 또는
SHOW TABLE STATUS WHERE name='테이블명';
*참고사이트
https://jaenjoy.tistory.com/26
https://small-dbtalk.blogspot.com/2015/01/lockwaittimeout-default_9.html
https://adbancedteam.tistory.com/223
https://dev.mysql.com/doc/refman/5.7/en/innodb-information-schema-transactions.html
