MVCC Internals and Snapshot Isolation's Anomaly: Write Skew TODO
Concept
MVCC never overwrites an existing row on update — it creates a new version, and each transaction sees only the versions visible under its own snapshot. That lets reads never block writes and writes never block reads; which version is visible is decided by transaction ID and visibility rules. Snapshot isolation, built on top of this, prevents dirty reads, non-repeatable reads, and lost updates, but it does not guarantee serializability. The classic anomaly is write skew: two transactions read the same set of rows and each updates a different, non-overlapping row, so each transaction individually respects the constraint, but the two together violate an invariant. The fixes are to use a serializable isolation level such as SSI, to explicitly lock the rows a decision was based on, or to physicalize the invariant as a single row or a unique constraint.
Trusting an isolation level's name and leaving application invariants to the database creates bugs that surface quietly, only once load rises enough for concurrent transactions to actually overlap.
Code & Formula
# MVCC 내부와 스냅샷 격리의 이상현상(write skew) — 두 트랜잭션이 같은 스냅샷을 읽고 서로 다른 행을 갱신해 불변식이 깨진다.
# "온콜 최소 1명" 불변식을 스냅샷 격리에서 재현한다: 각자 상대가 아직 온콜이라 보고 자신을 뺐지만 합치면 0명이 된다.
class VersionedTable:
def __init__(self, initial):
self.versions = {k: [(0, v)] for k, v in initial.items()} # key -> [(txn_id, value), ...]
def snapshot_read(self, key, as_of_txn):
# as_of_txn 이전에 커밋된 가장 최신 버전만 보인다 (스냅샷 격리)
visible = [v for (tid, v) in self.versions[key] if tid <= as_of_txn]
return visible[-1] if visible else None
def write(self, key, txn_id, value):
self.versions[key].append((txn_id, value))
table = VersionedTable({"alice_on_call": True, "bob_on_call": True})
SNAPSHOT_TXN = 0 # 두 트랜잭션 모두 같은 시점의 스냅샷에서 시작
a_sees_bob = table.snapshot_read("bob_on_call", SNAPSHOT_TXN) # A: bob이 아직 온콜이니 alice는 빠져도 된다
b_sees_alice = table.snapshot_read("alice_on_call", SNAPSHOT_TXN) # B: alice가 아직 온콜이니 bob도 빠져도 된다
print("A가 본 bob 상태:", a_sees_bob, "→ alice를 오프콜로 전환")
print("B가 본 alice 상태:", b_sees_alice, "→ bob을 오프콜로 전환")
if a_sees_bob:
table.write("alice_on_call", txn_id=1, value=False)
if b_sees_alice:
table.write("bob_on_call", txn_id=2, value=False)
final_alice = table.snapshot_read("alice_on_call", as_of_txn=99)
final_bob = table.snapshot_read("bob_on_call", as_of_txn=99)
print(f"\n최종 상태: alice={final_alice}, bob={final_bob}")
invariant_ok = final_alice or final_bob
note = "" if invariant_ok else " ← write skew로 위반됨"
print("불변식(최소 1명 온콜) 유지 여부:", invariant_ok, note)
docs/code/algorithms/algorithms-69.py
Exercise
In PostgreSQL, reproduce a write skew that violates a balance-sum constraint using two sessions under REPEATABLE READ, then switch to SERIALIZABLE and check whether a serialization failure now occurs.
Practical Connection
Any path that 'writes based on a value it just read' — deducting remaining order quantity, checking a collateral limit — is a textbook case of write skew in an off-chain database; on-chain, the same problem is solved by atomizing it into a single state update.
Where it lands in Jayverse
- Verex: audit every "read then write based on it" path for write skew. Remaining order quantity and collateral/margin checks are the textbook case; lock the rows involved or run those checks at SERIALIZABLE isolation under concurrent load.
- Verex: physicalize on-chain invariants as a single atomic state update. Don't assume a balance or collateral invariant is safe just because it is on-chain; enforce it as one check-and-set on one storage slot.
- Auditor: add a standing write-skew reproduction test for any new off-chain service. Reproduce a concurrent-session violation of a balance-sum invariant before shipping, rather than trusting an isolation level's name.
Key expressions
| Expression | 뜻 · 쓰이는 자리 |
|---|---|
| write skew | 쓰기 왜곡(각자는 규칙을 지켰지만 합쳐서 위반되는 이상 현상) · 스냅샷 격리의 대표적 결함. "The classic anomaly is write skew" |
| snapshot isolation | 스냅샷 격리 수준 · 각 트랜잭션이 자신만의 스냅샷을 보는 격리 수준. "Snapshot isolation, built on top of this" |
| dirty reads | 더티 리드(커밋 안 된 값을 읽는 현상) · 스냅샷 격리가 막아주는 이상 현상 중 하나. "prevents dirty reads, non-repeatable reads, and" |
| lost updates | 갱신 손실(동시 쓰기로 한쪽 변경이 사라짐) · 스냅샷 격리가 막아주는 또 다른 이상 현상. "reads, and lost updates, but it does" |
| surface quietly | 조용히(눈에 띄지 않게) 드러나다 · 부하가 커져야 비로소 발견되는 버그를 가리킴. "creates bugs that surface quietly, only once" |
| physicalize the invariant | 불변조건을 물리적 구조(단일 행 등)로 구현하다 · 애플리케이션 규칙을 DB 제약으로 강제하는 해법. "physicalize the invariant as a single row" |
| explicitly lock the rows | 근거로 삼은 행을 명시적으로 잠그다 · 동시성 문제를 막는 수동 잠금 기법. "explicitly lock the rows a decision was based on" |
| MVCC | 다중버전 동시성 제어(Multi-Version Concurrency Control) · 갱신 시 기존 행을 덮어쓰지 않고 새 버전을 만드는 방식. "MVCC never overwrites an existing row on update" |
| SSI | 직렬화 가능 스냅샷 격리(Serializable Snapshot Isolation) · write skew를 막는 직렬화 가능 격리 수준. "to use a serializable isolation level such as SSI" |
If you study this on a given day, add a note link and a ✅ to this line in the source curriculum (docs/knowledge/dev-100-curriculum.md) and this spot will lead straight to the note body. You can also write directly on this page — but regenerating overwrites it, so it's safer to keep anything you want to save as markdown under docs/algorithms/.