django mysql行锁定,Django ORM和锁定表

My problem is as follows:

I have a car dealer A, and a db table named sold_cars. When a car is being sold I create entry in this table.

Table has an integer column named order_no. It should be unique within cars sold by dealer.

So if dealer A sold cars a, b and c, then this column should be 1, 2, 3. I have to use this column, and not a primary key because I don't want to have any holes in my numeration - dealer A and B (which might be added later) should have order numbers 1, 2, 3, and not A: 1, 3, 5, and B: 2, 4, 6. So... I select last greatest order_no for given dealer, increment it by 1 and save.

Problem is that two people bought car from dealer A in the same millisecond and both orders got the same order_no. Any advice? I was thinking of closing this process in a transaction block, and locking this table until the transaction is complete, but can't find any info on how to to that.

解决方案

I think this code snippet meets your need, assuming you are using MySQL. If not, you may need to tweak the syntax a little, but the idea should still work.

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