此代码应该获取或创建一个对象,并在必要时更新它.该代码在网站上用于生产.
This code is supposed to get or create an object and update it if necessary. The code is in production use on a website.
在某些情况下 - 当数据库繁忙时 - 它会抛出异常DoesNotExist:MyObj 匹配查询不存在".
In some cases - when the database is busy - it will throw the exception "DoesNotExist: MyObj matching query does not exist".
# Model: class MyObj(models.Model): thing = models.ForeignKey(Thing) owner = models.ForeignKey(User) state = models.BooleanField() class Meta: unique_together = (('thing', 'owner'),) # Update or create myobj @transactionmit_on_success def create_or_update_myobj(owner, thing, state) try: myobj, created = MyObj.objects.get_or_create(owner=user,thing=thing) except IntegrityError: myobj = MyObj.objects.get(owner=user,thing=thing) # Will sometimes throw "DoesNotExist: MyObj matching query does not exist" myobj.state = state myobj.save()我在 ubuntu 上使用 innodb mysql 数据库.
I use an innodb mysql database on ubuntu.
我该如何安全地处理这个问题?
How do I safely deal with this problem?
推荐答案这可能是与此处相同问题的分支:
This could be an off-shoot of the same problem as here:
为什么不这个循环每五秒显示一个更新的对象计数?
基本上get_or_create 可能会失败 - 如果你看一下它的来源,你会发现它是:get, if-problem: save+some_trickery, if-still-problem: get again, if-still-problem:投降和提高.
Basically get_or_create can fail - if you take a look at its source, there you'll see that it's: get, if-problem: save+some_trickery, if-still-problem: get again, if-still-problem: surrender and raise.
这意味着如果有两个同时运行的线程(或进程)create_or_update_myobj,都试图 get_or_create 同一个对象,那么:
This means that if there are two simultaneous threads (or processes) running create_or_update_myobj, both trying to get_or_create the same object, then:
- 第一个线程试图获取它 - 但它还不存在,
- 因此,线程尝试创建它,但在创建对象之前...
- ...第二个线程试图得到它 - 这显然失败了
- 现在,由于 MySQLdb 数据库连接的默认 AUTOCOMMIT=OFF 和 REPEATABLE READ 可序列化级别,两个线程都冻结了它们对 MyObj 表的视图.
- 随后,第一个线程创建它的对象并优雅地返回它,但是...
- ...第二个线程不能创建任何东西,因为它会违反unique 约束
- 有趣的是,由于 MyObj 表的冻结视图,第二个线程上的后续 get 没有看到第一个线程中创建的对象
- first thread tries to get it - but it doesn't yet exist,
- so, the thread tries to create it, but before the object is created...
- ...second thread tries to get it - and this obviously fails
- now, because of the default AUTOCOMMIT=OFF for MySQLdb database connection, and REPEATABLE READ serializable level, both threads have frozen their views of MyObj table.
- subsequently, first thread creates its object and returns it gracefully, but...
- ...second thread cannot create anything as it would violate unique constraint
- what's funny, subsequent get on the second thread doesn't see the object created in the first thread, due to the frozen view of MyObj table
因此,如果您想安全地get_or_create 任何东西,请尝试以下操作:
So, if you want to safely get_or_create anything, try something like this:
@transactionmit_on_success def my_get_or_create(...): try: obj = MyObj.objects.create(...) except IntegrityError: transactionmit() obj = MyObj.objects.get(...) return obj于 27/05/2010 编辑
该问题还有第二种解决方案——使用 READ COMMITED 隔离级别,而不是 REPEATABLE READ.但它的测试较少(至少在 MySQL 中),因此它可能存在更多错误/问题 - 但至少它允许将视图绑定到事务,而无需在中间提交.
Edited on 27/05/2010
There is also a second solution to the problem - using READ COMMITED isolation level, instead of REPEATABLE READ. But it's less tested (at least in MySQL), so there might be more bugs/problems with it - but at least it allows tying views to transactions, without committing in the middle.
这里有一些关于 MySQL 和 Django 的好博文(不是我的),与这个问题相关:
Here are some good blog posts (not mine) about MySQL and Django, related to this question:
www.no-ack/2010/07/mysql-transactions-and-django.html
www.no-ack/2011/05/broken-transaction-management-in-mysql.html
更多推荐
我如何处理 Django 中的这种竞争条件?
发布评论