[ErrorLog] CannotAcquireLockException

 

에러발생

빈번한 에러 발생으로 서버가 다운되는 현상이 발생했는데 CannotAcquireLockException이 반복해서 발생하는 것을 로그를 통해 알 수 있었다.

2023-02-09 10:32:02.577  WARN 3028 --- [o-8080-exec-609] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1205, SQLState: 40001
2023-02-09 10:32:02.577 ERROR 3028 --- [o-8080-exec-609] o.h.engine.jdbc.spi.SqlExceptionHelper   : Transaction (Process ID 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
2023-02-09 10:32:02.579  WARN 3028 --- [o-8080-exec-609] .m.m.a.ExceptionHandlerExceptionResolver : Resolved [org.springframework.dao.CannotAcquireLockException: could not execute query;

 

CannotAcquireLockException 는 왜 발생하는 걸까?

package org.springframework.dao;

@SuppressWarnings("serial")
public class CannotAcquireLockException extends PessimisticLockingFailureException {

	public CannotAcquireLockException(String msg) {
		super(msg);
	}

	public CannotAcquireLockException(String msg, Throwable cause) {
		super(msg, cause);
	}

}

 

업데이트 과정에 실패를 던지는 것으로 업데이트에서 lock이 걸렸을 때 조회를 하는 경우 발생된다고 명시되어 있다. ( "select for update" 상태)

나의 경우에도..

주로 Trascation Error가 발생하는 부분은 Select 쿼리를 실행하려고 할 때 였는데, 빈번한 Insert / Update가 발생하는 테이블을 조회하는 과정에서 발생했다는 것을 알 수 있었다. 이렇게 트랜잭션이 실행되지 못하고 교착상태(DeadLock) 상태에 빠지게 되는 것을 알 수 있었다.

특히나 조회구문이 배치를 통해 반복적으로 트랜잭션을 일으키기 때문에 더 문제가 컸던 것 같다.

 

그래서 어떻게 해결해야 할까?

대용량 서비스를 관리하는 경우에서 보면, slave 서버를 두거나, read만 가능한 DB를 구축해서 조회만 수행하고, update 시에는 두 DB가 동일하게 update가 될 수 있도록 하는 등 다양한 방법이 존재한다. 하지만 일단 이건 아주 큰 대용량 서비스의 경우고, 비용적으로 해결할 수 없는 프로젝트여서 다른 방안을 찾아야 했다.

 

여러가지 방안을 고려했는데 다음과 같다.

 

1. Lock이 풀릴 때까지 대기하자

@Retryable(value = Exception.class, maxAttempts = 5, backoff = @Backoff(0))

결과적으로 한정된 자원을 얻지 못해 생기는 경우이므로 한정된 자원을 다 쓰고 다음 트랜잭션이 쓸 때까지 기다리는 대기 상태를 만드는 것이다.

 

2. Read_Commit_Snapshot ON처리하기

ALTER DATABASE <database name>
   SET READ_COMMITTED_SNAPSHOT ON
   WITH ROLLBACK IMMEDIATE;

update와 상관없이 이전 tempDB를 가지고 와서 결과값을 조회할 수 있도록 하는 방법이 존재했다. 이 방법은 조금 부정확할 수 있다. 왜냐면 update 이후의 결과값이 실제와 상이할 수도 있기 때문이다.

 

3. Lock 걸리는 컬럼의 값에 인덱스 설정하기

조건절에 해당하는 컬럼에 인덱스를 설정해서 ROW에 lock이 걸리는 것이 아니라 해당 인덱스 key값에 lock이 걸리도록 변경하는 방법이 있다.

이 경우는 조건절에 인덱스 설정할 컬럼이 분명하고 오히려 서비스 속도에 문제를 발생시킬 원인이 되지 않아야 했다. 해당 쿼리에서는 조건이 여러 개이고, 이미 테이블에 인덱스가 여러 개 걸려 있어서 좋은 방안이 되지 못했다.

 

어떻게 해결했는지

WITH(NOLOCK)

락이 걸리는 부분을 봤더니 주로 S LOCK과 X LOCK 또는 S LOCK과 IX LOCK의 교착상태에서 발생한 경우가 많았다. (지금 프로젝트는 MSSQL을 사용중이다.)

 

 

하지만 대기를 걸기에도 계속적으로 요청이 뒤로 밀리기만 하고 문제 해결은 쉽지 않아보여서 차라리 S LOCK 설정을 하지 않는 방향으로 진행하기로 했다. SELECT 하는데 과도한 리소스 사용이 좋아보이지 않았기 때문이다.

더 좋은 방법이 있으면 다시 적용해봐야겠다.