[영상후기] (2부) DB MVCC 이어서 설명합니다 ! MySQL & postgreSQL 예제와 함께 확인해 보세요 ! (feat. select ... for update)

MySQL Lost Update 해결(locking read)

x = 50 / y = 10
트랜잭션 1 : x가 y에 40을 이체
트랜잭션 2 : x에 30 입금
정상 동작 할 경우 : x = 40 / y = 50

• 순서
  • 1. 트랜잭션 2에서 read(x)로 읽을 때 lock을 취득
  • 2. 트랜잭션 1에서 read(x)로 읽을때 lock을 취득하며 읽으려 하나 트랜잭션 2에서 lock을 가지고 있어 대기
  • 3. 트랜잭션 2에서 x를 80으로 업데이트, write(x=80)
  • 4. 트랜잭션 2 완료 후 commit
  • 5. 트랜잭션 1에서 read(x) 작업 재개(lock 획득)
    • read(x) => 80
    • Locking Read 방식으로 인해 가장 최근의 commit된 data를 읽어옴
  • 6. 트랜잭션 1에서 x=40으로 업데이트(인출)
  • 7. 트랜잭션 1에서 y를 lock을 얻으며 읽음, read(y) => 10
  • 8. 트랜잭션 1에서 y를 50으로 설정, write(y=50)
  • 9. 트랜잭션 1을 commit
       정상 결과, Lost Update 해결 x=40 / y= 50

• MySQL LOST UPDATE 해결 : repatable read 만으로 해결이 안되고 locking read 추가 필요

• Locking read : read를 하면서 Lock 취득 방식

  • Isolation Level과 관계 없이 가장 최근의 commit된 데이터를 읽는 방식
  • SELECT ~~ FOR UPDATE; // MySQL 추가 필요(Locking read), write lock 획득
  • SELECT ~~ FOR SHARE; // read lock 획득

Write Skew 발생 및 해결

• Write Skew : 두 트랜잭션 실행 결과 일관성 없는 data를 가지는 문제
  • 위 예시에서 x=30, y=20 또는 x=20, y=30 되어야 하지만 둘다 20이 나오는 현상
  • MySQL, PostgreSQL 모두 발생 가능

MySQL 해결(Locking Read 사용)

트랜잭션 1: x와 y를 더해서 x에 쓴다.
트랜잭션 2 : x와 y를 더해서 y에 쓴다.

• 순서
  • 1. 트랜잭션 1에서 read(x)를 통해 lock을 얻고 값을 얻음
  • 2. 트랜잭션 2에서 x의 값을 얻으려 하지만 트랜잭션 1이 lock을 가지고 있어 대기
  • 3. 트랜잭션 1에서 read(y)를 통해 lock을 얻고 값을 얻음
  • 4. 트랜잭션 1에서 write(x)를 통해 값을 갱신
  • 5. 트랜잭션 1에서 결과 commit 후 lock 반환
  • 6. 트랜잭션 2에서 read(x)를 통해 갱신된 "20" 값 얻음
  • 7. 트랜잭션 2에서 read(y)를 통해 값 10을 얻음
  • 8. 트랜잭션 2에서 write(y=30)을 통해 갱신
  • 9. 트랜잭션 2 commit 후 lock 반환
       Write Skew 문제 해결

postgreSQL에서 write skew 해결 1 - for update문 활용

트랜잭션 1: x와 y를 더해서 x에 쓴다.
트랜잭션 2 : x와 y를 더해서 y에 쓴다.

• 순서
  • 1. 트랜잭션 1에서 read(x)를 통해 lock을 얻고 값을 얻음
  • 2. 트랜잭션 2에서 x의 값을 얻으려 하지만 트랜잭션 1이 lock을 가지고 있어 대기
  • 3. 트랜잭션 1에서 read(y)를 통해 lock을 얻고 값을 얻음
  • 4. 트랜잭션 1에서 write(x)를 통해 값을 갱신
  • 5. 트랜잭션 1에서 결과 commit 후 lock 반환
  • 6. 트랜잭션 2에서 read(x)를 실행하나 실패 후 Rollback
    • first-winner-update 적용
      - 같은 data에 먼저 update한 Transaction이 commit되면 나중 Transaction은 Rollback
      Rollback 이후 다시 트랜잭션을 실행여 Write Skew 문제 해결

postgreSQL에서 write skew 해결 2 - for share문 활용

트랜잭션 1: x와 y를 더해서 x에 쓴다.
트랜잭션 2 : x와 y를 더해서 y에 쓴다.

• 순서
  • 1. 트랜잭션 1에서 write(x)를 통해 lock을 얻고 값을 씀
  • 2. 트랜잭션 2에서 x의 값을 얻으려 하지만 트랜잭션 1이 lock을 가지고 있어 대기
  • 중간 생략..
  • 6. 트랜잭션 2에서 read(x)를 실행하나 실패 후 Rollback
    • first-winner-update 적용
      - 같은 data에 먼저 update한 Transaction이 commit되면 나중 Transaction은 Rollback
      Rollback 이후 다시 트랜잭션을 실행여 Write Skew 문제 해결

정리

• postgreSQL에서 FOR UPDATE, FOR SHARE : repeatable read 환경에서 동일하게 적용

  • 같은 데이터에 먼저 update한 트랜잭션이 commit되면 나중 트랜잭션은 rollback
  • repeatable read level에서 Write Skew 해결 가능

• MySQL은 rollback 없이 해결 가능, Postgre SQL은 rollback 이후 트랜잭션 다시 실행 후 해결

• Read Skew 문제는 isolation 레벨 중 serializable 레벨로 해결할 수도 있음

  • MySQL : Repeatable Read와 유사 동작
    • Transaction 내부 모든 평범한 Select 문은 암묵적으로 Select .. for Share 처럼 동작
    • MVCC 보다는 일반적으로 Lock으로 동작
    • for update를 기본으로 하지 않는 이유는 성능 측면의 이점(read 도 하기 위함)
      • read lock시 dead lock 가능성이 있다.
        • write lock 가지고 있으면서 다른 트랜잭션이 소유한 read lock 요청(해당 자원 역시 다른 트랜잭션에서 write lock으로 가지고 있음)
  • PostgreSQL : SSI(Serializable snapshot Isolation) 구현
    • MVCC 동작 + 모든 이상현상 막아주는 기법
    • First-Committer-Winner 기법 활용