Login

Locking tables

Author:
miohtama
Posted:
June 30, 2008
Language:
Python
Version:
.96
Score:
2 (after 2 ratings)

Sometimes you need to prevent concurrent access to update/calculate some properties right. Here is (MySQL) specific example to lock one table with new object manager functions.

 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
class LockingManager(models.Manager):
    """ Add lock/unlock functionality to manager.
    
    Example::
    
        class Job(models.Model):
        
            manager = LockingManager()
    
            counter = models.IntegerField(null=True, default=0)
    
            @staticmethod
            def do_atomic_update(job_id)
                ''' Updates job integer, keeping it below 5 '''
                try:
                    # Ensure only one HTTP request can do this update at once.
                    Job.objects.lock()
                    
                    job = Job.object.get(id=job_id)
                    # If we don't lock the tables two simultanous
                    # requests might both increase the counter
                    # going over 5
                    if job.counter < 5:
                        job.counter += 1                                        
                        job.save()
                
                finally:
                    Job.objects.unlock()
     
    
    """    

    def lock(self):
        """ Lock table. 
        
        Locks the object model table so that atomic update is possible.
        Simulatenous database access request pend until the lock is unlock()'ed.
        
        Note: If you need to lock multiple tables, you need to do lock them
        all in one SQL clause and this function is not enough. To avoid
        dead lock, all tables must be locked in the same order.
        
        See http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
        """
        cursor = connection.cursor()
        table = self.model._meta.db_table
        logger.debug("Locking table %s" % table)
        cursor.execute("LOCK TABLES %s WRITE" % table)
        row = cursor.fetchone()
        return row
        
    def unlock(self):
        """ Unlock the table. """
        cursor = connection.cursor()
        table = self.model._meta.db_table
        cursor.execute("UNLOCK TABLES")
        row = cursor.fetchone()
        return row       

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 10 months, 3 weeks ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 11 months ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 7 months ago

Comments

haplo (on September 30, 2008):

Nice manager. Having the locking type (writer, reader, both) in the manager's init may be a good feature.

I suppose the following line in the docstring

manager = LockingManager()

should be

objects = LockingManager()

#

myoffe (on November 28, 2011):

Also, add

from django.db import models, connection

at the top

#

victorbstan (on February 2, 2012):

I get an error:

DatabaseError: near "UNLOCK": syntax error

#

ryuusenshi (on January 18, 2013):

@victorbstan It's likely you're using a db engine that doesn't have the UNLOCK command. For example postgres doesn't have UNLOCK.

According to postgres' user manual "There is no UNLOCK TABLE command; locks are always released at transaction end"

#

Please login first before commenting.