19:34

Django and PostgreSQL “Idle In Transaction” Connections

25 October 2010

A well-known issue that can come up with Django sites running on PostgreSQL is that connections in “Idle in Transaction” state can pile up. There’s a relatively straight-forward fix, but ultimately, it’s due to a bug in Django’s transaction management, at least when PostgreSQL is the back-end.

Let’s run through it.

First, what’s the “Idle in Transaction” state in PostgreSQL? Like the name implies, it’s when a transaction has been opened on a connection to a PostgreSQL server, but no query is running at that moment. Generally, you want to avoid these; they consume considerable system resources, defeat connection poolers (like pgbouncer) running in transaction pooling mode, and can seriously slam the performance of your server.

Django sites seem to be particularly susceptible to this; I’ve encountered client sites building up quite literally hundreds of these connections.

The short reason why is: Django, in its default transaction management scheme, does not issue a COMMIT if it thinks no data modification has occurred on the database. This wouldn’t be a problem if a BEGIN hadn’t been issued, but by default, one is issued by the psycopg2 database interface. Thus, in a read-only transaction, a BEGIN is issued, but no COMMIT, and since the connection stays open (potentially for quite some time), the connection sits in Idle in Transaction more or less indefinitely.

There’s even a bug in the Django issue tracker over it, but there’s a lot of discussion there over what to do about the existing behavior, and how to maintain backwards compatibility. In my opinion, there is no backwards-compatibility to maintain: Any application which relies on transactions staying open a random period of time and then being closed in some arbitrary fashion is essentially depending on uninitialized memory, and shouldn’t be surprised when the decision is made firmly one way or another.

That being said, how can we fix it in existing applications? The easiest way is to fix the bug in the transaction middleware, which I expect most Django applications use, as it provides a very clean and natural transaction model:

from django.db import transaction

class TransactionMiddleware(object):
    """
    Transaction middleware. If this is enabled, each view function will be run
    with commit_on_response activated - that way a save() doesn't do a direct
    commit, the commit is done when a successful response is created. If an
    exception happens, the database is rolled back.
    """
    def process_request(self, request):
        """Enters transaction management"""
        transaction.enter_transaction_management()
        transaction.managed(True)

    def process_exception(self, request, exception):
        """Rolls back the database and leaves transaction management"""
        if transaction.is_dirty():
            transaction.rollback()
        transaction.leave_transaction_management()

    def process_response(self, request, response):
        """Commits and leaves transaction management."""
        if transaction.is_managed():
            if transaction.is_dirty():
                transaction.commit()
            transaction.leave_transaction_management()
        return response

Note that the transaction is only closed if the transaction is marked as dirty. This is the source of the problem: psycopg2 by default opens a transaction, even for read-only operations, but that transaction is never closed. The fix is simplicity itself:

from django.db import transaction

class TransactionMiddleware(object):
    def process_request(self, request):
        transaction.enter_transaction_management()
        transaction.managed(True)

    def process_exception(self, request, exception):
        transaction.rollback()
        transaction.leave_transaction_management()

    def process_response(self, request, response):
        if transaction.is_managed():
            transaction.commit()
            transaction.leave_transaction_management()
        return response

The various decorators, such as @commit_on_success, have the same issue, so you’ll need to use patched versions of those.

Another option is to use AUTOCOMMIT mode. If this is set, Django will tell psycopg2 not to issue the BEGIN, and it will be up to the application to properly manage the BEGIN and COMMIT/ROLLBACK blocks. This is the most detailed and controlled way of handling it, but it does create the possibility of unbalanced transactions.

I believe the correct answer is just for Django to always commit (on normal return) or rollback (on exception), whether or not is_dirty is set, but until and unless that patch is applied, this should greatly reduce the Idle in Transaction problems.

There are 10 comments.