python - 具有相同外键的对象的类自动增量字段(Django 1.8、MySQL 5.5)

Question

我有一个 Django 模型(我们称它为 ObjectLog)，其中许多模型通过外键与单个对象相关。请参阅下面的简要定义:

class ObjectLog(models.Model):
    class Meta:
        ordering = ['-created','-N']
        unique_together = ("object","N")
    object = models.ForeignKey(Object, null=False)                                                                                                                                                              
    created = models.DateTimeField(auto_now_add=True)
    issuer = models.ForeignKey(User)
    N = models.IntegerField(null=False)

与单个对象相关的每个 ObjectLog 都应具有唯一的 N 值(如 unique_together 要求所示)。另一种表达方式是 N 本质上应该是一个自动增量字段，但仅相对于为单个对象设置的 ObjectLog。使用自定义保存方法从逻辑上讲这不是一件难事:

def save(self, *args, **kwargs):                                                                                                                                                                        
    with transaction.atomic():
        logs = self.object.objectlog_set.select_for_update().order_by('-N')
        if logs:
            self.N = logs[0].N + 1
        else:
            self.N = 1
        super(ObjectLog, self).save(*args, **kwargs)

但是，我在处理并发时遇到了麻烦。当多个进程同时尝试为单个对象创建 ObjectLog 时，它们通常以相同的 N 值结束，从而导致错误，如“Duplicate entry\'249244-169\' for key”。我试图通过在我的 save 方法中使用 transaction.atomic 和 select_for_update 来处理这个问题，尽管我现在意识到原子性是不是真的会在这里帮助我的属性(property)。看起来我需要某种方法来锁定 ObjectLog 表中与相关对象相关的行，同时代码确定 N 应该是什么并保存新行，但我不知道该怎么做。

我正在使用 Django 1.8 和 MySQL 5.5 以及 MyISAM 引擎。我还尝试修改有问题的表以使用 InnoDB。使用 InnoDB 时，我收到类似“1213，\'尝试获取锁时发现死锁；尝试重新启动事务\'”的错误。所以看起来好像锁定在这种情况下有效，但也许我做得过头了？一个糟糕的解决方案是捕获这些错误并使用 while 循环强制重试保存方法，但我真的不想这样做。

非常感谢任何建议!如果我误用了一些术语，我深表歉意，我是这个领域的新手。如果我遗漏了一些重要信息，我很乐意提供更多信息。

Answer 1

我最终通过构造和执行原始 SQL 查询来在单个数据库事务中执行自动增量和插入来解决问题。我花了很多时间仔细研究 Django 源代码，以了解他们默认的模型保存方法是如何工作的，这样我就可以尽可能稳健地做到这一点。但是，我完全预计这将需要针对非 MySQL 后端进行修改。

首先，我创建了一个抽象类，ObjectLog 现在将派生自它，它具有这个新的保存方法:

class AutoIncrementModel(models.Model):
    """
    An abstract class used as a base for classes which need the
    autoincrementing save method described below.
    """
    class Meta:
        abstract = True

    def save(self, auto_field, auto_fk, *args, **kwargs):
        """
        Arguments:
            auto_field: name of field which acts as an autoincrement field.
            auto_fk:    name of ForeignKey to which the auto_field is relative.
        """

        # Do normal save if this is not an insert (i.e., the instance has a
        # primary key already).
        meta = self.__class__._meta
        pk_set = self._get_pk_val(meta) is not None
        if pk_set:
            super(ObjectLog, self).save(*args, **kwargs)
            return

        # Otherwise, we'll generate some raw SQL to do the
        # insert and auto-increment.

        # Get model fields, except for primary key field.
        fields = meta.local_concrete_fields
        if not pk_set:
            fields = [f for f in fields if not
                isinstance(f, models.fields.AutoField)]

        # Setup for generating base SQL query for doing an INSERT.
        query = models.sql.InsertQuery(self.__class__._base_manager.model)
        query.insert_values(fields, objs=[self])
        compiler = query.get_compiler(using=self.__class__._base_manager.db)
        compiler.return_id = meta.has_auto_field and not pk_set

        fk_name = meta.get_field(auto_fk).column
        with compiler.connection.cursor() as cursor:
            # Get base SQL query as string.
            for sql, params in compiler.as_sql():
                # compiler.as_sql() looks like:
                # INSERT INTO `table_objectlog` VALUES (%s,...,%s)
                # We modify this to do:
                # INSERT INTO `table_objectlog` SELECT %s,...,%s FROM
                # `table_objectlog` WHERE `object_id`=id
                # NOTE: it's unlikely that the following will generate
                # a functional database query for non-MySQL backends.

                # Replace VALUES (%s, %s, ..., %s) with
                # SELECT %s, %s, ..., %s
                sql = re.sub(r"VALUES \((.*)\)", r"SELECT \1", sql)

                # Add table to SELECT from and ForeignKey id corresponding to
                # our autoincrement field.
                sql += " FROM `{tbl_name}` WHERE `{fk_name}`={fk_id}".format(
                    tbl_name=meta.db_table,
                    fk_name=fk_name,
                    fk_id=getattr(self, fk_name)
                    )

                # Get index corresponding to auto_field.
                af_idx = [f.name for f in fields].index(auto_field)
                # Put this directly in the SQL. If we use parameter
                # substitution with cursor.execute, it gets quoted
                # as a literal, which causes the SQL command to fail.
                # We shouldn't have issues with SQL injection because
                # auto_field should never be a user-defined parameter.
                del params[af_idx]
                sql = re.sub(r"((%s, ){{{0}}})%s".format(af_idx),
                r"\1IFNULL(MAX({af}),0)+1", sql, 1).format(af=auto_field)

                # IFNULL(MAX({af}),0)+1 is the autoincrement SQL command,
                # {af} is substituted as the column name.

                # Execute SQL command.
                cursor.execute(sql, params)

            # Get primary key from database and set it in memory.
            if compiler.connection.features.can_return_id_from_insert:
                id = compiler.connection.ops.fetch_returned_insert_id(cursor)
            else:
                id = compiler.connection.ops.last_insert_id(cursor,
                    meta.db_table, meta.pk.column)
            self._set_pk_val(id)

            # Refresh object in memory in order to get auto_field value.
            self.refresh_from_db()

然后 ObjectLog 模型像这样使用它:

class ObjectLog(AutoIncrementModel):
    class Meta:
        ordering = ['-created','-N']
        unique_together = ("object","N")
    object = models.ForeignKey(Object, null=False)                                                                                                                                                              
    created = models.DateTimeField(auto_now_add=True)
    issuer = models.ForeignKey(User)
    N = models.IntegerField(null=False)

    def save(self, *args, **kwargs):
        # Set up to call save method of the base class (AutoIncrementModel)
        kwargs.update({'auto_field': 'N', 'auto_fk': 'event'})
        super(EventLog, self).save(*args, **kwargs)

这允许对 ObjectLog.save() 的调用仍然按预期工作。