小马ORM报告记录“已在当前交易之外更新"没有其他交易 [英] Pony ORM reports record "was updated outside of current transaction" while there is not other transaction

问题描述

代码非常简单，如下所示:

The code is quite simple, as follows:

from pony.orm import Required, Set, Optional, PrimaryKey
from pony.orm import Database, db_session
import time


db = Database('mysql', host="localhost", port=3306, user="root",
                      passwd="123456", db="learn_pony")


class TryUpdate(db.Entity):
    _table_ = "try_update_record"
    t = Required(int, default=0)

db.generate_mapping(create_tables=True)


@db_session
def insert_record():
    new_t = TryUpdate()


@db_session
def update():
    t = TryUpdate.get(id=1)
    print t.t
    t.t = 0
    print t.t


if __name__ == "__main__":
    insert_record()
    update()

pony.orm报告异常:pony.orm.core.CommitException:对象TryUpdate [1]已在当前事务之外更新.但是根本没有其他事务在运行

pony.orm reports exception: pony.orm.core.CommitException: Object TryUpdate[1] was updated outside of current transaction. But there is no other transaction running at all

正如我的实验所示，只要将t.t更改为与原始值不同的值，小马就可以正常工作，但是当t.t设置为等于原始值时，它始终会报告异常.

And as my experiments show, pony works OK as long as t.t is changed to a value different from the original, but it always reports exception when t.t is set to a value which equals to the original.

我不确定这是否是设计决定.在分配之前，我是否必须检查输入值是否每次都更改?还是有什么办法可以避免这种令人讨厌的异常?

I'm not sure if this is a design decision. Do I have to check if my input value changes everytime before the assignment? Or is there anything I can do to avoid this annoying exception?

我的小马版本:0.4.8

my pony version: 0.4.8

非常感谢~~~

推荐答案

小马ORM作者在这里.

Pony ORM author is here.

此行为是一个MySQL特定的错误，已在Pony ORM 0.4.9版本中修复，因此请升级.我剩下的答案就是导致错误的原因的解释.

This behavior is a MySQL-specific bug which was fixed in release Pony ORM 0.4.9, so please upgrade. The rest of my answer is the explanation of what caused the bug.

此错误的原因如下.为了防止更新丢失，Pony ORM使用了乐观检查.小马跟踪在程序执行期间读取或更改了哪些属性，然后在相应的UPDATE查询的WHERE部分中添加了额外的条件.这样，Pony保证不会由于并发更新而丢失任何数据.让我们考虑下一个示例:

The reason for this bug is the following. In order to prevent lost updates, Pony ORM uses optimistic checks. Pony tracks which attributes were read or changed during the program execution and then adds extra conditions in the WHERE section of the corresponding UPDATE query. This way Pony guarantees that no data will be lost because of the concurrent update. Lets consider the next example:

@db_session
def some_function()
   obj = MyObject[123]
   print obj.x
   obj.x = 100

在some_function退出时，@db_session装饰器将提交正在进行的事务.在提交之前，将通过以下UPDATE命令保存对象的数据:

Upon exit of the some_function the @db_session decorator will commit ongoing transaction. Right before the commit, the object's data will be saved by the following UPDATE command:

UPDATE MyTable
SET x = <new_value>
WHERE id = 123 and x = <old_value>

您可能想知道，为什么添加了此附加条件and x = <old_value>?这是因为Pony知道程序看到了属性x的先前值，并且可以使用该值来计算同一属性的新值.因此，Pony采取步骤以确保在UPDATE时刻此属性仍保持不变.这种方法称为乐观并发检查" (另请参阅Wikipedia文章).由于大多数数据库默认情况下使用的隔离级别不是SERIALIZABLE，因此如果不进行此附加检查，则其他某些事务可能会在事务提交之前设法更新x属性的值，然后再更新并发写入的值.交易将丢失.

You may wonder, why this additional condition and x = <old_value> was added? This is because Pony knows that the program saw previous value of the attribute x and may use this value in order to calculate new value of the same attribute. So Pony takes steps to guarantee that this attribute is still unchanged at the moment of the UPDATE. This approach is called "optimistic concurrency check" (see also Wikipedia article "optimistic concurrency control"). Since isolation level used by default in most databases is not SERIALIZABLE, without this additional check it is possible that some other transaction have managed to update value of the x attribute before our transaction commit, and then the value written by the concurrent transaction will be lost.

当Python数据库驱动程序执行UPDATE查询时，它返回满足UPDATE条件的行数.通过这种方式，小马知道更新是否成功.如果结果为1，则表示已成功找到并更新了一行，但如果结果为0，则表示该行已被另一事务修改，现在不满足WHERE部分中的条件. .当发生这种情况时，Pony会终止当前事务，以防止丢失更新.

When Python database driver executes the UPDATE query, it returns the number of rows which satisfy the UPDATE criteria. This way Pony knows if the update was successful or not. If the result is 1, this means that one row was successfully found and updated, but if the result is 0, this means that the row was already modified by another transaction and now it doesn't satisfy the criteria in the WHERE section. When this happens Pony terminates the current transaction in order to prevent lost update.

该错误的原因是，尽管所有其他数据库驱动程序都返回通过WHERE部分条件找到的行数，但是MySQLdb驱动程序默认情况下会返回实际被修改的行数！因此，如果该属性的新值与相同属性的原始值相同，则MySQLdb报告修改了0行，而Pony(版本0.4.9之前)错误地认为这意味着该行已被并发事务修改.从0.4.9版本开始，Pony ORM告诉MySQLdb驱动程序以标准方式运行，并返回找到的行数，而不是实际更新的行数.

The reason of the bug is that while all other database drivers return number of rows which were found by WHERE section criteria, MySQLdb driver by default returns the number of rows which were actually modified! Because of this, if the new value of the attribute turns out to be the same as the original value of the same attribute, MySQLdb reports that 0 rows were modified, and Pony (prior to the release 0.4.9) mistakenly believes that it means that the row was modified by a concurrent transaction. Started with the release 0.4.9 Pony ORM tells MySQLdb driver to behave in a standard way and return the number of rows which were found and not the number of rows which were actually updated.

希望这会有所帮助:)

P.S.我发现您是一个偶然的问题，为了可靠地获得有关Pony ORM的答案，我建议您将问题发送到我们的邮件列表 http://ponyorm-list.ponyorm.com .如果您认为发现了一个错误，则可以在此处打开问题: https://github.com/ponyorm/pony /问题. 谢谢您的提问！

P.S. I found you question just by chance, in order to reliably get answers about Pony ORM I recommend you to send questions to our mailing list http://ponyorm-list.ponyorm.com. If you think that you found a bug you can open issue here: https://github.com/ponyorm/pony/issues. Thank you for your question!

这篇关于小马ORM报告记录“已在当前交易之外更新"没有其他交易的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！