[MySQL] Lost Update

기훈·2025년 3월 21일

mysql

MySQL

목록 보기

2/23

1. 정의

1-1 Lost Update란

여러 트랜잭션이 동시에 데이터를 변경하는 과정에서, 한 트랜잭션의 수정 내용이 다른 트랜잭션의 수정 내용에 의해 덮어씌워져 최종적으로 의도와 다른 값이 저장되는 현상을 말한다.

이번 글에서는 MySQL, PostgreSQL, MSSQL에서 Lost Update가 발생하는 상황과, 이를 방지하는 방법에 대해 살펴본다. (낙관적 락은 제외)

3. DBMS별 Lost Update 동작

3.1 MySQL (InnoDB)

REPEATABLE READ

MySQL InnoDB의 REPEATABLE READ는 MVCC 기반이므로, 기본적으로 읽기 시점에 해당 레코드의 스냅샷 버전을 고정해서 봅니다.
이로 인해 서로 다른 트랜잭션이 같은 레코드를 읽고, 각자 수정한 후 커밋하면, 마지막에 커밋한 트랜잭션이 이전 변경을 덮어써버립니다.

동일 행 수정 시 Lost Update 가능
- MVCC는 읽기 시점의 버전을 사용하기 때문에, 두 트랜잭션 모두 같은 값을 읽을 수 있음
- 이후 각자 UPDATE하면 마지막 커밋이 승리 (이전 변경이 사라짐)

PostgreSQL과의 차이

같은 MVCC 기반이라도, MySQL과 PostgreSQL은 REPEATABLE READ의 쓰기 충돌 처리 방식이 다릅니다.

PostgreSQL REPEATABLE READ
- 스냅샷 읽기 + 쓰기 시 버전 충돌 감지
- UPDATE/DELETE 시 내가 읽었던 버전이 최신인지 확인
- 이미 다른 트랜잭션이 수정한 경우 could not serialize access 오류로 롤백
MySQL REPEATABLE READ
- 스냅샷 읽기만 하고, 쓰기 시 충돌을 감지하지 않음
- 조건에 맞는 현재 레코드를 그대로 덮어씀 → 마지막 커밋이 승리(Last Commit Wins)

이 차이 때문에, PostgreSQL은 모든 트랜잭션이 REPEATABLE READ 이상이면 Lost Update를 막지만,
MySQL은 SELECT ... FOR UPDATE 또는 SERIALIZABLE로 격리수준을 높이지 않으면 Lost Update가 발생할 수 있습니다.

타임라인 비교

PostgreSQL REPEATABLE READ (충돌 감지)

T1: BEGIN TRANSACTION (RR)
T2: BEGIN TRANSACTION (RR)

T1: SELECT balance → 100 (스냅샷)
T2: SELECT balance → 100 (스냅샷)

T2: UPDATE balance = 90
T2: COMMIT

T1: UPDATE balance = 80
    → "네가 본 balance=100은 이미 변경됨" 감지
    → ERROR: could not serialize access
T1: ROLLBACK

MySQL REPEATABLE READ (충돌 미감지)

T1: BEGIN TRANSACTION (RR)
T2: BEGIN TRANSACTION (RR)

T1: SELECT balance → 100 (스냅샷)
T2: SELECT balance → 100 (스냅샷)

T2: UPDATE balance = 90
T2: COMMIT

T1: UPDATE balance = 80
    → 현재 레코드(balance=90) 덮어씀
T1: COMMIT
최종 balance = 80 (T2 변경 손실)

방지 방법

Locking Read 사용
```
SELECT ... FOR UPDATE
```
- 읽기 시점에 배타적 잠금을 걸어, 다른 트랜잭션이 해당 레코드를 읽지 못하게 함
- 두 번째 트랜잭션은 첫 번째 트랜잭션이 커밋하기 전까지 대기
SERIALIZABLE 격리수준
- 모든 읽기에 공유락을 걸고, 쓰기 시도 시 충돌이 감지되면 커밋 시점에 롤백
- 공유락과 배타락은 동시에 가질 수 없기 때문에 Lost Update 차단 가능

3.2 PostgreSQL

REPEATABLE READ

PostgreSQL의 REPEATABLE READ는 MVCC 기반이지만, 쓰기 시점에 버전 충돌 감지 기능이 있습니다.

동일 행을 두 트랜잭션이 동시에 수정하려고 하면:
- 먼저 커밋한 트랜잭션의 변경이 반영됨
- 나중에 커밋하려는 트랜잭션은 스냅샷과 현재 버전이 다르다는 사실을 감지하고 Serialization Failure 또는 could not serialize access 오류를 발생시킴 → 롤백됨

중요: 모든 트랜잭션이 REPEATABLE READ 이상이어야 함.
한쪽이 READ COMMITTED라면 충돌 감지가 안 되고 Lost Update 발생 가능.

방지 방법

트랜잭션 모두 REPEATABLE READ 또는 SERIALIZABLE로 실행
SELECT ... FOR UPDATE로 명시적 잠금

SERIALIZABLE

PostgreSQL의 SERIALIZABLE은 SSI(Serializable Snapshot Isolation)를 사용
REPEATABLE READ와 동일하게 버전 충돌을 감지하되, 직렬성 위반 가능성이 있는 경우도 감지하여 롤백
Lost Update는 물론, Write Skew까지 방지 가능

3.3 MSSQL

MSSQL은 MySQL, PostgreSQL과 달리 MVCC가 아닌 Lock 기반 동시성 제어를 기본으로 사용합니다.

REPEATABLE READ

행 읽기 시 S Lock(공유 락)을 걸고, 트랜잭션 종료까지 유지
다른 트랜잭션이 동일 행을 UPDATE하려 하면 X Lock(배타 락)을 걸어야 하는데, S Lock과 X Lock은 동시에 불가능 → 대기 또는 Deadlock
따라서 모든 트랜잭션이 RR일 경우 Lost Update 불가능

중요한 차이점

MSSQL은 SELECT 시 읽은 값을 그대로 들고 있다가 무조건 UPDATE하는 구조가 아님
UPDATE 실행 시에도 해당 행에 대해 다시 잠금 요청
이미 다른 트랜잭션이 변경 후 커밋했다면, UPDATE는 최신 값을 기반으로 실행
- 예: balance=100 읽었더라도, UPDATE 시점에 이미 balance=90이면 이를 읽고 -10 적용하여 80 저장
- 이 과정에서 기존 변경이 덮이지 않음

방지 방법

REPEATABLE READ 자체로 Lost Update 방지 가능 (모든 트랜잭션이 RR일 때)
혼합 격리수준 사용 시(READ COMMITTED 혼재)에는 WITH (UPDLOCK, HOLDLOCK) 같은 비관적 잠금 사용 권장

주의: MSSQL에서 Lost Update가 다시 가능해지는 경우

MSSQL은 기본적으로 Lock 기반 동시성 제어를 사용하며, 모든 트랜잭션이 REPEATABLE READ 이상이면 Lost Update가 발생하지 않습니다.
하지만 다음과 같은 환경에서는 Lost Update 가능성이 존재합니다.

READ_COMMITTED_SNAPSHOT = ON 활성화 시 (RCSI 켜짐)

READ COMMITTED 격리 수준이 MVCC(버전 기반) 방식으로 동작합니다.

이 설정은 READ COMMITTED 트랜잭션에만 영향을 주며,
REPEATABLE READ 트랜잭션은 여전히 잠금(S Lock)을 유지합니다.

그러나, 시스템에 REPEATABLE READ와 READ COMMITTED(RCSI) 트랜잭션이 혼재하는 경우,
READ COMMITTED(RCSI) 트랜잭션은 S Lock을 오래 유지하지 않으므로,
REPEATABLE READ 쪽의 보호가 깨지고 Lost Update가 발생할 수 있습니다.

ALLOW_SNAPSHOT_ISOLATION = ON 활성화 및 SNAPSHOT 격리 사용 시

SNAPSHOT 격리 트랜잭션은 S Lock을 사용하지 않고 버전 데이터를 읽습니다.

따라서 REPEATABLE READ 트랜잭션과 혼합되면 동시 갱신이 가능하며,
충돌이 발생하면 SNAPSHOT 트랜잭션은 오류(에러 3960)로 롤백되지만,
READ COMMITTED(RCSI) 트랜잭션과 혼합될 경우에는 Lost Update가 발생할 수 있습니다.

혼합 격리 수준 환경

한쪽이 REPEATABLE READ, 다른 쪽이 READ COMMITTED(RCSI) 또는 SNAPSHOT 격리인 경우

REPEATABLE READ가 기대하는 S Lock 기반 보호가 깨지므로 Lost Update 위험이 있습니다.

이를 방지하기 위해, 충돌 가능 구간에서 WITH (UPDLOCK, HOLDLOCK) 등 명시적 잠금을 사용하는 것이 안전합니다.

요약

REPEATABLE READ 트랜잭션만 단독으로 실행 시 Lost Update 방지
READ COMMITTED(RCSI), SNAPSHOT 격리 등 MVCC 트랜잭션과 혼합 시 Lost Update 위험
혼합 환경에서는 명시적 비관적 잠금 사용 권장

SERIALIZABLE

REPEATABLE READ 방식 + 범위 잠금(Key-Range Lock) 추가
Lost Update는 물론, Phantom Read, Write Skew까지 방지

4. 비교 표

DB / 격리수준	Lost Update 가능 여부	방지 방식
MySQL RR	🔺 가능	MVCC 스냅샷 기반 → FOR UPDATE 필요
MySQL SERIALIZABLE	⭕ 불가능	모든 읽기 공유락 + Next-Key Lock
PostgreSQL RR	⭕ 불가능(모두 RR 이상일 때)	버전 충돌 감지 후 롤백
PostgreSQL SERIALIZABLE	⭕ 불가능	SSI 기반 직렬성 위반 감지
MSSQL RR	⭕ 불가능(모두 RR일 때)	S Lock 유지 + UPDATE 시 최신 버전 재확인
MSSQL SERIALIZABLE	⭕ 불가능	RR 방식 + Key-Range Lock

5. 핵심 요약

MySQL, PostgreSQL은 MVCC 기반 → REPEATABLE READ에서 Lost Update 가능(단, PostgreSQL은 모든 트랜잭션이 RR 이상이면 방지)
MSSQL은 Lock 기반 → RR에서 기본적으로 Lost Update 방지
SERIALIZABLE은 모든 DBMS에서 Lost Update 방지
혼합 격리수준 환경에서는 별도의 잠금(FOR UPDATE, UPDLOCK, HOLDLOCK) 필요

기훈

이전 포스트

[MySQL] Write Skew

다음 포스트

[MySQL] Lost Update

MySQL

1. 정의

1-1 Lost Update란

3. DBMS별 Lost Update 동작

3.1 MySQL (InnoDB)

REPEATABLE READ

PostgreSQL과의 차이

타임라인 비교

PostgreSQL REPEATABLE READ (충돌 감지)

MySQL REPEATABLE READ (충돌 미감지)

방지 방법

3.2 PostgreSQL

REPEATABLE READ

방지 방법

SERIALIZABLE

3.3 MSSQL

REPEATABLE READ

중요한 차이점

방지 방법

요약

SERIALIZABLE

4. 비교 표

5. 핵심 요약

[MySQL] Write Skew

[MySQL] MySQL vs PostgreSQL vs MsSQL

0개의 댓글