While integrating celery and django-celery into a project at work recently, it gave a warning about the transaction isolation level on our database:

Polling results with transaction isolation level repeatable-read within the same transaction will may give outdated results. Be sure to commit the transaction for each poll iteration.

In the context of a library with its own daemon and polling loop, this seemed worrying. Since I didn't really know much about transaction isolation, I decided to read up a bit on it, and discovered that it's something that perhaps should get a bit more thought in the Django world, where often database knowledge is fairly limited (as mine is).

InnoDB's default transaction isolation level is REPEATABLE-READ. The MySQL documentation has a list of levels and descriptions in the help for SET TRANSACTION, but as usual it misses out on explaining the implications to performance, application correctness, or ancillary processes that a change might bring about.

For most people, "dirty reads" are going to be bad, as there's no consistency guarantees there. The default level differs from READ-COMMITTED in a key way:

All consistent reads within the same transaction read the snapshot established by the first read.

There are some strong implications for this in the way that Django uses transactions, covered at length in this blogpost and ticket #13906 (opened by the blog post's author) on Django's tracker. The basic idea is that this makes concurrent calls to Model.get_or_create() break. The strong isolation provided by REPEATABLE-READ also means that there will be more time costly locking in transaction-using write-heavy environments.

While READ-COMMITTED seems like it could be a performance boost, fixes a bug with a fairly common Django object creation idiom, it also breaks statement-based replication (MIXED replication seems to be at least safe-guarded as of 5.1.20). While MySQL now has row-based replication, RBR can have a higher cost on the network compared to SBR if you are, say, UPDATE-ing many rows.

We aren't using SBR, situations where we might want to create the same object at anywhere near the same time just do not arise, and given that I was having problems getting django-celery up and running at the time, decided to go through with it and change the transaction isolation.

This is something that can be done on most DBS with a simple SQL command, SET GLOBAL TRANSACTION ISOLATION LEVEL READ-COMMITTED, or added to the [mysql] section of my.cnf: transaction-isolation = READ-COMMITTED. If you are using Amazon RDS, it gets potentially much more complicated, and I should warn that while modifying an existing ParameterGroup applied to your RDS instance should happen live, creating a new one (so as not to modify the default group) and applying that to your instance requires an instance reboot that can take some time.

In the end, my celery issues were not due to the transaction level, but due to a mismatch in the anchor on imports between celeryd and my shell; celeryd thought project.app.tasks, and I had imported app.tasks. This would have been completely obvious, but for some logging module rerouting that obscured the problem mightily.

Oct 23 2010