MySQL 트랜잭션 lock 충돌 오류 개선하기

1) 서론

이번 글에서는 MySQLTransactionRollbackException의 발생 원인을 분석하고 개선 방안을 고민해보는 글입니다. 그리고 JPA를 사용하다 보면 동일한 상황에서 발생하는 PessimisticLockingFailureException도 함께 알아보려고 합니다.

 

나름대로의 방법을 찾았지만, 더 좋은 아이디어를 댓글로 주신다면 감사히 받겠습니다.

 

---

2) MySQLTransactionRollbackException가 발생하는 이유

아래와 같은 상황을 가정합니다.

TR은 transaction의 약자로 사용합니다.

• TR1 test 테이블 데이터 전체 삭제
• TR1 lock 획득 후 실행 중 TR2 삭제 요청

 

이때 TR2는 실행될 수 있을까요?

 

아래와 같이 코드를 작성합니다.

• TR1: 테이블 전체 삭제 요청
• TR2: 테이블의 특정 조건 값 삭제
• 비동기 테스트 시 결과 보기 전 종료되는 것을 방지하기 위해서 단순 sleep 사용합니다

 

위의 코드는 어떻게 진행될까요?

• 첫 번째 트랜잭션이 진행되고, 두 번째가 순차적으로 수행될까요?
• 첫 번째 delete 중 두 번째 트랜잭션의 delete가 별도로 수행될까요?
• 혹은 무언가 실패하게 될까요?

• MySQLTransactionRollbackException, CannotAcquireLockException 발생

• MySQL이 MySQLTransactionRollbackException를 발생시킵니다

• jdbcTemplate.execute에서 SQLException을 catch 합니다
• MySQLTransactionRollbackException의 최상위 예외는 SQLException입니다

• 위의 catch문에서 다시 발생시키는 translateException에 의해 CannotAcquireLockException을 발생시킵니다

 

원인은 뭘까요?

 

상황은 아래와 같습니다.

• TR1이 전체 삭제를 위한 pessimistic lock(비관적 잠금)을 획득합니다
• TR2도 특정 row를 삭제하려고 하지만 이미 해당 row에 lock이 걸려있어서, 대기합니다
• 아무리 시간이 흘러도 TR2는 lock을 획득할 수 없어 exception을 발생시킵니다

 

그렇다면 lock 획득까지 얼마나 대기할 수 있는 걸까요?

 

아래는 MySQL innoDB 엔진에 대한 설정값입니다. 어딘가 익숙한 예외 메시지가 보입니다.

 

그중 innodb_lock_wait_timeout 설정을 보려고 하는데요. 다른 트랜잭션에 의해서 lock 되어 있는 row에 접근하기 위해 대기하는 시간입니다. 그리고 대기 시간은 default 50초로 설정되어 있습니다. 

 

즉 위의 상황에서 TR2는 50초 동안 대기하고 exception을 발생시킬 수밖에 없었습니다.

---

3) 그래서 PessimisticLockingFailureException는 뭔가요?

JPA를 사용하다 보면 위의 예외들이 아닌 PessimisticLockingFailureException를 보게 되는데요. 사실은 같은 예외입니다.

• native query를 사용했을 때와 같이 MySQLTransactionRollbackException 발생합니다
• 하지만 최종적으로는 PessimisticLockingFailureException이 발생하고 있습니다

• 트랜잭션이 시작한 뒤 rollback 관련 exception을 catch 하고 있습니다.
• 그리고 Hibernate에서 발생시키는 PessimisticLockException을 catch 합니다
• 최종적으로 호출한 스프링에서 PessimisticLockingFailureException을 발생시키고 있습니다

 

즉 natvie query, hibernate를 사용하더라도 근본적인 오류의 원인은 DB에서 lock 획득에 대한 충돌이 문제인 것을 알 수 있습니다.

---

4) 사실은 이미 이름이 모든 것을 말하고 있었습니다

PessimisticLockingFailureException은 매우 긴 이름을 가져서, 왠지 모르게 읽기에 거부감이 듭니다.

 

하지만 자세히 단어마다 뜯어보면 이미 모든 것을 설명하고 있습니다.

• Pessimistic (비관적)
• Locking
• Failure
• Exception

 

즉 '비관적 락 획득에 실패한 오류'입니다. 

 

만약 이 예외의 이름을 조금 더 자세히 읽었다면 원인을 모른 채 삽질하는 시간을 줄일 수 있지 않았을까 생각합니다.

---

5) 그래서 어떻게 개선하면 좋을까요?

각각의 트랜잭션을 아주 짧게 가지고 가는 것은 어떨까요?

 

기존에는 아래와 같이 하나의 트랜잭션이 아주 길게 작업을 하고 있습니다. CUD 작업 시 비관적 lock이 발생하게 되면, R(읽기) 작업조차 할 수 없는데요. 많은 요청이 발생할 때 단순 조회에 고객은 오랫동안 대기해야 합니다.

 

만약 아래와 같이 큰 작업을 여러 개의 작은 작업으로 나눌 수 있다면, 다른 트랜잭션이 빠르게 접근하여 작업할 수 있을 것입니다.

 

즉 충돌을 피하기 위해서 작은 단위의 작업을 실행하는 것이 필요할 것 같은데요. 생각하는 방법은 아래와 같습니다.

• 작은 트랜잭션을 여러 번 짧게 수행
• 작업에 필요한 row를 명확하고 겹치지 않는 작은 where절 활용
• 분산 락 활용

5. 1) 분산 락을 활용하는 이유

분산 락을 활용한 이유는 아래와 같습니다.

 

만약 아래와 같이 1개의 row를 삭제하는 아주 짧고, 작은 조건절이 있다고 가정합니다.

 

하지만 그럼에도 불구하고 TR1, TR2가 동일한 조건을 바라보고 동시에 수행하면 충돌이 날 수밖에 없습니다.

 

결국 동일한 조건절에 CUD 작업을 하게 되면 충돌되는 것은 동일합니다. 이를 개선하기 위해서 삭제 전1) 조회,2) 있다면 삭제하는 것으로 변경합니다.

 

아래에서 트랜잭션 시작인 조회를 위한 lock 획득 대기 시간을 임의로 설정합니다. 다른 트랜잭션이 접근하지 못하는 환경을 구성 후 없다면, 삭제하는 것으로 개선할 수 있을 것 같습니다.

 

개인적으로는 DB를 활용한 분산 락 구현은 좋지 않다고 생각합니다. Lock 획득까지 계속해서 DB에 확인하는 요청이 발생하여 부하를 주게 됩니다. 그래서 Redis를 활용하여 pub/sub 형태의 분산 락이 더 좋은 방법이라고 생각합니다.

 

하지만 인프라 상황상 Redis를 사용할 수 없다면 DB를 활용하는 방법도 고려할 수 있을 것 같습니다.

---

6) 참고 문헌

MySQL 공식 문서