[Answered ]-Django: Insert new row with 'order' value of the next highest value avoiding race condition

by

2👍

✅

For concurrency problems in Django & relational databases, you could write table lock to achieve atomic transactions. I came across this problem and found this great code snippet from http://shiningpanda.com/mysql-table-lock-django.html. I’m not sure if copy/pasting his code directly here would be offend anybody, but since SO discourage link-only answers, I will cite it anyway(Thanks to ShiningPanda.com for this):

#-*- coding: utf-8 -*-
import contextlib

from django.db import connection


@contextlib.contextmanager
def acquire_table_lock(read, write):
    '''Acquire read & write locks on tables.

    Usage example:
    from polls.models import Poll, Choice
    with acquire_table_lock(read=[Poll], write=[Choice]):
        pass
    '''
    cursor = lock_table(read, write)
    try:
        yield cursor
    finally:
        unlock_table(cursor)


def lock_table(read, write):
    '''Acquire read & write locks on tables.'''
    # MySQL
    if connection.settings_dict['ENGINE'] == 'django.db.backends.mysql':
        # Get the actual table names
        write_tables = [model._meta.db_table for model in write]
        read_tables = [model._meta.db_table for model in read]
        # Statements
        write_statement = ', '.join(['%s WRITE' % table for table in write_tables])
        read_statement = ', '.join(['%s READ' % table for table in read_tables])
        statement = 'LOCK TABLES %s' % ', '.join([write_statement, read_statement])
        # Acquire the lock
        cursor = connection.cursor()
        cursor.execute(statement)
        return cursor
    # Other databases: not supported
    else:
        raise Exception('This backend is not supported: %s' %
                        connection.settings_dict['ENGINE'])


def unlock_table(cursor):
    '''Release all acquired locks.'''
    # MySQL
    if connection.settings_dict['ENGINE'] == 'django.db.backends.mysql':
        cursor.execute("UNLOCK TABLES")
    # Other databases: not supported
    else:
        raise Exception('This backend is not supported: %s' %
                        connection.settings_dict['ENGINE'])

It works with the models declared in your django application, by
simply providing two lists:

the list of models to lock for read purposes, and the list of models
to lock for write purposes. For instance, using django tutorial’s
models, you would just call the context manager like this:

with acquire_table_lock(read=[models.Poll], write=[models.Choice]):
    # Do something here
    pass

It basically creates a python context manager to wrap your insert your ORM statement and do LOCK TABLES UNLOCK TALBES upon entering and exiting the context.

👤Shang Wang

Leave a comment