[PostgreSQL] SERIALIZABLE 동시 갱신이 서로를 덮어써 lost update가 날 때

설치·접속: PostgreSQL 설치와 접속

부제: 두 요청이 잔액을 각각 읽어 +50씩 더하는데, 하나가 다른 하나의 갱신을 덮어써 결과가 틀어지는 상황

-- 흔한 read-modify-write 패턴 (앱이 읽은 값으로 다시 쓴다)
BEGIN ISOLATION LEVEL READ COMMITTED;
SELECT balance FROM m7_acct WHERE id=1;        -- 둘 다 100을 읽음
-- 앱에서 100+50 계산
UPDATE m7_acct SET balance = 150 WHERE id=1;   -- 읽은 값 기준으로 덮어씀
COMMIT;

########## LOST UPDATE (READ COMMITTED) — 둘 다 +50 하려는데 ##########
B: 읽은 잔액=100  -> +50 계산
A: 읽은 잔액=100  -> +50 계산
A: 커밋 (150 기록)
B: 커밋 (150 기록)
 최종 잔액 (기대 200)
----------------------
                  150   <- 한쪽 +50이 통째로 사라짐

########## SERIALIZABLE — 같은 시나리오 ##########
B: 읽은 잔액=100
A: 읽은 잔액=100
A: 커밋 성공
B: 커밋
ERROR:  could not serialize access due to concurrent update   <- B는 실패 → 앱이 재시도

두 트랜잭션이 각자 100을 읽고 각자 150을 써서 커밋하면, 나중에 커밋한 쪽이 앞선 갱신을 통째로 덮어쓴다. +50이 두 번이었는데 결과는 150. 이게 lost update다. UPDATE ... SET balance = balance + 50처럼 DB 안에서 계산하면 행 락으로 직렬화되어 안전하지만, 앱이 읽은 값을 다시 써넣는 패턴에서는 READ COMMITTED가 이걸 못 막는다. SERIALIZABLE로 올리면 PostgreSQL이 두 트랜잭션이 사실상 충돌한다는 걸 감지해 나중 커밋을 40001(serialization failure)로 거절한다. 그래서 SERIALIZABLE을 쓸 땐 앱에 재시도 루프가 필수다. 실패한 트랜잭션을 다시 돌리면 이번엔 150을 읽어 200을 쓴다.

이렇게도 쓴다

DB 안에서 계산하면 SERIALIZABLE 없이도 lost update가 안 난다. (조합: 원자적 UPDATE)

UPDATE m7_acct SET balance = balance + 50 WHERE id=1;  -- 행 락으로 직렬화

 

읽는 시점에 행을 잠가 다른 세션을 대기시킨다. (조합: SELECT FOR UPDATE)

SELECT balance FROM m7_acct WHERE id=1 FOR UPDATE;     -- 비관적 잠금

 

버전 컬럼으로 낙관적 잠금을 건다(안 바뀌었을 때만 성공).

UPDATE m7_acct SET balance=150, version=version+1
WHERE id=1 AND version = 7;   -- 0 rows면 남이 먼저 바꾼 것 → 재시도

 

직렬화 실패인지 코드로 구분해 재시도한다. (조합: SQLSTATE 40001)

-- 애플리케이션: SQLSTATE '40001'(serialization_failure) 잡으면 트랜잭션 재실행

 

읽기만 하는 리포트는 예측 못한 직렬화 실패를 줄인다. (조합: DEFERRABLE)

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

 

직렬화 충돌이 실제로 얼마나 나는지 카운터로 본다. (조합: pg_stat_database)

SELECT datname, xact_commit, xact_rollback FROM pg_stat_database WHERE datname='shop';