I’ve been reading through the excellent Designing Data-Intensive Applications by Martin Kleppmann. In chapter 7, on transactions, the author covers a topic called write skew. Write skew is a race condition that is best explained with an example. So I’ll reuse the one provided in the chapter.

Imagine a simple scheduling application for a hospital that has the following constraint: There must always be a doctor on call.

Here is the current state of the doctors table.

+-------+---------+
| name  | on_call |
+-------+---------+
| Bob   | 1       |
| Alice | 1       |
+-------+---------+

The application logic to remove a doctor from being on call might look something like this:

with transaction.atomic():
    on_call = Doctor.objects.filter(on_call=True).count()
    if on_call >= 2:
        Doctor.objects.filter(name=doctor_name).update(on_call=False)

Which would translate roughly to the following SQL transaction:

START TRANSACTION;
SELECT count(*) FROM doctors WHERE on_call=true;
-- application checks the count >= 2
UPDATE doctors SET on_call=false WHERE name="Alice";
COMMIT;

Now imagine that both Alice and Bob are feeling unwell. They log into the the application and both try to go off call at nearly the same time. The below image represents the overlapping transactions in time.

What will happen is that both transactions will read the number of on call doctors as two and then each proceed to update the respective doctor as no longer being on call.

+-------+---------+
| name  | on_call |
+-------+---------+
| Bob   | 0       |
| Alice | 0       |
+-------+---------+

Our constraint is now broken and there is no one on call in case of an emergency, which in the worst case, could result in the loss of a life.

You might think that using an isolation level like READ COMMITTED would prevent this, but it would just require the overlapping time of the transactions to be closer together. As long as both SELECT statements execute before the other transaction commits then it would occur.

This exact scenario of:

has been the source of some expensive bugs. Check out this reddit post where a user details how they stole 100 bitcoin from a crypto currency exchange using this exact technique.

The simple solution is to use a SERIALIZABLE isolation level in your database, but this incurs a pretty large performance hit on the number of transactions you are able to run per second. A more granular approach is to use the SELECT ... FOR UPDATE clause.

SELECT ... FOR UPDATE puts an exclusive lock on the rows returned from the SELECT. This means another transaction trying to perform a SELECT ... FOR UPDATE will block until the the first transaction commits or performs a rollback.

Let’s update our application logic to use this (Django provides the select_for_update queryset method to achieve this).

with transaction.atomic():
    on_call = Doctor.objects.select_for_update().filter(on_call=True).count()
    if on_call >= 2:
        Doctor.objects.filter(name=doctor_name).update(on_call=False)

Now if two attempts to pass through this code path occur at or near the same time the losing transaction will be blocked at the SELECT until the winning transaction finishes.

One interesting property of this is that even under the REPEATABLE READ isolation level the SELECT ... FOR UPDATE behaves like READ COMMITED for that expression (at least with MySQL and InnoDB). See what I mean by checking out this transaction where I had another transaction going in a different tab that was changing the on_call status for Bob (some output removed for clarity).

> select @@transaction_ISOLATION;
+-------------------------+
| @@transaction_ISOLATION |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

> start transaction;
> -- transaction in another tab just set Bob's on_call status to false;
> select * from doctors;
+-------+---------+
| name  | on_call |
+-------+---------+
| Bob   | 1       |
| Alice | 1       |
+-------+---------+
> -- the other transaction has now submitted the change;
> select * from doctors;
+-------+---------+
| name  | on_call |
+-------+---------+
| Bob   | 1       |
| Alice | 1       |
+-------+---------+
> select * from doctors for update;
+-------+---------+
| name  | on_call |
+-------+---------+
| Bob   | 0       |
| Alice | 1       |
+-------+---------+
> commit;

Now go forth and use your new found knowledge for good and definitely not for trying to skim gold/points/crypto currency/etc. from some application.