Django ORM和锁定表 [英] Django ORM and locking table

问题描述

我的问题如下：

我有一个汽车经销商A，一个名为的数据库表，sold_cars 。当一辆汽车出售时，我在此表中创建条目。

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.

表有一个名为 order_no 的整数列。在经销商销售的汽车中应该是独一无二的。

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

所以如果经销商A出售汽车 a，b和c ，那么这一栏应该是 1，2，3 。我不得不使用这个列，而不是主键，因为我不想在我的数字中有任何孔 - 经销商A和B（稍后可能添加）应该有订单编号1，2，3，而不是A ：1，3，5和B：2,4,6。所以...我给给经销商选择最后一个order_no，将其递增1并保存。

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.

问题是两个人在同一毫秒内从经销商A购买汽车，两个订单都有相同的order_no。任何建议？我正在考虑在事务块中关闭此进程，并锁定此表，直到事务完成，但无法找到有关如何执行的任何信息。

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.

推荐答案

我认为这个代码段可以满足你的需要，假设你使用MySQL。如果没有，您可能需要稍微调整语法，但这个想法应该仍然可以工作。

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  

这篇关于Django ORM和锁定表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！