right way to catch a race condition with Django ORM + MySQL/InnoDB

Question

One part of my application has a race condition where multiple threads could end up creating the same persistent object. So I have implemented code that looks like this:

from foobar.models import Bar

def testomatic(request):
    bar = None
    tries = 0
    while not bar:
        try:
            bar = Bar.objects.get(b=2)

        except Bar.DoesNotExist:
            time.sleep(10) # put in here to force the race condition
            bar = Bar(a = 2, b = 2)
            try:
                bar.save()
            except IntegrityError:
                tries += 1
                bar = None

            if tries > 1:
               raise Exception("something bad happened")

    return HttpResponse("tries = %s" % tries)

And this works OK in my test environment -- but when I run this with "real" classes, it seems to spin, repeatedly getting nothing from the get(), but always getting an IntegrityError on the save().

I'm using MySQL/InnoDB as a database engine. Is there something about the MVCC handling that is preventing me from getting a record, but giving me a duplicate key error when I try to create it?

Answer 1

The correct way to do this is to drop to raw SQL so that you can lock the table when doing the initial SELECT, and then creating and saving the object if it does not exist, then unlocking the table.

Answer 2

looks like this is a duplicate of http://stackoverflow.com/questions/2235318/how-do-i-deal-with-this-race-condition-in-django

and the answer is there