How do I deal with this race condition in django?

Question

This code is supposed to get or create an object and update it if necessary. The code is in production use on a website.

In some cases - when the database is busy - it will throw the exception "DoesNotExist: MyObj matching query does not exist".

# Model:
class MyObj(models.Model):
    thing = models.ForeignKey(Thing)
    owner = models.ForeignKey(User)
    state = models.BooleanField()
    class Meta:
        unique_together = (('thing', 'owner'),)

# Update or create myobj
@transaction.commit_on_success
def create_or_update_myobj(owner, thing, state)
    try:
        myobj, created = MyObj.objects.get_or_create(owner=user,thing=thing)

    except IntegrityError:
        myobj = MyObj.objects.get(owner=user,thing=thing)
        # Will sometimes throw "DoesNotExist: MyObj matching query does not exist"

    myobj.state = state
    myobj.save()

I use an innodb mysql database on ubuntu.

How do I safely deal with this problem?

Answer 1

Your exception handling is masking the error. You should pass a value for state in get_or_create(), or set a default in the model and database.

Answer 2

One (dumb) way might be to catch the error and simply retry once or twice after waiting a small amount of time. I'm not a DB expert, so there might be a signaling solution.

Answer 3

This could be off-shoot of the same problem as here:

http://stackoverflow.com/questions/2221247/why-doesnt-this-loop-display-an-updated-object-count-every-five-seconds/2221400

Basically get_or_create can fail - if you take a look at it's source, the you'll see that it's: get, if-problem: save+some_trickery, if-still-problem: get again, if-still-problem: surrender and raise.

This means that if there are two simultaneous threads (or processes) running create_or_update_myobj, both trying to get_or_create the same object, then:

• first thread tries to get it - but it doesn't yet exists,
• so, it tries to create it, but before the object is created...
• ...second thread tries to get it - and it obviously fail
• now, because of default AUTOCOMMIT=OFF for MySQLdb database connection, and REPEATABLE READ serializable level, both threads have frozen their views of MyObj table.
• subsequently, first thread creates it's object and returns it gracefully, but...
• ...second thread cannot create anything as it would violate unique constraint
• what's funny, subsequent get on the second thread doesn't see the object created in first thread, due to frozen view of MyObj table

So, if you want to safely get_or_create anything, try something like this:

 @transaction.commit_on_success
 def my_get_or_create(...):
     try:
         obj = MyObj.objects.create(...)
     except IntegrityError:
         transaction.commit()
         obj = MyObj.objects.get(...)
     return obj

Edited on 27/05/2010

There is also second solution to the problem - using READ COMMITED isolation level, instead of REPEATABLE READ. But it's less tested (at least in MySQL), so there might be more bugs/problems with it - but at least it allows to tie view processing to transaction, without commiting in the middle.