Recently, I worked on a project using PonyORM. I read the document, which says that PonyORM enables optimistic concurrent control by default. However, when the project was almost finished, I found the optimistic concurrent control mechanism very wired. It doesn’t work at first until I commit the changes and update again.

Here is my scenario. My database is Oracle 19c, using PonyORM 0.7.19.

from pony.orm import *

db = Database()

class Demo(db.Entity):
    item_no = Optional(str, 10, optimistic=True)
    version = Optional(str, 2, optimistic=True)

db.bind(provider='oracle',user='gcs1',password='gcs1',dsn='192.168.80.106:1521/ammiceng')
db.generate_mapping(create_tables=True)

if __name__ == '__main__':
    with db_session(optimistic=True):
        # First I create a record with the Demo.
        # Demo(item_no=123, version='1')
        # Then I used the following code to test.
        set_sql_debug(True)
        demo:Demo = Demo[1]
        demo.version = '1'

and I checked the SQL pony generated and found the field updated directly, without any optimistic concurrent control. In my opinion, all of the fields should be as the update condition appended in the where clause of the SQL, but I got the following result. the filed just was updated with the primary key.

GET CONNECTION
SELECT "ID", "ITEM_NO", "VERSION"
FROM "DEMO"
WHERE "ID" = :p1
{'p1':1}

UPDATE "DEMO"
SET "VERSION" = :p1
WHERE "ID" = :p2
{'p1':'1', 'p2':1}

COMMIT
RELEASE CONNECTION

Furthermore, I create the breakpoint at demo.version = '1' and ran it with debug mode. when the running was broken, we could see that the record had been loaded from the database.

I updated the value of the version manually to another value

and resumed the process broken previously

I got the same SQL command which updated the value of the version field to 1.
Here is the evidence

Until now, the optimistic concurrent control hasn’t worked, and the value updated previously has been lost.

But the interesting thing is that I followed the following operations, the optimistic concurrent control works.

and I changed the value in the back-end like before to another value.

then when I resumed the process, the pony threw the OptimisticCheckError exception, it worked.

Could anyone help me solve this issue? or how to enable PonyORM optimistic concurrent control correctly.

Thanks a lot