Login

Django Row Level Locking (Prevents race conditions if used correctly)

Author:
sleepycal
Posted:
May 26, 2010
Language:
Python
Version:
1.2
Tags:
unlock django database operation operations mysql race condition lock table atomic locking
Score:
0 (after 2 ratings)

WARNING This is extremely slow.

This snippet allows you to easily prevent most race conditions (if used properly).

Feel free to extend on top of this as you like, I'd appreciate any comments to cal@simplicitymedialtd.co.uk

  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
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
#### *WARNING* This is *extremely* slow.

#### EXAMPLE
#### person = Person.objects.all()[0]
#### person.lock()
#### person.lock(nowait=True)
#### person.unlock()

from django.db import models
from django.db import connection, transaction
from django.db import IntegrityError
from django.db import DatabaseError
import time
import logging
import random

""" Provides table locking functionality"""
class Lock(models.Model):
    pkid = models.IntegerField(editable=False, blank=False, null=False)
    table = models.CharField(max_length=31, editable=False, blank=False, null=False)
    ts = models.FloatField(default=time.time, editable=False, blank=False, null=False)
    seed = models.FloatField(default=random.random(), editable=False, blank=False, null=False)

    class Meta:
        unique_together = (
            ('pkid', 'table')
        )

        verbose_name = "Row object lock"
        verbose_name_plural = "Row object locks"

class LockObject():
    def __init__(self, id, seed):
        self.id = id
        self.seed = seed

    def unlock(self):
        # perform clean
        clean()

        # Create cursor
        cursor = connection.cursor()

        # Overwrite timeout
        c = cursor.execute("DELETE FROM de_lock WHERE id = %s AND seed = %s", [self.id, self.seed])
        transaction.commit_unless_managed()
        if c==0:
            raise DatabaseError, (9001, "Unable to unlock object")

def clean():
    # Create cursor
    cursor = connection.cursor()
    # delete timed out locks
    c = cursor.execute("DELETE FROM de_lock WHERE (%s-`ts`)>10"%time.time())
    # commit changes
    transaction.commit_unless_managed()


def lock(self, nowait=False):
    # perform clean
    clean()
    # Store our start time
    start = time.time()

    # Grab our PKID
    if not self.id:
        raise IntegrityError, (9004, "Object has no PKID, lock aborted")

    pkid = self.id

    # Loop forever until timeout is reached
    while True:
        try:
            # Call locking method
            return _lock(self, pkid)

        except IntegrityError, e:
            # Check if we are being told not to wait
            if nowait:
                raise

            # Calculate remaining time
            remaining = (time.time()-start)

            # Check if we have been trying to do this for more than 10 seconds
            if (time.time()-start)>10:
                raise IntegrityError, "Timeout after 10 seconds whilst attempting to lock PKID (Reason: %s)"%e

            logging.info("(%s remaining) Lock attempt failed: %s"%(remaining, e))

        # Sleep for 1 second in between attempts
        time.sleep(1)

def unlock(self):
    if not hasattr(self, "_lock_object"):
        raise Exception, "Object does not appear to be locked"
    self._lock_object.unlock()

def _lock(self, pkid):
    # Create cursor
    cursor = connection.cursor()
    table = self._meta.db_table

    try:
        # Generate a new seed
        seed = random.random()
        ts=time.time()

        # Create new lock entry
        try:
            # Attempt to create the new lock
            c = cursor.execute("INSERT INTO de_lock (`pkid`, `table`, `ts`, `seed`) VALUES (%s, %s, %s, %s)", [pkid, table, ts, seed])
            transaction.commit_unless_managed()
            # Ensure we found one
            if c==0:
                raise DatabaseError, (9002, "Unable to lock object")

            logging.info("Created new lock on %s for PKID %s"%(table,pkid))
            _id = cursor.lastrowid

            # Define the lock object
            self._lock_object = LockObject(_id, seed)

        except IntegrityError, e:
            # Check if the error is 1062 (duplicate key)
            if e[0]==1062:
                # Check if we have a lock entry
                c = cursor.execute("SELECT * from de_lock WHERE pkid = %s AND `table` = %s", [pkid, table]);

                # Ensure we found one
                if c==0:
                    raise DatabaseError, (9000, "Unable to lock object")

                # Extract values
                _id, _pkid, _table, _ts, _seed = cursor.fetchone()

                # Calculate seconds until timeout
                elapsed=(time.time()-_ts)
                remaining=10-elapsed

                # Check if timeout is above 10 seconds
                if elapsed>=10:
                    # Overwrite timeout
                    logging.info("Lock timed out on %s for PKID %s (%s seconds old)"%(table, pkid, elapsed))
                    c = cursor.execute("DELETE FROM de_lock WHERE id = %s AND seed = %s", [_id, _seed])
                    transaction.commit_unless_managed()
                    if c==0:
                        raise DatabaseError, (9005, "Unable to lock object")

                    cursor.execute("INSERT INTO de_lock (`pkid`, `table`, `ts`, `seed`) VALUES (%s, %s, %s, %s)", [pkid, table, ts, seed])
                    transaction.commit_unless_managed()
                    if c==0:
                        raise DatabaseError, (9006, "Unable to create new lock")

                    # Grab the last row id
                    _id = cursor.lastrowid

                    logging.info("Created new lock on %s for PKID %s"%(table,pkid))
                    return LockObject(_id, seed)

                else:
                    # Disallow lock.
                    logging.info("Lock already exists on %s for PKID %s (Timeout in %s)"%(table, pkid, remaining))
                    raise IntegrityError, (9000, "Object is already locked")

            # Some other exception code, so raise as normal.
            else:
                raise

    except:
        raise

More like this

  1. Easy Conditional Template Tags by fragsworth 5 years, 11 months ago
  2. Locking tables by miohtama 6 years, 10 months ago
  3. Model Locking Mixin & Decorator (MySQL Advisory Locks) by pio 4 years ago
  4. lock tables decorator by seandong 6 years, 8 months ago
  5. Optimistic locking in Admin by Taifu 3 years, 9 months ago

Comments

Please login first before commenting.