« Parsing Microseconds in a Django Form
Testing Django Views for Concurrency Issues »

Explicit Table Locking with PostgreSQL and Django

May 26th, 2009 by Colin Copeland

By default, Django doesn’t do explicit table locking. This is OK for most read-heavy scenarios, but sometimes you need guaranteed, exclusive access to the data. Caktus uses PostgreSQL in most of our production environments, so we can use the various lock modes it provides to control concurrent access to the data. Once we obtain a lock in PostgreSQL, it is held for the remainder of the current transaction. Django provides transaction management, so all we need to do is execute a SQL LOCK statement within a transaction, and Django and PostgreSQL will handle the rest.

Below is an example decorator we came up with to provide easy table-locking access in Django:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

from django.db import transaction
 
LOCK_MODES = (
    'ACCESS SHARE',
    'ROW SHARE',
    'ROW EXCLUSIVE',
    'SHARE UPDATE EXCLUSIVE',
    'SHARE',
    'SHARE ROW EXCLUSIVE',
    'EXCLUSIVE',
    'ACCESS EXCLUSIVE',
)
 
def require_lock(model, lock):
    """
    Decorator for PostgreSQL's table-level lock functionality
 
    Example:
        @transaction.commit_on_success
        @require_lock(MyModel, 'ACCESS EXCLUSIVE')
        def myview(request)
            ...
 
    PostgreSQL's LOCK Documentation:
    http://www.postgresql.org/docs/8.3/interactive/sql-lock.html
    """
    def require_lock_decorator(view_func):
        def wrapper(*args, **kwargs):
            if lock not in LOCK_MODES:
                raise ValueError('%s is not a PostgreSQL supported lock mode.')
            from django.db import connection
            cursor = connection.cursor()
            cursor.execute(
                'LOCK TABLE %s IN %s MODE' % (model._meta.db_table, lock)
            )
            return view_func(*args, **kwargs)
        return wrapper
    return require_lock_decorator

This is, by no means, a perfect solution. Feel free to comment below.

This entry was posted on Tuesday, May 26th, 2009 at 2:59 pm. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

6 Responses to “Explicit Table Locking with PostgreSQL and Django”

  1. Caktus Blog » Blog Archive » Testing Django Views for Concurrency Issues Says:
    May 26th, 2009 at 3:59 pm

    [...] Blog « Explicit Table Locking with PostgreSQL and Django [...]

  2. Chirayu Patel Says:
    May 28th, 2009 at 1:52 am

    A very useful function. In a similar piece of code, I have also added support for timeout (implemented using time.sleep(random.randint (1, timeout)) and NOWAIT). It helps in identifying lockup problems both in testing and production.

  3. Graham Fawcett Says:
    May 29th, 2009 at 12:57 pm

    Excellent recipe! Thank you.

    One suggestion: consider changing the signature to require_lock(lock, *models) and execute the LOCK TABLE commands once for each of the models given. This would still work for a single-table lock, but would also make it easy to specify multiple tables in a trickier situation. (It would also help to enforce locking-order by documenting it in one place, which might help to avoid deadlocks.)

  4. Joe Joe Says:
    September 28th, 2009 at 7:38 pm

    Shouldn’t you add a call to transaction.set_dirty() after the cursor.execute() call? What if the transaction exists entirely of SELECTs(which won’t internally call set_dirty)? Will the lock still be properly released?

    http://docs.djangoproject.com/en/dev/topics/db/sql/#id1

  5. tobias Says:
    September 29th, 2009 at 5:54 pm

    The PostgreSQL docs say “locks are always released at transaction end”, which I assume includes ROLLBACKs and connection closures. Try creating a test case for this one and let us know what you find!

  6. Angelo Dell'Aera Says:
    November 16th, 2009 at 6:11 am

    Nice code. I was thinking about making it really generic. Think about this idea. You import settings.py thus being able to read DATABASE_ENGINE. Then you define LOCK_MODES this way

    LOCK_MODES = {’postgresql_psycopg2′ : POSTGRESQL_LOCK_MODES,
    ‘mysql’ : MYSQL_LOCK_MODES,
    …}

    (with POSTGRESQL_LOCK_MODES being the same tuple as your LOCK_MODES)

    In wrapper then

    def wrapper(*args, **kwargs):
    if DATABASE_ENGINE not in LOCK_MODES.keys():
    raise …
    if lock not in LOCK_MODES[DATABASE_ENGINE]:
    raise …
    if DATABASE_ENGINE == “postgresql_psycopg2″:
    postgresql_psycopg2_lock_table(table, lock)
    …
    return view_func(*args, **kwargs)

Leave a Reply