.. index::
   pair: Transactions; transaction.atomic() not so atomic

.. _transaction_michal_2020_10_10:

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

.. seealso::

   - https://charemza.name/blog/posts/django/postgres/transactions/not-as-atomic-as-you-may-think/
   - https://github.com/michalc
   - https://twitter.com/michalcharemza
   - https://www.postgresql.org/docs/12/transaction-iso.html
   - https://wiki.postgresql.org/wiki/SSI

.. contents::
   :depth: 3

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 <https://github.com/michalc/django-postgres-isolation-levels/blob/master/test_select_for_update_blocks.py>`_.


.. literalinclude:: test_select_for_update_blocks.py
   :linenos:


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 <https://github.com/michalc/django-postgres-isolation-levels/blob/master/test_select_for_update_can_deadlock.py>`_.

.. literalinclude:: test_select_for_update_can_deadlock.py
   :linenos:



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 <https://www.postgresql.org/docs/12/transaction-iso.html>`_ and to the
`PostgreSQL Serializable Snapshot Isolation (SSI) documentation <https://wiki.postgresql.org/wiki/SSI>`_ 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.