具有相同外键的对象的自动增量类字段(Django 1.8，MySQL 5.5) [英] Autoincrement-like field for objects with the same foreign key (Django 1.8, MySQL 5.5)
问题描述
我有一个Django模型(我们称之为ObjectLog)，其中许多通过外键与单个Object相关.参见下面的简短定义:
I've got a Django model (let's call it ObjectLog), many of which are related to a single Object by a foreign key. See below for a brief definition:
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设置而言.使用自定义保存方法在逻辑上完成这并非难事:
Each ObjectLog which is related to a single Object should have a unique N value (as shown by the unique_together requirement). Another way of putting this is that N should essentially be an autoincrement field, but only relative to the ObjectLog set for an individual Object. This is not a difficult thing to accomplish logically with a custom save method:
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)
但是，我在处理并发方面遇到麻烦.当多个进程同时尝试为单个对象创建ObjectLogs时，它们通常以相同的N值结束，从而导致出现诸如键重复条目\'249244-169 \'"的错误.我已经尝试在我的save方法中使用transaction.atomic和select_for_update来解决这个问题，尽管我现在意识到原子性并不是真正可以在这里帮助我的属性.看起来我需要某种方式来锁定与所讨论的对象相关的ObjectLog表中的行，同时代码确定N应该是什么并保存新行，但是我不知道该怎么做.
However, I'm having trouble dealing with concurrency. When multiple processes are simultaneously trying to create ObjectLogs for a single Object, they often end up with the same value of N, resulting in errors like "Duplicate entry \'249244-169\' for key". I've tried to deal with this by using transaction.atomic and select_for_update in my save method, although I'm now realizing that atomicity is not really the property that will help me here. It looks like I need some way of locking the rows in the ObjectLog table related to the Object in question while the code determines what N should be and saves the new row, but I can't figure out how to do this.
我正在将Django 1.8和MySQL 5.5与MyISAM引擎一起使用.我也尝试过修改有问题的表以使用InnoDB.使用InnoDB时，出现类似"1213，\'试图获取锁时发现死锁；尝试重新启动事务\'"的错误.因此，似乎锁定在这种情况下仍然有效，但也许我做得太过分了?一个讨厌的解决方案是捕获这些错误并使用while循环强制save方法重试，但是我真的不愿意.
I'm using Django 1.8 and MySQL 5.5 with a MyISAM engine. I've also tried modifying the table in question to use InnoDB. When using InnoDB, I get errors like "1213, \'Deadlock found when trying to get lock; try restarting transaction\'". So it seems as though locking is working in this scenario, but maybe I'm overdoing it? A nasty solution would be to catch these errors and use a while loop to force the save method to retry, but I'd really rather not.
任何建议都将不胜感激！抱歉，如果我误用了一些术语，那么我是新手.如果我错过了重要的事情，我很乐意提供更多信息.
Any suggestions are much appreciated! Apologies if I've misused some terminology, I'm fairly new to the scene. I'm happy to provide more information if I've missed something of importance.
推荐答案
我最终通过构造和执行原始SQL查询以在单个数据库事务中进行自动增量和插入来解决了该问题.我花了很多时间浏览Django源代码，以了解其默认模型保存方法的工作方式，以便我可以尽可能地稳健地执行此操作.但是，我完全希望非MySQL后端需要对此进行修改.
I ended up solving the problem by constructing and executing a raw SQL query to do the autoincrement and insertion in a single database transaction. I spent a lot of time poring through the Django source code to understand how their default model save method works so that I could do this as robustly as possible. However, I fully expect that this will need to be modified for non-MySQL backends.
首先，我创建了一个抽象类，ObjectLog现在将派生该类，该类具有此新的保存方法:
First, I created an abstract class which ObjectLog will now derive from, which features this new save method:
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模型使用如下代码:
Then the ObjectLog model uses this like:
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()的调用仍能按预期工作.
This allows calls to ObjectLog.save() to still work as expected.
这篇关于具有相同外键的对象的自动增量类字段(Django 1.8，MySQL 5.5)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
