Row Level Locking in Django

In one of my fixes that I was working at work I had to implement row level locking in Django. Current stable, 1.3, version of Django does not have built-in capability for row level locking on InnoDB tables. The good news are that the development version already has an update in QuerySet API that will let you use select_for_update method to acquire a write lock on rows matching your query. If you can use development version for your project you may stop reading and go upgrade Django, otherwise I will see you at the bottom of the page.

Locking Your Models

Since you're building your web application with Django it's most likely that you are using Django ORM to access database. However, in order to be able to perform locking queries we will have to write SQL queries ourselves. Which is much easier than you might think (unless you need some complex join across million tables). Assuming you have Article model you acquire row level lock as follows:

article = Article.objects.raw("SELECT * FROM articles WHERE id = 123 FOR UPDATE")[0]

When running the above query an instance of your Django web application will remain blocked until the instance that acquired the lock first releases it or lock wait timeout occurs. One thing to note here is that locking will only work if your code is executing inside of a transaction. Ultimately, you want to have something like this:

from django.db import transaction
from MySQLdb import OperationalError

def hack_article(article):
    # Some magical code that does something with the article.
    pass

@transaction.commit_on_success
def function():
    try:
        article = Article.objects.raw("SELECT * FROM articles WHERE id = 123 "
                                      "FOR UPDATE")[0]
    except IndexError:
        # Handle not found
        return
    except OperationalError:
        # Handle lock timeouts
        return

    hack_article(article)

The write lock will get released once the transaction is committed or aborted, in the above case this happens once we leave the function. There is actually one case (maybe there are more, let me know if I am wrong) where the lock can get released before the transaction is committed or aborted -- when a new transaction is started inside of another transaction. So, the above example would be prone to race conditions if our hack_article function looked like this:

@transaction.commit_on_success
def hack_article(article):
    # Some magical code that does something with the article.
    pass

The reason for this is that locks don't propagate through nested transactions in MySQL.

Wrapping up

Row level locking in Django is easy as 1, 2 and 3 :D

1. Make sure that your tables use InnoDB engine.
2. Acquire locks inside of transaction.
3. Make sure that you are not creating new transactions inside of another transaction which holds the lock.

That's all.