Clarification: Originally, when we tried SELECT FOR UPDATE and isolation_level SERIALIZABLE unsuccessfully, we were using pyramid_tm 0.7. As soon as we upgraded to 0.8, it all started working correctly.
On Monday, December 8, 2014 6:15:26 PM UTC-6, HP3 wrote: > > Thank you all, > > It turned out that combining SELECT FOR UPDATE and isolation_level > SERIALIZABLE and pyramid_tm 0.8 produced the expected result. IOW: > everything is now working correctly: > > The only change to history_meta.py we ended up doing was the following: > > ``` > > #attr['version'] = obj.version > > session.query(obj.__class__).with_for_update().filter(obj.__class__.id > == obj.id).one() #S1 > > version_number = > session.query(obj.__class__.version).with_for_update().filter(obj.__class__.id > > == obj.id).one() #2 > > attr['version'] = version_number > > ``` > > Though we haven't tested extensively, we suspect that between 'SELECT FOR > UPDATE' S1 and S2, only S1 is needed. > > Thanks again. > > On Thursday, December 4, 2014 8:55:59 PM UTC-6, kirk holland wrote: >> >> Hi, >> >> We were having a similar problem with history_meta, so we created >> history_meta_date (.py file attached). In our version, we use timestamps >> rather than sequence numbers to track history versions and don't need to >> worry about duplicate key problems. The "version_date" timestamps still >> provide sortability on version, but are infinitely less likely to have the >> same value issued to separate processes. >> >> Disclaimer: The attached file was developed in a hurry to solve an urgent >> problem, it has performed well for us, but probably hasn't gone through the >> testing rigours that this group might expect. Feel free to use/adapt it as >> necessary and if you have the time/inclination, post a more rigorous >> version for consideration as a formal recipe. >> >> Cheers >> >> ---------- Forwarded message ---------- >> From: HP3 <henddher...@gmail.com> >> Date: 5 December 2014 at 10:36 >> Subject: [sqlalchemy] history_meta.py IntegrityError: (IntegrityError) >> duplicate key value violates unique constraint "p2docs_history_pkey" >> To: sqlal...@googlegroups.com >> >> >> Hello all, >> >> We are facing a problem when using history_meta.py recipe. >> >> It seems like two concurrent transactions read the same (id,version) >> tuple at "#1" and then each one tries to insert a new row into the >> pbases_history table with the same pk (id, version) combination (see #2) >> By removing #2, the issue goes away but of course, there is no history >> kept anywhere. >> >> >> -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
