무럭무럭 STUDY - SELECT ... FOR UPDATE (MySQL)

SELECT (REPEATABLE-READ;반복 읽기)

MySQL 서버에서 select 구문은 기본적으로 '잠금 없는 일관된 읽기'를 지원한다. 이는 격리 수준에 따라 레코드 버전이 달라지는데, commit-read 격리 수준에서는 가장 최근에 커밋된 데이터를 반환하지만 repeatable-read 수준에서는 트랜잭션이 시작된 시점의 데이터를 반환한다. 즉 repeatable-read 격리 수준에서는 select 문장이 하나의 트랜잭션에 내에서 여러 번 실행되어도 항상 동일한 결과를 반환한다.

Tip)
'잠금 없는 일관된 읽기;Non-Locking Consistent Read(MVCC)'는 테이블의 레코드를 읽을 때 대상 레코드에 대해 잠금을 거지 않고도 일관된 데이터 읽기를 지원하는 것을 의미. 예를 들어 A세션이 USER1 이란 사용자 정보를 읽을 때, B 세션이 USER1 사용자 정보를 변경한다고 가정해보자. 이때 변경 전 데이터를 undo 영역에 백업을 해두고 실제 데이터를 변경한다. 그리고 A는 변경 중인 데이터가 아닌 undo 영역에 백업된 데이터를 읽는다. 이렇게 MySQL은 잠금 없는 일관된 읽기를 제공함으로써 잠금으로 인한 동시 처리 성능 이슈를 해결했다.

 

하지만 for update와 for share 구문을 사용해야 할 때 주의할 점이 있다. 이 두 구문을 사용하면 현재 레코드에 'exclusive lock;배타적 잠금' 혹은 'shard lock;공유 잠금'을 걸기 때문에 격리 수준에 상관 없이 트랜잭션 시작 시점 데이터가 아닌 마지막에 커밋된 최신 데이터를 반환한다. 

 

select ... for update 튜닝 (X-LOCK)

select for update는 성능이 문제라기보단 배타락이 걸리고 다른 DB 서버 혹은 Redis 같은 시스템에 값을 전달하게 되면 크게 문제가 된다. 예를 들어 잠금이 걸린 상태로 다른 DB 서버 혹은 Redis 같은 시스템에서 응답이 느려지는 경우 MySQL 서버에서도 트랜잭션이 계속 활동 상태가 되고 다른 트랜잭션 처리를 막기 때문에 문제가 발생한다. 때문에 select for update 는 필요치 않으면 제거하는 것이 좋다.

 

예를 들어 하기 코드처럼 조건을 모두 update 구문으로 옮겨서 실행할 수도 있다. 이는 exam_col 값이 100 이상인 경우에만 차감하도록 업데이트 문장을 변경했다. 그리고 업데이트 여부를 식별하기 위해 affected_rows 카운터 값을 확인한다. 이로 인해 여러 트랜잭션이 동시에 동일한 처리를 진행해도 한 트랜잭션에서 업데이트 문장을 실행하면서 레코드에 대해 배타락을 먼저 획득하면 다른 트랜잭션은 대기하기 때문에 여러 쓰레드가 동일한 처리를 진행해도 문제가 없다.

begin;
update exam_tab
   set exam_col = #{current_exam_col} - 100
 where exam_id  = ?
   and exam_col >= 100;
   
if (affected_rows == 1) {
  commit;
} else {
  rollback;
}

 

혹은 하기 코드처럼 select for update 문장을 작성할 때 where 조건절에 최대한 필터링 조건을 넣어 실행해 필요한 경우에만 레코드 잠금을 걸 수 있게 할 수도 있다. 물론 이 경우에는 레코드 잠금이 걸리지 않도록 하기 위해 Lock Release 조건을 만족해야 한다.

begin;
select *
  from exam_tab
 where exam_id  = 'A' and exam_col >= 1000000000 for update;

if (resultSet.hasNext()) {
  update exam_tab
     set exam_col = #{current_exam_col} + 10000
   where exam_id  = ?;
}

commit;

Tip)
Lock Release 조건
1. transaction_isolation = read-committed
2. binlog_format = MIXED | ROW

 

select ... for share 튜닝 (S-LOCK)

select for share를 실행한 이후 다시 읽기 잠금을 건 레코드를 업데이트하게 되면 for share보단 for update를 사용하는 것이 좋다. for share 구문은 select 구문 실행하면서 레코드에 s-lock을 걸고 이후 업데이트 구문이 실행되면 MySQL 서버는 동일 레코드에 대해 x-lock을 다시 걸어야 한다. 즉 잠금 업그레이드 상황, s-lock을 걸고 있는 상태에서 x-lock 도 획득해야 하는 상황이 발생하면 dead lock을 유발하기 매운 쉬운 패턴이 되기 때문이다.

 

MySQL은 JPA의 Optimistic와 Pessimistic 중 무엇인가?

MySQL 서버에서는 레코드를 변경할 때 레코드에 잠금을 걸지 않고 변경할 수 없기 때문에, Optimistic Lock이란 개념은 있을 수가 없다. 굳이 비교하자면 insert, update, delete 구문은 pessimistic으로 처리된다고 봐야 겠지만 의미가 없다.

-- optimistic lock (변경 시점에 잠금)
begin;
select * from exam_tab where exam_id = 1;
...
update exam_tab set exam_col = ? where exam_id = 1;
commit;

-- pessimistic lock (읽는 시점에 잠금 - 미리 충돌 예상)
begin;
select * from exam_tab where exam_id = 1 for update;
...
update exam_tab set exam_col = ? where exam_id = 1;
commit;

JPA에서 이야기 하는 두 잠금은 MySQL의 기능이라기보단 트랜잭션 내에서 어떤 SQL 문장을 사용하냐에 따라 트랜잭션이 pessimistic이 될 수도 있고 optimistic이 될 수 있는 것이다.

Tip)
'optimistic lock;낙관적 락' 다른 트랜잭션과 동일 레코드에 대해 동시 변경 가능성이 낮을 거라는 가정에서 사용하는 방식이고 반대로 'pessimistic lock;비관적 락'은 다른 트랜잭션도 동일 레코드에 대해 변경 가능성이 높다고 판단되면 미리 잠금을 걸고 트랜잭션을 시작하는 방식이다. 예를 들어,  낙관적 락은 version이란 컬럼을 두어서 잠금 대기 없이 빠르게 처리되긴 하지만 많은 트랜잭션이 동일 레코드를 업데이트하게 되면 locking failure exception error가 매우 빈번하게 발생한다. 반면 비관적 락은 동일 레코드에 대해 업데이트가 집중되는 환경에서는 잠금 대기 시간이 길어지고 트랜잭션 처리 시간이 길어지게 되기만 하고 에러가 발생할 확률은 높지 않다. 그리고 수많은 레코드로 트랜잭션이 분산되는 서비스에서는 비관적 락 방식은 거의 잠금 경험 없이 빠르게 처리될 수도 있다. 결론적으로 잠금이 집중되는 환경이든 아니든 트랜잭션 내의 쿼리들이 잘 튜닝만 된다면 비관적 락 방식이 깔끔한 처리를 보여줄 수도 있다.

 

---

Reference

Real MySQL - 이성욱님, 백은빈님