[spring]org.springframework.dao.CannotAcquireLockException

문제 상황

유지보수 중인 업체에서 특정 버튼을 클릭하고 해당 작업을 수행하려고 하면 반응이 느리고, 페이지 로딩이 너무너무너무 오래 걸리는 현상이 간헐적으로 발생한다고 함 😭

처음엔 일시적인 문제인 줄 알았는데, 반복적으로 매일 오후에 같은 증상이 나타나는 상황을 발견함.

---

원인 진단 과정

1️⃣ 프로세스 확인

DB의 lock이 걸려있다는 에러를 기반으로, DB 프로세스를 확인해봄.

SHOW FULL PROCESSLIST;

확인해보니 Sleep 상태로 오랫동안 유지되고 있는 세션이 다수 존재 (몇 시간 동안 지속되고 있었음).

2️⃣ 트랜잭션 점검

SELECT * FROM information_schema.innodb_trx;

즉, 작업이 진행 중인 트랜잭션은 없는데도 연결은 종료되지 않고 남아있던 것!!! 😱

👉 원인 : DB connection이 애플리케이션에서 제대로 닫히지 않아 sleep 상태로 남게 된 것 (정확한 이유는 알 수 없음..)

---

조치 내용

1️⃣ 장시간 Sleep 상태였던 세션 수동 종료

KILL 1777;
KILL 1787;

2️⃣ DB의 비활성 세션 자동 종료 시간을 5분으로 변경

SET GLOBAL wait_timeout = 300;

• wait_timeout은 클라이언트가 아무런 요청도 하지 않은 상태로 대기할 수 있는 최대 시간(초 단위)을 의미.
• 300초 = 5분

3️⃣ 모니터링 결과

이후 한 시간 정도 모니터링 해봤는데 일부 세션(1780, 1783, 1784 등)은 ID는 유지되지만 Time만 갱신되고 있었음.
→ 즉, 해당 ID들은 WAS Connection Pool 내에서 관리되는 연결로 추정되고,
그 외 ID들은 비정상적으로 종료되지 않은 오래된 세션으로 판단됨.

---

🔎 확인 명령어 정리

(나는 root 계정도 가지고 있어서 가능했던 부분도 있음)

목적	명령어
현재 연결 상태 확인	SHOW FULL PROCESSLIST;
트랜잭션 진행 상태 확인	SELECT * FROM information_schema.innodb_trx;
커넥션 수 확인	SHOW STATUS LIKE 'Threads_connected';
활성 스레드 확인	SHOW STATUS LIKE 'Threads_running';
자동 종료 시간 확인	SHOW VARIABLES LIKE 'wait_timeout';

---

정리하며 💡

DB에서 나는 문제는 대부분 “조회 쿼리가 너무 오래 걸려서 로딩이 길어지는 경우”가 많았는데, 이번 케이스는 완전히 달랐다 — 구조적인 문제 때문에 일어난 케이스였다.

심지어 MySQL의 기본 설정값 그대로(8시간)인데도 불구하고, 사용자나 네트워크 환경, 또는 애플리케이션 동작 방식에 따라 비정상적으로 종료되지 않은 세션이 계속 쌓이면서 문제가 발생할 수 있다는 걸 알게 됨!

👉 참고로 MySQL의 기본 wait_timeout 값은 28800초 (8시간)이다.

---

추가 조사 : REPEATABLE-READ 관련

혹시나 MySQL의 REPEATABLE-READ (기본 격리 수준) 때문인가? 🤔 해서 따로 찾아보았다.

REPEATABLE-READ 특성

• 한 트랜잭션에서 동일한 SELECT를 여러 번 실행하면 같은 결과를 보장.
• 이를 위해 InnoDB는 다른 트랜잭션에서 참조 중인 레코드에 잠금을 걸 수 있음.
• INSERT ... SELECT / CREATE TABLE AS SELECT 같은 전체 테이블 참조 시, MySQL이 해당 테이블을 스냅샷/잠금 처리함.
• 이때 동시에 다른 트랜잭션이 같은 테이블에 INSERT, UPDATE, DELETE를 시도하면 대기(lock) 상태가 될 수 있음.

결국, 이미 실행 중인 트랜잭션이 테이블을 잠근 상태에서 다른 요청이 들어오면 SLEEP 상태로 대기하는 현상이 발생할 수 있다.

근데 나는 INSERT ... SELECT나 CREATE TABLE AS SELECT처럼 다른 테이블을 참조하는 쿼리를 쓰지 않아서, 이 부분은 직접적인 원인은 아니었다고 판단됨.
(그래도 이런 경우도 있구나~ 하고 정리만 해둠 ㅎㅎ)

---

결론적으로, Sleep 세션이 많고 페이지가 느려진다면, 쿼리 튜닝보다는 커넥션 관리 로직부터 점검해보는 게 좋다는 교훈을 얻음 👍