Pony ORM reports record “was updated outside of cu

Question

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 reports exception: pony.orm.core.CommitException: Object TryUpdate[1] was updated outside of current transaction. But there is no other transaction running at all

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?

my pony version: 0.4.8

Thansk a lot~~~

Answer 1

Pony ORM author is here.

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.

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

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>

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.

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.

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.

Hope this helps :)

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!