[MySQL] Write Skew

1. 정의

두 개 이상의 트랜잭션이 동시에 동일한 조건을 읽고, 서로 다른 데이터를 수정하면서 데이터 정합성 및 비즈니스 규칙이 깨지는 현상이다.

Lost Update와의 차이점은, Write Skew는 같은 row를 수정하지 않아도 발생한다는 것이다. 각 트랜잭션의 수정 자체는 유효하지만, 두 수정이 동시에 반영되면 규칙이 깨진다.

이 글에서는 MySQL, PostgreSQL, MSSQL에서 Write Skew가 발생하는 상황과 방지 방법을 살펴본다. (낙관적 락은 제외)

---

2. 기본 시나리오

Write Skew는 크게 두 가지 패턴으로 나뉜다.

a. 같은 row의 다른 컬럼을 수정하는 경우

병원 당직 시스템을 예로 든다. 한 row에 primary_doctor와 backup_doctor 컬럼이 있고, 둘 중 하나는 반드시 당직이어야 한다는 규칙이 있다.

-- 초기 상태
shift: { primary_doctor: 'on_call', backup_doctor: 'on_call' }

-- T1: primary_doctor를 off_call로 변경 (backup이 on_call이니 괜찮다고 판단)
-- T2: backup_doctor를 off_call로 변경 (primary가 on_call이니 괜찮다고 판단)

타임라인:

아래 타임라인은 MVCC 기반 DB(PostgreSQL RR 기준) 에서 발생하는 케이스다.
MySQL RR은 같은 row를 UPDATE할 때 Record Lock 충돌이 발생해 직렬화되므로 이 패턴에서 Write Skew가 방지된다. MSSQL RR도 S Lock 충돌로 직렬화된다.

-- [T1 시작] 격리수준: READ COMMITTED
-- (패턴 a Write Skew는 RC 이하 또는 명시적 잠금 없는 환경에서 발생)
BEGIN;

-- [T2 시작] 격리수준: READ COMMITTED
BEGIN;

-- T1: 현재 당직 상태 확인
-- "primary, backup 둘 다 on_call → 내가 primary를 off_call로 바꿔도 backup이 커버"
SELECT primary_doctor, backup_doctor FROM shifts WHERE id = 1;
-- 결과: primary_doctor='on_call', backup_doctor='on_call'

-- T2: 동일한 row 조회
-- "primary, backup 둘 다 on_call → 내가 backup을 off_call로 바꿔도 primary가 커버"
SELECT primary_doctor, backup_doctor FROM shifts WHERE id = 1;
-- 결과: primary_doctor='on_call', backup_doctor='on_call'

-- T1: primary_doctor 컬럼만 수정 (backup_doctor는 건드리지 않음)
UPDATE shifts SET primary_doctor = 'off_call' WHERE id = 1;
COMMIT;
-- 커밋 직후: primary='off_call', backup='on_call' → 규칙 만족

-- T2: backup_doctor 컬럼만 수정
-- 서로 다른 컬럼을 수정했으므로 write-write 충돌로 감지되지 않음
UPDATE shifts SET backup_doctor = 'off_call' WHERE id = 1;
COMMIT;
-- 최종: primary='off_call', backup='off_call' → 규칙 위반 ❌

RR 이상에서 패턴 a는 어떻게 되나?

• MySQL RR / MSSQL RR: 같은 row UPDATE 시 Record Lock / S Lock 충돌 → 직렬화 → Write Skew 방지
• PostgreSQL RR: 같은 row UPDATE 시 버전 충돌 감지 → T2 롤백 → Write Skew 방지

패턴 a는 RR 이상에서 대부분 방지된다. 실질적인 Write Skew 위협은 패턴 b(다른 row 수정) 다.

T1과 T2는 각자 읽은 스냅샷 기준으로 규칙을 확인했지만, 동시에 커밋되면서 둘 다 off_call이 되어버린다.

---

b. 다른 row를 수정해서 비즈니스 규칙이 깨지는 경우

병원 전체에 최소 1명의 당직 의사가 있어야 한다는 규칙이 있다. 의사마다 row가 분리되어 있다.

-- 초기 상태
doctors: { id=1, name='Alice', on_call=true }
         { id=2, name='Bob',   on_call=true }

-- T1: Alice가 당직 해제 신청 (Bob이 있으니 괜찮다고 판단)
-- T2: Bob이 당직 해제 신청 (Alice가 있으니 괜찮다고 판단)

타임라인:

-- [T1 시작] Alice의 당직 해제 요청 처리
BEGIN;

-- [T2 시작] Bob의 당직 해제 요청 처리
BEGIN;

-- T1: 현재 당직 중인 의사 수 확인
-- "2명이니까 내가 1명 줄여도 최소 1명 조건을 만족한다"고 판단
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- 결과: 2

-- T2: 동일한 조건으로 카운트 조회 (T1과 같은 스냅샷)
-- "2명이니까 내가 1명 줄여도 최소 1명 조건을 만족한다"고 판단
SELECT COUNT(*) FROM doctors WHERE on_call = true;
-- 결과: 2  ← T1과 동일한 스냅샷, T1의 변경이 반영되지 않음

-- T1: Alice(id=1) 당직 해제 → id=1 row에만 X Lock
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- 커밋 직후 DB 상태: Alice=false, Bob=true → on_call 1명, 규칙 만족

-- T2: Bob(id=2) 당직 해제
-- id=2는 T1이 수정한 row(id=1)와 다르므로 write-write 충돌 없음 → 대기 없이 바로 실행
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;
-- 최종 DB 상태: Alice=false, Bob=false → on_call 0명, 규칙 위반 ❌
-- T1과 T2가 서로 다른 row를 수정했기 때문에 충돌이 감지되지 않았고,
-- 두 트랜잭션 모두 커밋에 성공함

T1과 T2는 서로 다른 row를 수정했기 때문에 write-write 충돌이 없다. 그래서 대부분의 격리수준에서 둘 다 커밋에 성공한다.

---

3. DBMS별 동작

3.1 MySQL (InnoDB)

REPEATABLE READ

MVCC 기반으로 스냅샷을 고정해서 읽는다.

• 같은 row, 다른 컬럼 수정: 두 트랜잭션이 동일한 row를 UPDATE하면 Record Lock 충돌이 발생한다. 먼저 락을 잡은 트랜잭션이 커밋할 때까지 다른 트랜잭션은 대기하므로, 결과적으로 직렬화된다. Write Skew 방지 가능.
• 다른 row 수정: write-write 충돌이 없으므로 두 트랜잭션 모두 커밋된다. Write Skew 발생.

-- 패턴 b: 다른 row 수정 → Write Skew 발생
-- [T1 시작] Alice 당직 해제 요청
BEGIN;
-- [T2 시작] Bob 당직 해제 요청
BEGIN;

-- T1: 당직 의사 수 확인 → 2명, 조건 만족으로 판단
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 결과: 2

-- T2: 동일한 스냅샷으로 조회 → 마찬가지로 2명
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 결과: 2 (T1 변경 미반영)

-- T1: Alice(id=1) 해제 → id=1에 X Lock 획득 후 UPDATE
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;  -- Alice=false, Bob=true → 규칙 만족 상태로 커밋

-- T2: Bob(id=2) 해제
-- id=2는 T1이 건드린 row(id=1)와 다른 row → 충돌 감지 없음 → 즉시 실행
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;  -- Alice=false, Bob=false → on_call 0명 ❌

방지 방법

-- FOR UPDATE로 조회한 row 전체에 X Lock을 걸어 직렬화
BEGIN;

-- on_call=true인 모든 row(Alice, Bob)에 X Lock 획득
-- T2가 같은 FOR UPDATE 실행 시 X Lock 충돌 → T1 커밋까지 대기
SELECT COUNT(*) FROM doctors WHERE on_call = true FOR UPDATE;
-- 결과: 2 → 조건 만족, Alice 해제 진행

-- Alice 해제 (이미 X Lock 보유 중이므로 바로 실행)
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- T1 커밋 후 T2의 FOR UPDATE 대기 해제
-- T2는 이 시점에 on_call=true인 row가 Bob 1명뿐임을 확인하게 됨 → 해제 불가

SERIALIZABLE

모든 SELECT에 공유락과 Next-Key Lock이 자동 적용된다. 조회 조건에 해당하는 인덱스 범위 전체가 잠기므로, 다른 트랜잭션의 INSERT/UPDATE가 차단된다. Write Skew와 Phantom Read 모두 방지된다.

---

3.2 PostgreSQL

REPEATABLE READ

MVCC 기반이며, 쓰기 시점에 row-level 버전 충돌을 감지한다.

• 같은 row, 다른 컬럼 수정: 동일 row를 두 트랜잭션이 UPDATE하면 버전 충돌을 감지하고 나중에 쓴 트랜잭션을 롤백한다. Write Skew 방지 가능.
• 다른 row 수정: 충돌로 간주하지 않으므로 두 트랜잭션 모두 커밋된다. Write Skew 발생.

-- 패턴 b: 다른 row 수정 → Write Skew 발생
-- [T1 시작] REPEATABLE READ
BEGIN;
-- [T2 시작] REPEATABLE READ
BEGIN;

-- T1: 당직 의사 수 확인
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 결과: 2, 조건 만족으로 판단

-- T2: 동일한 스냅샷으로 조회
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 결과: 2 (T1 변경 미반영)

-- T1: Alice(id=1) 해제
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;  -- Alice=false, Bob=true → 규칙 만족 상태로 커밋

-- T2: Bob(id=2) 해제
-- PostgreSQL RR은 write-write 충돌(같은 row 수정)만 감지함
-- id=2는 T1이 수정한 row(id=1)와 다르므로 충돌로 판단하지 않음
UPDATE doctors SET on_call = false WHERE id = 2;
COMMIT;  -- Alice=false, Bob=false → on_call 0명 ❌

방지 방법

BEGIN;

-- on_call=true인 모든 row(Alice, Bob)에 FOR UPDATE로 X Lock 획득
-- T2가 동일한 FOR UPDATE 실행 시 X Lock 충돌 → T1 커밋까지 대기
SELECT * FROM doctors WHERE on_call = true FOR UPDATE;
-- 결과: Alice, Bob 2건 → 조건 만족, 해제 진행

UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;
-- T1 커밋 후 T2 대기 해제
-- T2는 이 시점에 on_call=true인 row가 Bob 1명뿐임을 확인 → 해제 불가

SERIALIZABLE

SSI(Serializable Snapshot Isolation) 알고리즘을 사용한다. 트랜잭션 간 읽기/쓰기 의존성(anti-dependency)을 추적하여, 커밋 시점에 직렬 실행으로는 재현 불가능한 결과가 발생할 것으로 판단되면 롤백한다. 서로 다른 row를 수정하더라도 규칙 위반 가능성이 있으면 차단할 수 있다.

-- [T1 시작] SERIALIZABLE
BEGIN;
-- [T2 시작] SERIALIZABLE
BEGIN;

-- T1: 당직 의사 수 확인 (SSI가 이 읽기를 추적하기 시작)
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 결과: 2

-- T2: 동일한 조건으로 읽기 (SSI가 T2의 읽기도 추적)
SELECT COUNT(*) FROM doctors WHERE on_call = true;  -- 결과: 2

-- T1: Alice 해제
-- SSI: "T1이 on_call=true를 읽었고, T1이 on_call 데이터를 수정했다" 기록
UPDATE doctors SET on_call = false WHERE id = 1;
COMMIT;  -- T1 커밋 성공

-- T2: Bob 해제 시도
-- SSI: T2도 on_call=true를 읽고(T1이 수정한 범위), on_call 데이터를 수정하려 함
-- T1과 T2 사이에 읽기/쓰기 anti-dependency 사이클이 형성됨
-- 직렬 실행으로는 재현 불가능한 결과라고 판단 → 롤백
UPDATE doctors SET on_call = false WHERE id = 2;
-- ERROR: could not serialize access due to read/write dependencies among transactions
-- T2: ROLLBACK 자동 발생

---

3.3 MSSQL

REPEATABLE READ

잠금 기반으로 동작한다. 행을 읽으면 S Lock을 걸고 트랜잭션 종료까지 유지한다.

• 같은 row, 다른 컬럼 수정: S Lock이 유지되는 동안 다른 트랜잭션의 X Lock이 차단되므로 직렬화된다. Write Skew 방지 가능.

• 다른 row 수정: 읽은 row에만 S Lock이 걸리고 범위 잠금이 없으므로, 다른 row의 수정은 차단되지 않는다. Write Skew 발생 가능.

  단, 실행 계획이나 인덱스 조건에 따라 Key-Range Lock이 잡히는 경우가 있어 "항상 발생"이라고 단정할 수는 없다.

-- 패턴 b: 다른 row 수정 → Write Skew 발생
-- COUNT(*) 조회 시 Alice, Bob 둘 다 S Lock이 걸려 Deadlock이 발생하므로,
-- Write Skew가 발생하는 시나리오는 각자 자기가 수정할 row만 SELECT하는 경우다.
-- [T1 시작] REPEATABLE READ
BEGIN TRAN;
-- [T2 시작] REPEATABLE READ
BEGIN TRAN;

-- T1: Alice 본인 row만 확인 후 당직 해제 가능 여부 판단
-- id=1(Alice)에만 S Lock 획득
SELECT on_call FROM doctors WHERE id = 1;  -- 결과: on_call=true
-- 애플리케이션: "다른 의사 수를 COUNT해서 1명 이상이면 해제 가능"이라고 판단했다고 가정

-- T2: Bob 본인 row만 확인
-- id=2(Bob)에만 S Lock 획득 (T1의 S Lock과 겹치지 않음)
SELECT on_call FROM doctors WHERE id = 2;  -- 결과: on_call=true

-- T1: Alice(id=1) 해제
-- id=1에 X Lock 요청 → T1이 이미 S Lock 보유 중이므로 업그레이드 가능
-- T2는 id=1에 S Lock 없으므로 충돌 없음
UPDATE doctors SET on_call = 0 WHERE id = 1;
COMMIT;  -- Alice=false, Bob=true → 규칙 만족

-- T2: Bob(id=2) 해제
-- id=2에 X Lock 요청 → T1은 이미 커밋되어 id=2에 락 없음 → 즉시 실행
UPDATE doctors SET on_call = 0 WHERE id = 2;
COMMIT;  -- Alice=false, Bob=false → on_call 0명 ❌
-- T1과 T2가 서로 다른 row에만 S Lock을 걸었기 때문에 충돌이 없었고,
-- 둘 다 커밋에 성공하면서 Write Skew 발생

방지 방법

BEGIN TRAN;

-- UPDLOCK: S Lock 대신 U Lock 획득
--   - U Lock은 다른 트랜잭션의 U Lock, X Lock 요청을 차단
--   - S Lock과는 공존 가능 (S Lock끼리도 공존 가능)
--   - UPDATE 직전에 X Lock으로 자동 업그레이드됨
-- HOLDLOCK: 트랜잭션 종료까지 락 유지 (기본 RC보다 강한 격리 보장)
-- 두 힌트를 함께 써야 조회 범위 전체를 트랜잭션 끝까지 잠글 수 있음
SELECT COUNT(*) FROM doctors WITH (UPDLOCK, HOLDLOCK) WHERE on_call = 1;
-- 결과: 2 → on_call=true인 모든 row(Alice, Bob)에 U Lock 획득
-- T2가 동일한 UPDLOCK 쿼리 실행 시 U Lock 충돌 → T1 커밋까지 대기

UPDATE doctors SET on_call = 0 WHERE id = 1;
COMMIT;
-- T1 커밋 후 T2 대기 해제
-- T2는 이 시점에 on_call=true인 row가 Bob 1명뿐임을 확인 → 해제 불가

SERIALIZABLE

REPEATABLE READ에 Key-Range Lock을 추가한다. 조회 조건에 해당하는 인덱스 범위 전체를 잠그므로, 해당 범위 내 INSERT/UPDATE/DELETE가 모두 차단된다. Write Skew와 Phantom Read 모두 방지된다.

---

4. 비교 표

DB / 격리수준	같은 row Write Skew	다른 row Write Skew	Phantom Read	비고
MySQL RR	방지 (Record Lock 충돌)	발생 가능 (FOR UPDATE 필요)	방지 (Next-Key Lock)	MVCC + Next-Key Lock
MySQL SERIALIZABLE	방지	방지	방지	모든 읽기에 S Lock + Next-Key Lock
PostgreSQL RR	방지 (버전 충돌 감지)	발생 가능 (FOR UPDATE 필요)	방지 (스냅샷 고정)	갭 락 없어도 스냅샷으로 방지
PostgreSQL SERIALIZABLE	방지	방지	방지	SSI anti-dependency 감지
MSSQL RR	방지 (S Lock 직렬화)	발생 가능 (UPDLOCK 필요)	발생 가능	범위 락 없음 (실행 계획 예외 있음)
MSSQL SERIALIZABLE	방지	방지	방지	Key-Range Lock

---

5. 핵심 요약

• Write Skew는 같은 row를 수정하지 않아도 발생한다. 각 트랜잭션의 수정은 유효하지만, 동시에 반영되면 비즈니스 규칙이 깨진다.
• 같은 row, 다른 컬럼 수정: REPEATABLE READ 이상이면 대부분 방지 가능 (row-level 락 또는 버전 충돌 감지).
• 다른 row 수정: MySQL, PostgreSQL, MSSQL 모두 REPEATABLE READ에서는 방지하지 못함. FOR UPDATE 또는 UPDLOCK으로 명시적 잠금이 필요.
• SERIALIZABLE: 세 DBMS 모두에서 Write Skew 방지. PostgreSQL은 SSI로, MySQL/MSSQL은 범위 잠금으로 처리.
• 혼합 격리수준 환경: 명시적 잠금(FOR UPDATE, UPDLOCK, HOLDLOCK)으로 보완 필요.