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
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
article = Article.objects.raw("SELECT * FROM articles WHERE id = 123 FOR UPDATE")
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") 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.
Achieving row level locks in Django is simple:
- Make sure that your tables use InnoDB engine.
- Acquire locks inside of transaction.
- Make sure that you are not creating new transactions inside of another transaction which holds the lock.
Hey there! I'm working on a new goal management software. Check out Simple OKR — The easiest way to manage OKRs.
I'm working on a new web app for managing goals with objectives and key results. Check it out it's awesome.
Bluebook - API Testing for Developers
API, end-to-end, and integration testing made simple.
Hut for macOS
Design and prototype web APIs and services.