Concurrent transactions

In short: When two transactions read and then write the same data, one can overwrite the other's change (a lost update); a single conditional UPDATE or a row lock prevents it.

Problem

The last packs of coffee

There are 15 packs of coffee beans. The till in the shop and the online shop each sell one pack in the same second. Both programs do the same thing: read the stock, subtract 1 and write the new number.

Run the steps one at a time. Every query really runs, on a shared database:

Till · Onlinedatabase: shop

Till
```
SELECT stock FROM products WHERE id = 17;
```
The till in the shop reads the coffee stock: 15.
Online
```
SELECT stock FROM products WHERE id = 17;
```
In the same second an online order reads the same: 15.

Till

UPDATE products SET stock = 14 WHERE id = 17;

The till sells 1 pack and writes 15 − 1 = 14.

Online
```
UPDATE products SET stock = 14 WHERE id = 17;
```
The online order also sells 1 and also writes 14. It knows nothing about the till.
Till
```
SELECT stock FROM products WHERE id = 17;
```
2 packs were sold, but the stock went down by 1. One change is lost.

The idea

A lost update

This is a lost update. Each query on its own is right. The fault is the gap between the read and the write: during it the other program wrote something that was then overwritten.

The simplest protection is to have no gap. Let the database read and write itself, in one query:

Till · Onlinedatabase: shop

Till
```
UPDATE products SET stock = stock - 1
WHERE id = 17 AND stock >= 1;
```
The till tells the database “take 1 off, if there is one”. Reading and writing are one query.
Online
```
UPDATE products SET stock = stock - 1
WHERE id = 17 AND stock >= 1;
```
The online order does the same. The database runs the two one after the other, each on the real current value.

Till

SELECT stock FROM products WHERE id = 17;

15 − 2 = 13. Nothing is lost.

The condition stock >= 1 also guards against selling something that is not there. How many rows the query changed tells the program whether it sold:

Till · Onlinedatabase: shop

Till
```
UPDATE products SET stock = stock - 5
WHERE id = 23 AND stock >= 5;
```
The knife set: there are 5. The till sells all 5 at once (a company gift): 1 row changed.
Online
```
UPDATE products SET stock = stock - 5
WHERE id = 23 AND stock >= 5;
```
The online order wants 5. The condition is no longer true: 0 rows changed. The program tells the customer “out of stock” instead of selling knives that do not exist.

Online

SELECT stock FROM products WHERE id = 23;

The stock did not go below zero.

Try it

Three packs, if there are three

Write one query that sells 3 packs of coffee (product 17) only if there are at least 3.

SQLdatabase: shop

SELECT id, stock FROM products WHERE id = 17;

Solution

UPDATE products SET stock = stock - 3
WHERE id = 17 AND stock >= 3;

The idea

Isolation levels

Not everything fits in one query. So databases keep transactions apart from each other, at levels. The higher the level, the fewer strange things you see, but the more waiting there is:

Level	Dirty read	Non-repeatable read	Phantoms
READ UNCOMMITTED	possible	possible	possible
READ COMMITTED	no	possible	possible
REPEATABLE READ	no	no	possible
SERIALIZABLE	no	no	no

Dirty read: you see a change the other transaction has not confirmed yet (and may undo).
Non-repeatable read: you read a row twice in your transaction and get different values, because someone changed it in between.
Phantom: you repeat a query with a condition and new rows appear that someone added.

PostgreSQL defaults to READ COMMITTED, MySQL (InnoDB) to REPEATABLE READ. SQLite is the strictest: only one transaction writes at a time, and the others wait.

More precisely, if you are curious

The table comes from the standard. Real databases do more than it requires: in PostgreSQL READ UNCOMMITTED behaves like READ COMMITTED, and REPEATABLE READ does not allow phantoms either.

The idea

Locks and deadlock

To keep these promises, the database locks: a transaction that changes a row holds it until it says COMMIT or ROLLBACK. Whoever wants the same row waits.

Sometimes two wait for each other: the first transaction holds the coffee and wants the tea, the second holds the tea and wants the coffee. This is a deadlock. The database notices it and stops one of them with an error. The program must be ready to retry.

How to make it rarer: short transactions (no waiting for a person halfway), and every program locking in the same order, for example by increasing product id.

More precisely, if you are curious

In PostgreSQL and MySQL, SELECT … FOR UPDATE locks the rows it reads right away, so “read, compute, write” inside one transaction becomes safe. SQLite has no FOR UPDATE. There, BEGIN IMMEDIATE locks the whole database for writing at the start of the transaction.

Test

Check yourself

1. Why does UPDATE products SET stock = stock - 1 not lose sales, while “read, compute in the program, write” does?Because it is shorterBecause reading and writing happen in one query, on the current valueBecause it uses WHERE

2. What is a “dirty read”?Reading without WHEREReading data another transaction has not confirmed yetReading old data from a cache

3. Two transactions wait for each other: the first holds row 1 and wants row 2, the second the other way round. What does the database do?Stops one with an error (deadlock) and lets the other go onRuns them at onceWaits forever

Made by Ivo Gergov as part of Evo Tech's campaign for accessible programming education ·

Concurrent transactions

Cookies