Write-Write Conflict and Row Lock

Topic: Databases

Concrete problem

An online shop stores stock = 100 for one product. Two web requests run this statement at the same moment:

UPDATE products SET stock = stock - 10 WHERE id = 1;

Both requests commit. The correct final value is $100-10-10=80$. Which mechanism makes sure the result is 80 and not 90?

Background terms

• MVCC: MVCC means multi-version concurrency control. Postgres does not edit a row in place. An UPDATE creates a new version of the row. The old version stays until no transaction needs it.
• Snapshot: A snapshot is a fixed view of which row versions are visible. A reader uses a snapshot to pick one committed version of each row. A reader never waits for a writer, because the old version is still there.
• READ COMMITTED: READ COMMITTED is the default isolation level in Postgres. Each statement takes a new snapshot when it starts. The statement sees only changes that are committed at that moment.
• Lost update: A lost update happens when two transactions read the same value, both compute a new value, and both write. The second write erases the first change. The first change is lost.
• Row lock: A row lock is a mark on one row. The mark says “transaction X is changing this row”. Another transaction that wants to change the same row must wait until X commits or rolls back.

Intuition

Readers and writers do different jobs.

• A reader needs any one consistent version. An old committed version is good enough. So a reader never waits.
• A writer needs the newest version, because the new version builds on it. So a writer must know the final state of the newest version.

Transaction A has changed row 1 and has not committed. B wants to change row 1. B has three options. Each option fails:

1. Build on A’s version. A may roll back. Then B would build on a version that never existed.
2. Ignore A’s version. A may commit. Then B would erase A’s change. This is the lost update.
3. Merge both versions. stock - 10 is a function of the current value. The database cannot merge two functions without knowing what they mean.

One option is left. B waits until A ends. After A ends, the newest version is known. The row lock makes B wait.

A row lock stays until the transaction ends. A transaction ends with COMMIT or ROLLBACK. The end of a statement does not release the lock.

Numeric example

Start: stock = 100. A and B run the same UPDATE. A starts first.

Step	Transaction A	Transaction B	Row 1
1	UPDATE sets stock to $100-10=90$		A’s version: 90 (not committed)
2		UPDATE finds a row lock held by A. B waits.	unchanged
3	COMMIT	B wakes up	90 (committed)
4		B reads the newest version, 90. B sets $90-10=80$.	B’s version: 80
5		COMMIT	80 (committed)

In step 4, B does not use the old value 100. B re-reads the row. This is the key step. It is why the final value is 80.

Visual

sequenceDiagram
  participant A as Transaction A
  participant R as Row 1
  participant B as Transaction B
  A->>R: UPDATE stock = 100 - 10 (row lock taken)
  B->>R: UPDATE stock = stock - 10
  R-->>B: locked by A, wait
  A->>R: COMMIT (row lock released)
  R-->>B: wake up, newest version is 90
  B->>R: write 90 - 10 = 80
  B->>R: COMMIT

Notation

Postgres has no special syntax for this lock. A plain UPDATE or DELETE takes the row lock by itself. The lock is implicit.

In pg_locks the waiting transaction shows a lock request of type transactionid. B waits for A’s transaction id to finish. Node N5 covers this.

Plain-English translation

For the update $v_{new}=v_{old}-10$:

• $v_{old}$ means “the value in the newest committed version”.
• $v_{new}$ means “the value in the version that this writer creates”.
• The lock makes sure that $v_{old}$ is final before the writer reads it.

Without the lock, both writers read $v_{old}=100$. Both write $100-10=90$. The final value is 90. One subtraction is lost.

Equation

With the row lock, the two writers run one after the other:

$$v_0=100,v_1=v_0-10=90,v_2=v_1-10=80$$

What changes when parameters change

What changes if A ends differently, or if the rows or the isolation level change?

• A rolls back. B wakes up. The newest version is the old value, 100. B writes $100-10=90$. A’s change never existed.
• A and B change different rows. The two locks are on different rows. Nobody waits.
• B only reads. A plain SELECT takes no row lock. B does not wait.
• B’s WHERE clause no longer matches. In READ COMMITTED, B re-checks the WHERE clause on the newest version. If the clause no longer matches, B skips the row.
• Isolation level REPEATABLE READ. B cannot re-read the newest version, because B must keep one snapshot. After A commits, B fails with a serialization error. The application must retry.

Serialization error

A serialization error is an error that tells the application to run the whole transaction again. Postgres raises it when a transaction cannot continue without breaking the rules of its isolation level.

Practice problem

Try these on a later review. Do not open the answers first.

acct has row 1 with balance = 100 and row 2 with balance = 50. READ COMMITTED.

1. A runs UPDATE acct SET balance = balance + 5 WHERE id = 1 and does not commit. Then D runs UPDATE acct SET balance = balance * 2 WHERE id = 1. A commits. What is the final balance of row 1? What would it be if D had run first and A had waited?
2. A updates row 1 and then wants to update row 2. B updates row 2 and then wants to update row 1. Both run at the same time. What happens?
3. Plain SELECT never takes part in the deadlock of problem 2. Why not?

Answers

1. $(100+5)\times 2=210$. D waits for A, then builds on A’s committed version. If D ran first: $100\times 2+5=205$. The order of the lock decides the result, because the two changes do not commute.
2. Each session waits for a lock that the other holds. This is a deadlock. Postgres detects it after deadlock_timeout (1 second by default). It aborts one session with error 40P01. The other session continues.
3. A deadlock needs a cycle of waits. A reader never waits for a writer, so a reader adds no edge to the cycle. Only writers wait for writers.

Demo

Each scenario opens real concurrent sessions against a local Postgres. The log shows every statement with a time stamp. The folder is 01-write-write-conflict.

Scenario	What it shows
01-readers-never-wait	A plain SELECT does not wait for a row lock.
02-write-write-wait	The numeric example. B waits for A. Final stock is 80.
03-writer-rollback	A rolls back. B builds on the old value. Final stock is 90.
04-lost-update-app-side	The lost update without a lock. Final stock is 90.

To run one scenario, start Postgres with ./up.sh. Then run go run ./01-write-write-conflict/01-readers-never-wait in the repository. To run all scenarios of this concept, use ./run.sh 01-write-write-conflict.

Quiz

Asked on 2026-10-05 after the lesson. All three answers were correct.

Quiz (2026-10-05)

Q1. stock is 100. A and B each run UPDATE products SET stock = stock - 10 WHERE id = 1. A runs first. B waits. A rolls back. B commits. READ COMMITTED. What is the final stock?

• 80

• 100

• An error for B

• 90

Answer and reasoning

Your answer: 90 (correct). Correct answer: 90.

A rolled back, so the newest committed version is 100. B wakes up, re-reads that version, and writes $100-10=90$. The answer 80 would mean that B built on A’s version. The answer 100 would mean that B’s update is cancelled with A’s. The error answer is wrong, because under READ COMMITTED B does not fail. B continues.

Q2. Why must B wait for A in the same setup?

• B needs the newest version of the row, and that version is not final until A ends

• B needs a snapshot of the row, and A’s change is not visible in it

• B needs to read A’s uncommitted value, and Postgres hides it until A commits

• B needs its own copy of the row, and the database has only one copy

Answer and reasoning

Your answer: B needs the newest version of the row, and that version is not final until A ends (correct). Correct answer: B needs the newest version of the row, and that version is not final until A ends.

A write builds on the newest version. A may still commit or roll back, so that version is not final. The second option mixes up the reader rule (snapshots) with the writer rule. The third option is wrong because B never needs A’s uncommitted value. The fourth option is wrong because Postgres makes a new row version for each write.

Q3. A has updated row 1 and has not committed. Which statement from B waits for A?

• Plain SELECT of row 1

• UPDATE of row 2

• Plain SELECT of row 2

• UPDATE of row 1

Answer and reasoning

Your answer: UPDATE of row 1 (correct). Correct answer: UPDATE of row 1.

Only a write to the same row conflicts with A’s row lock. A plain SELECT takes no row lock. A different row has a different lock.

My reasoning

Probe on 2026-10-05. The user expected that session B “writes its own version” and that Postgres “merges both versions when both commit”. The user also expected the final balance to be 90 (a lost update). The diagnosis was a real misconception. The user applied the MVCC rule for readers (“never wait”) to writers.

Fix: a version is built from the newest version. So writers must be ordered. The row lock orders them.

Session history

• 2026-10-05 — First taught. Quiz all correct (rollback case, reason for the wait, which statement waits). See 2026-10-05 Databases - Postgres Locking.
• 2026-10-05 — Demo section added. See 01-write-write-conflict.