Transactions and two users changing the same data

A transaction groups database statements into one unit. Two users changing the same row at the same moment can still leave wrong data.

On enlistment day, Section A of MGT 45 shows 1 slot left in the university's online enlistment system, and you press Enlist. On another laptop, a second student presses Enlist for the same section at the same moment.

Two laptopsTwo sessions
Enlistment systemThe server
DatabaseSection row, 1 slot left
Think first. One slot is left and two students press Enlist together. What must the database do so that only one of them is enlisted? Write down one idea.
Typical answers are "check again", "lock it" and "do it in one step". The next step tests each of them. Collect a few answers before anyone presses Play. Ask what the second student should see when the slot is gone.

Each tab is a different way to enlist a student in the last slot. Press Play on each tab, from left to right, and watch the Slots left column.

What this attempt does
More detail

The statements behind each tab

Here slots is the number of free slots in the section. BEGIN starts a transaction and COMMIT saves it.

-- No transaction: each session reads, decides, then writes SELECT slots FROM courses WHERE code = 'MGT 45'; UPDATE courses SET slots = 0 WHERE code = 'MGT 45'; -- Transaction only: the same statements between BEGIN and COMMIT BEGIN; SELECT ...; UPDATE ...; INSERT ...; COMMIT; -- Lock the row: the second session waits at the SELECT BEGIN; SELECT slots FROM courses WHERE code = 'MGT 45' FOR UPDATE; UPDATE ...; INSERT ...; COMMIT; -- One statement: the check is part of the change UPDATE courses SET slots = slots - 1 WHERE code = 'MGT 45' AND slots > 0;

Lost update

In the first tab, session B wrote a value based on a slot count that was already out of date. This bug is called a lost update, and it is common in student projects.

What the fixes cost

The lock makes the second session wait, so on enlistment day nine thousand users pressing Enlist would wait on one row. The single statement needs no waiting, and the row count it reports tells you the result: 1 means you got the slot, 0 means somebody else did. It works whenever the decision can be written as a condition.

Enlistment day is covered in Case study: enlistment. The idempotency key in Payments and gateways solves a similar problem for repeated requests that arrive over HTTP.

A transaction makes a group of statements succeed together or fail together. At the default setting it leaves other sessions free to read the old slot count while you decide. To protect the slot, lock the row during the decision, or write the check and the change as one statement.

More detail

Isolation is the setting that controls how much two sessions can see of each other's work. At the default level in Postgres, called READ COMMITTED, session B can still read the old slot count while session A is deciding. Many people assume the defaults protect them fully, and they do not.

The fix is to tell the database what you need. A lock holds the row, and a single statement lets the database order the two requests itself.

A transaction can promise four things, known together as ACID.

A
Atomicity

All of a transaction's statements happen, or none do.

C
Consistency

Rules you declared stay true after every write.

I
Isolation

How much two sessions running together can see of each other's work.

D
Durability

After a save, the change survives a power failure.

You can now say why two users need a transaction and what it guarantees.

More detail

Atomicity

If the INSERT works and the UPDATE fails, the database undoes the INSERT. BEGIN and COMMIT give you this guarantee.

Consistency

A rule such as CHECK (slots >= 0), or a unique key, makes the database refuse any write that breaks it, whichever session sent the write and whatever the timing.

Isolation

This is the guarantee that failed in the first two tabs.

Durability

After COMMIT returns, the change has been written to disk. Of the four guarantees, this is the easiest to trust and the most expensive for the database to provide.