mysql과 postgresql의 repeatable read 동작 차이 (모르면 삽질함)

응애 나 아기 코끼리

 

 

 

이론으로는 알고 있었는데 오늘따라 직접 테스트하고 싶어졌음.

시험 기간이 되면 신문 기사도 재미있어지는 그런 효과인가봄.

 

 

 

일단 mysql과 postgresql의 committed read 동작은 다를 게 없다. 

근데 repeatable read 동작은 전혀 다르다.

phantom read고 next key lock이고 undo log가 어쩌고.. 그런 건 전혀 중요하게 느껴지지도 않는 차이점이다.

 

"repeatable read가 성능도 별 차이 없으면서 안전한데 postgresql은 왜 default가 committed read임? repeatable read로 바꿔야지 ㅋㅋ"

 

이러면 이제 님들은 충격과 공포에 빠질 것이다.

 

시작 전에, 원리까지 구구절절 올리기엔 시간이 없으므로 요약.

더 궁금하면 "postgresql mvcc"로 검색해보자.

- postgresql은 업데이트/삭제 시 기존 튜플을 dead tuple 처리함.

- 각 튜플에는 행을 생성한 txid, 행을 삭제하거나 업데이트한 txid를 별도로 저장하고 있음.

- repeatable read 격리단계에서는, 오직 트랜잭션 시작 시점의 txid까지의 변경 사항만 읽을 수 있다.

- 업데이트/Lock 시도/삭제 시 튜플의 수정 txid 변경된 것 감지하면 바로 충돌 처리.

 

난 동작 차이만 올린다.

 

 

공식 문서 링크 -> https://www.postgresql.org/docs/current/transaction-iso.html

13.2. Transaction Isolation

 

 

핵심만 말하자면 postgresql의 repeatable read는 lost update를 원천 봉쇄한다.

(phantom read도 원천 봉쇄한다. mysql과는 달리 아예 방법이 없다.)

(단, 갭락이 없으므로 where user_id > 5 이런 식으로 걸어도 insert는 가능함. 같은 튜플만 안 건드리면 됨.)

 

lost update가 무엇이냐? 우리가 생각 없이 개발하면 겪는 동시성 문제다.

mysql에서는 두 세션에서 동시에 같은 레코드를 작업할 때 아래와 같은 일이 일어난다.

 

 

#session A
begin;

#session B
begin

#session A
update users set age = 100 where user_id = 5;
commit;

#session B
update users set age = 200 where user_id = 5;
commit;

#session A
select u.age from users u where u.user_id = 5;
-> 결과 : 200

 

 

그냥 이렇게 쿼리문으로만 보면 당연한 결과라고 할 수 있지만, 어플리케이션 로직에서 생각했을 때는 이게 보통 우리가 원한 결과가 아닐 가능성이 높다.

일반적으로 이런 쿼리는 아래 같은 로직에서 발생하기 때문이다.

 

#session A (재고를 100개 추가함)
트랜잭션 진입
val product = productRepository.findById(5)

#session B (재고를 200개 추가함)
트랜잭션 진입
val product = productRepository.findById(5)

#session A
product.increaseStock(100)
커밋

#session B
product.increaseStock(200)
커밋

// 그냥 update products set stock = stock + 100하면 이런 일 안 생김

 

 

sessionA는 현재 재고에서 100을 추가했고, sessionB는 현재 재고에서 200을 추가하고 저장했다.

그러면 어플리케이션 로직상 우리가 원한 결과는 300이 나와야 하지만, 실제로는 200이 나와버린다.

이렇게 session이 동시에 하나의 레코드에 접근할 때 마지막 커밋만 반영되는 걸 lostUpdate라고 한다.

 

근데 postgresql은 이런 lostUpdate 현상을 아예 방지한다는 것이다.

"와 ㅋㅋ 대박 그럼 동시성 문제 안 일어나는 거 아님? ㅋㅋ"

 

어.. 안 일어나긴 하는데.. 기대하던 동작이 아닐 수 있다.

postgresql로 똑같은 시도를 해보자.

 

 

#session A
begin;

#session B
begin

#session A
update users set age = 100 where user_id = 5;
commit;

#session B
update users set age = 200 where user_id = 5;
-> 결과 : Error 어쩌고 저쩌고 (대충 한 레코드를 동시에 수정할 수 없다는 뜻)

 

 

그렇다. 우리의 코끼리는 정말 무식하게 txId 기준으로 모든 걸 판단하기 때문에 현재 txId보다 높은 txId(꼭 숫자가 더 크지는 않을 수 있음.)에서 이미 건드린 레코드를 건드리려는 시도를 원천 봉쇄한다.

 

s lock, x lock 또한 다른 곳에서 특정 레코드를 이미 update하고 commit()해버리면 예외가 발생한다.

 

 

 

# session A
begin

# session B
begin

# session A
update users set age = 100 where user_id = 5;
commit()

# session B
select * from users u where user_id = 5 for share;
-> 결과 : Error 어쩌고 저쩌고

 

 

아니 이런 식이면 대체 application server에서 어떻게 써요?

서로 다른 세션에서 같은 레코드에 접근하는 일이 얼마나 비일비재한데????

-> 아마도 그러니까 default가 committed read인 거겠죠

 

일단, 사실 나도 직접 구현해본 적은 없다. 우리 서비스 로직은 committed read 격리 수준에서 문제가 생길만한 것들이 없어서..

근데 낙관적 락 로직 구현하듯 재시도 로직으로 구현하면 되지 않을까?

 

라고 생각하고 찾아보지는 않았었는데, 글 쓰다가 찾아보니 마침 이에 대해 다루고 있는 글이 있다.

궁금하면 읽어보셈.

 

롤백 후 재시도 로직을 구현하고 있다.

 

https://medium.com/@l0coful/database-locks-with-spring-hibernate-and-postgres-33e2293468b4

Database locks with Spring, Hibernate and Postgres