MySQL 격리수준 & SELECT .. FOR UPDATE & 낙관적 락과 비관적 락

 

1. MySQL의 격리수준과 SELECT 구문의 작동 방식

 

REPEATABLE READ

 

MySQL의 기본 격리 수준은 REPEATBLE-READ입니다. 처음 MySQL을 접하면 다른 REPEATBLE-READ이라는 다른 격리 수준에 흥미를 가지며 알아보게 되는데요. 오늘도 해당 격리 수준에 대해 알아보면서 SELECT ... FOR UPDATE가 동작하는 방식도 함께 알아보겠습니다.

 

 

• 잠금 없는 일관된 읽기: MySQL에서는 잠금 없는 일관된 읽기를 지원합니다. 이는 무슨 뜻일까요?
  • A 세션에서 a 레코드를 읽는데 B 세션이 a 레코드를 변경하면? 일부는 변경된 데이터, 일부는 변경 전 데이터를 가져올 수 있지 않을까요? 그럴 때 shared lock을 걸면 물론 변경되는 걸 방지할 수 있습니다. 하지만 특정 레코드에 몰리는 동시 트랜잭션 성능이 떨어지게 됩니다. 변경에 대해서는 exclusive lock을 걸게 되니 심각한 성능 저하가 일어나게 됩니다. 그래서 mySQL은 동시 처리 성능을 위해 잠금 없이 읽기를 지원합니다.
  • 변경되는 도중에 다른 세션에서 변경 중인 데이터를 읽으려고 하면 Undo의 변경 이력을 참조합니다.
  • 이를 통해 lock 없는 일관된 읽기를 제공하게 됩니다.
• 다시 말해 REPEATABLE READ의 경우 트랜잭션 시작 시점의 스냅샷에 대해 일관된 읽기를 제공한다는 것입니다. 즉 반복적인 읽기를 실행해도 일관된 읽기를 제공합니다.

 

SELECT ... FOR UPDATE

반면 SELECT ... FOR UPDATE는 위와 다소 동작이 다릅니다.

• 격리 수준 무관하게 항상 최신 커밋 데이터를 조회하게 됩니다.
• 즉 단순 SELECT와 다른 결과를 반환할 수 있습니다.

예시와 함께 보도록 하겠습니다.

BEGIN;
SELECT * FROM wallet WHERE user_id='A';

/* 결과에 따라서, 처리 로직이 가변적이어서... FOR UPDATE 필요 */
if(balance >= 100){
    UPDATE wallet SET balance = #{current_balance} - 100
        WHERE user_id=?;
    /* (필요시) 부가적인 처리 수행 */
    COMMIT;
}else{
    ROLLBACK;
}

 

위 트랜잭션은 무슨 문제가 있을까요?

두 개의 트랜잭션에서 동시에 조회했는데, 150원이 조회되었다고 해볼까요. 앞서 말한 것처럼 mySQL은 잠금 없는 일관된 읽기를 지원하므로 가능합니다. 따라서 동시에 100원씩 차감하는 로직을 실행하게 되면 결과적으로 잔액은 -50이 됩니다. 그래서 이런 것을 막기 위해 FOR UPDATE가 필요해지게 됩니다.

• FOR UPDATE 키워드 사용 시: X lock을 걸고 접근하여 다른 트랜잭션에서 동일 트랜잭션에 대해 x lock을 걸지 못하게 하고 선행 동작을 수행할 수 있게 됩니다. 그리고 COMMIT 수행 후 비로소 다른 트랜잭션이 동작 가능하게 됩니다. AUTO COMMIT 모드에서는 그래서 잠금 능력이 거의 없습니다. LOCK 잡고 바로 풀어버리기 때문입니다.
• SELECT … FOR UPDATE는 SELECT 실행 시점에서 최종 커밋된 데이터를 가져오게 됩니다. NON-LOCKING SELECT에서는 트랜잭션 시작 시점 데이터를 가져오는 것과는 다릅니다.

• UPDATE 문장에서 이미 X LOCK을 획득하기 때문에 여러 트랜잭션이 동시에 실행되더라도 정합성 문제가 발생하지 않습니다.

그럼 다 SELECT ... FOR UPDATE로 해버리면 문제가 안 생기지 않을까요..?

• 그러면 당연히 동시성이 떨어지게 됩니다.
• SELECT … FOR UPDATE가 문제가 많이 되는 것은, 일반적인 구현에서 FOR UPDATE 구문으로 읽어온 뒤 DB 이외의 원격 서버로 요청을 보내는 경우가 많기 때문입니다. 잔액 조회에 X LOCK을 걸고 원격 서버나 레디스에 요청할 경우 해당 잠금이 길어지고 다른 트랜잭션의 처리가 길어져 문제가 생깁니다. 특히 Aurora와 같이 Long Transaction의 범위가 잘 전파되는 경우 문제가 더 커질 것입니다.
  • 업데이트 대상이 희박하다면 WHERE 절에 추가 조건을 명시하는 것이 좋습니다. 불필요한 lock을 걸지 않는 것이 좋기 때문입니다. 즉 락을 걸고자 한다면 where 절에 최대한 필터링을 걸어야 합니다.
• 그렇다고 SELECT … FOR UPDATE가 성능에 안좋아서 빼라는 것이 아니라 SQL 구문 하나라도 줄이는 것이 성능 튜닝에 도움이 됩니다.

여기서 MySQL의 REPEATABLE READ의 잠금 조건에 대해 잠시 알아보고 갈까요?

 

 

2. SELECT ... FOR SHARE

 

BEGIN;
SELECT * FROM article WHERE article_id='A' FOR SHARE;
/* 부모 테이블인 article 확인 후, 자식 테이블인 comment 테이블의 레코드 INSERT */
if(article.canAddComment()){
    INSERT INTO comment (...) VALUES ('A', ...);
    COMMIT;
}else{
    ROLLBACK;
}

 

• 부모인 article이 있는지 확인 후 comment를 insert를 하는 구조입니다.
• 부모인 article을 지워버리는 것을 방지하기 위해 FOR SHARE로 lock을 잡고 들어가는 것입니다.

SELECT … FOR SHARE 이후 UPDATE & DELETE가 필요하다면 FOR SHARE를 사용 자제 하는 것이 좋습니다.

• Lock upgrade 필요 (S-lock → X-lock)
• DeadLock 가능성이 매우 높습니다.
• lock upgrade의 시간 간격이 넓으면 넓을 수록 확률이 매우 높아집니다.

 

3. 낙관적 락과 비관적 락

낙관적 락

BEGIN;
SELECT * FROM user WHERE id=1;
...
UPDATE user SET address=? WHERE id=1;
COMMIT;

 

비관적 락

BEGIN;
SELECT * FROM user WHERE id=1 FOR UPDATE;
...
UPDATE user SET address=? WHERE id=1;
COMMIT;

 

주요 차이점은 SELECT 구문에서 FOR UPDATE 키워드의 사용 여부입니다. 비관적 락은 읽는 시점부터 잠금을 걸어 동시성 충돌을 미리 방지합니다.

 

MySQL 서버의 SELECT … FOR UPDATE와 jpa의 낙관적, 비관적 락을 혼동하는 경우가 많습니다.

MySQL에서 낙관적 락은 있을 수 없습니다. 레코드 변경 시 update에 lock을 잡지 않을 수 없기 때문입니다.

따라서 모두 Pessimistic lock으로 동작하기 때문에 구분하는 것이 의미 없습니다.

낙관적 락과 비관적 락은 트랜잭션 내에서 어떤 SQL 문장을 사용하느냐에 따라 낙관적, 비관적일 수 있습니다.

• 낙관적 락: SELECT 시점에 lock 없이 수행하고 update에 잠금을 수행하는 모습으로 즉 다른 트랜잭션에서 동일한 레코드에 대해 동시 변경 가능성이 낮다고 가정합니다
• 비관적 락: SELECT 시점에 FOR UPDATE로 잠금을 미리 걸고 트랜잭션을 수행합니다

즉 낙관적 락과 비관적 락은 트랜잭션의 특성으로 보는 것이 맞습니다.

 

낙관적 락

• update 시 버전을 증분시켜 처음 조회했던 version 정보가 바뀌었을 경우 롤백합니다. 즉 MySQL 서버가 제공하는 잠금 기법이 아니라 JPA가 제공하는 방식입니다. → 어떻게 가능할까요? 말한 것처럼 MySQL의 repeatable read의 경우 트랜잭션 시작 시점 동일한 읽기를 제공하는데, X lock은 아니기 때문에 update 시점에서 알 수 있습니다. 근데 SELECT 이후 update 실행 확률이 높을 경우 성능이 낮을 수 있습니다.

 

비관적 락

• 락 잡고 있는 트랜잭션에 대해 기다리는 방식입니다. 많은 트랜잭션이 동일한 레코드를 많이 update를 할 경우 잠금 대기 시간이 길어지고 트랜잭션 대기 시간이 길어지지만 에러가 발생할 확률이 높지 않습니다.
• 특정 레코드에 잠금이 집중되지 않는다면 비관적 락이 훨씬 깔끔한 처리를 보여줄 수 있습니다.