Planned lessonMID-015-L06

Demo: lost update in two terminals

On This Lesson 3 topics

This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.

In this lesson

reproduce a lost update and fix it.

Draft outline details

Create stock(product_id, qty) with one row, qty 10.
In two psql terminals, both BEGIN; SELECT qty (10), then both UPDATE stock SET qty = 9 and commit; final qty is 9, not 8.
Fix 1: atomic UPDATE stock SET qty = qty - 1 WHERE qty > 0 (final 8).
Planned mistake: assume REPEATABLE READ always lets both commit; show T2 fails with a serialization error ("could not serialize access due to concurrent update"). Explain this is correct and needs a retry.
Fix 2: SELECT ... FOR UPDATE so the second waits.

Prepares MID-015-LAB1.

Which fix needs no extra round trip?

Show answer

The atomic UPDATE expression.

Apply this lesson with the module's lab, exercises and quiz.

Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.