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.
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.
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.
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.
Atomicity
All of a transaction's statements happen, or none do.
Consistency
Rules you declared stay true after every write.
Isolation
How much two sessions running together can see of each other's work.
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.