Django’s transaction.atomic() It may not be as atomic as you think by Michal Charemza Saturday October 10th, 2020

Introduction

I have a confession: I assumed things about Django’s transaction.atomic() that are not true, at least not true by default in PostgreSQL.

I assumed that in a transaction.atomic() context as below, database statements are protected from any race conditions, and everything will Just Work™.

with transaction.atomic():
    # Database statements

But that’s really not true.

Enter the world of transaction isolation levels: None with autocommit, Read committed, Repeatable read, Serializable, and “do it yourself”.

Do it yourself: select_for_update

You can “add to” isolation inside transactions using select_for_update.

This blocks until the current transaction can obtain the same locks as an UPDATE would on the matching rows, which are then kept until the end of the transaction .

 1##############
 2# Setup Django
 3import django
 4
 5django.setup()
 6
 7
 8#############
 9# Test proper
10
11import threading
12import time
13import pytest
14from django.db import transaction
15from django.db.models import F, Subquery
16
17from app.models import Sock
18
19
20@pytest.mark.django_db
21def test_select_for_update_blocks():
22    def create():
23        Sock.objects.all().delete()
24        Sock.objects.create(id_a=1, id_b=1, colour="black")
25
26    create_thread = threading.Thread(target=create)
27    create_thread.start()
28    create_thread.join()
29
30    barrier = threading.Barrier(2)
31
32    def select_for_update_with_sleep():
33        with transaction.atomic():
34            list(Sock.objects.select_for_update().filter(id_a=1))
35            barrier.wait()
36            time.sleep(11)
37
38    time_to_select_for_update = None
39
40    def select_for_update():
41        nonlocal time_to_select_for_update
42        with transaction.atomic():
43            barrier.wait()
44            start = time.time()
45            list(Sock.objects.select_for_update().filter(id_a=1))
46            end = time.time()
47            time_to_select_for_update = end - start
48
49    select_for_update_with_sleep_thread = threading.Thread(
50        target=select_for_update_with_sleep
51    )
52    select_for_update_with_sleep_thread.start()
53    select_for_update_thread = threading.Thread(target=select_for_update)
54    select_for_update_thread.start()
55
56    select_for_update_with_sleep_thread.join()
57    select_for_update_thread.join()
58
59    assert time_to_select_for_update >= 10.0

A use for this is in a Read committed transaction to enforce serializability, without the risk of commits failing as they could in a true Serializable transaction, but at the cost of the time of blocking, and the risk of deadlock .

 1##############
 2# Setup Django
 3import django
 4
 5django.setup()
 6
 7
 8#############
 9# Test proper
10
11import threading
12import pytest
13from django.db import DatabaseError, transaction
14
15from app.models import Sock
16
17
18@pytest.mark.django_db
19def test_select_for_update_can_deadlock():
20    def create():
21        Sock.objects.all().delete()
22        Sock.objects.create(id_a=1, id_b=1, colour="black")
23        Sock.objects.create(id_a=2, id_b=2, colour="white")
24
25    create_thread = threading.Thread(target=create)
26    create_thread.start()
27    create_thread.join()
28
29    barrier_1 = threading.Barrier(2)
30    barrier_2 = threading.Barrier(2)
31
32    caught = None
33
34    def update_a():
35        nonlocal caught
36        try:
37            with transaction.atomic():
38                list(Sock.objects.select_for_update().filter(id_a=1))
39                barrier_1.wait()
40                barrier_2.wait()
41                list(Sock.objects.select_for_update().filter(id_a=2))
42        except Exception as exception:
43            caught = exception
44
45    def update_b():
46        nonlocal caught
47        try:
48            with transaction.atomic():
49                barrier_1.wait()
50                list(Sock.objects.select_for_update().filter(id_a=2))
51                barrier_2.wait()
52                list(Sock.objects.select_for_update().filter(id_a=1))
53        except Exception as exception:
54            caught = exception
55
56    update_a_thread = threading.Thread(target=update_a)
57    update_a_thread.start()
58    update_b_thread = threading.Thread(target=update_b)
59    update_b_thread.start()
60
61    update_b_thread.join()
62    update_b_thread.join()
63
64    assert isinstance(caught, DatabaseError)
65    assert "deadlock" in caught.args[0]

At the time of writing this appears to be the only technique that is a first-class Django citizen: no need to write explit SQL.

Summary

There is no magic or one-size-fits-all approach to database transactions, and you can’t trust Django to always do the right thing, even with a transaction.atomic() or select_for_update.

Thank you to a collegue of mine who basically told me what I thought I knew was wrong.

Thanks also to the PostgreSQL isolation levels documentation and to the PostgreSQL Serializable Snapshot Isolation (SSI) documentation on which some of the above examples are based.

These pages are good for further reading for more details on INSERT, DELETE, and other forms of SELECT which are ommitted in the above for brevity.