postgresql when it's not your job

12:33

A Recipe for Django Transactions on PostgreSQL

19 March 2012

As noted before, Django has a lot of facilities for handling transactions, and it’s not at all clear how to use them. In an attempt to cut through the confusion, here’s a recipe for handling transactions sensibly in Django applications on PostgreSQL.

The goals are:

The bits of the recipe are:

The quick reasons behind each step:

This recipe a few other nice features:

xact() also supports the using parameter for multiple databases.

Of course, a few caveats:

To use, just drop the source (one class definition, one function) into a file somewhere in your Django project (such as the omni-present utils application every Django project seems to have), and include it.

Examples:

from utils.transaction import xact

@xact
def my_view_function1(request):
   # Everything here will be in a transaction.
   # It'll roll back if an exception escapes, commits otherwise.

def my_view_function2(request):
   # This stuff won't be in a transaction, so don't modify the database here.
   with xact():
      # This stuff will be, and will commit on normal completion, roll back on a exception

def my_view_function3(request):
   with xact():
      # Modify the database here (let's call it "part 1").
      try:
         with xact():
            # Let's call this "part 2."
            # This stuff will be in its own savepoint, and can commit or
            # roll back without losing the whole transaction.
      except:
         # Part 2 will be rolled back, but part 1 will still be available to
         # be committed or rolled back.  Of course, if an exception
         # inside the "part 2" block is not caught, both part 2 and
         # part 1 will be rolled back.

The source is available on GitHub. It’s licensed under the PostgreSQL License.

gj at 03:10, 20 March 2012:

You do actually want sometimes read blocks to be wrapped in transaction blocks, when you use serialised isolation.

Also, you’re sure turning autocommit on turns it off ? ;)

Xof at 08:13, 20 March 2012:

The good news is that there’s no problem wrapping read-only blocks with this decorator, and the right thing happens then.

And, yes, ‘autocommit’ is the worst-named set of features, ever. :)

Andrew T at 10:19, 20 March 2012:

Although I can’t speak to scaling issues (e.g. adding pgpool later) I like to wrap all database operations in a transaction; if the operation is read-only I explicitly mark the transaction READ ONLY. Should someone later add a statement which updates the database inside this block, a big nasty error pops up, which helps to keep supposedly-read-only methods truly read-only.

Ian Boston at 04:15, 13 May 2012:

Ok, so I am not a Python expert, but I notices some strange behaviour with xact where the path through the code varies and there are nested xact annotations. It seems that the _Transaction object is created once and the state is not reset so if there was a savepoint generates on one call self.sid is not none and so all subsequent seem to assume that the xact wrapper not the outermost wrapper.

If however the annotation creates a new _TransactionWrapper object with a __call__ method that does exactly what the old _Transaction object but this time creates a new _Transaction object on every the state of the _Transaction object is not shared between invocations in different sequences, nested invocations or invocations on different threads. It appears to fix the bugs I was seeing. You may have already fixed the problem. I submitted an issue at your GitHub repo with a patch. Did I say, I am no Python expert, know little about how Django threads in production and so may have gone overboard in making it re-entrant and thread safe.

Xof at 08:45, 16 May 2012:

I’m not able to reproduce the threading/reentrancy problem; as far as I can tell, each invocation of the wrapper or decorator creates a new _Transaction object, as you’d expect.

Xof at 09:25, 16 May 2012:

Ah! I am able to reproduce it when used as a function decorator rather than a context manager. Excellent catch! I’ll get a fix done.

ju at 05:30, 16 July 2012:

The bug that you talk about in Django’s transaction handling have a patch now that I’m using with Django 1.4.

https://code.djangoproject.com/ticket/16047

The problem will also be fixed in Django 1.5 with the same patch.

https://github.com/django/django/commit/f572ee0c65ec5eac9edb0cb3e35c96ec86d89ffb

My queston is, is it OK to use xact() when I use this patch?

Xof at 10:21, 16 July 2012:

Yes, it’s fine to use xact with the patch; it just does the same thing twice at a (very) small efficiency loss.

Dan LaMotte at 12:30, 30 August 2012:

I was honestly about ready to pull my hair out this morning when I deployed pgbouncer and noticed all connections were being closed as “unclean”. I use Django 1.4, autocommit, and the transaction.commit_on_success decorator on views. I thought I was safe.

I was wrong.

Stumbled upon this blog, tried out xact. Problem solved. No more is every connection closed as unclean. The pool is _actually_ working.

You are a lifesaver. Thank you.

Xof at 13:15, 30 August 2012:

You are most welcome!

Mike Sun at 10:07, 19 October 2012:

xact() provides the behavior that Django’s transaction decorators/context managers should be providing: intelligent use of savepoints to mimic nested transactional behavior.

But given that most Django sites utilize many third party apps, (e.g. django.contrib.admin, django.contrib.auth, django-socialauth) whose codebase utilizes the default Django transaction decorators/context managers/primitives that don’t behave intelligently in nested situations, how safe is it to using xact() around code that might call code form these third party apps? As mentioned in your blog post, wrapping an xact() transaction around an existing commit_on_success() transaction would cause the outer xact transaction to likely end when the inner commit_on_success() transaction commits or rollsback, resulting in the rest of the code defaulting to default autocommit mode. That could be potentally very problematic if the programmer assumed that data changes made in that latter portion of code was done atomically.

The only way I see this working properly is if the native Django transaction code is refactored to be like xact().

Any ideas?