On Jun 1, 2011, at 12:56 AM, thatsanicehatyouh...@mac.com wrote: > Hi, > > I'm working on a script using SQLAlchemy against a PostgreSQL database and > using Python's multiprocessing. The pattern is for each thread to: > > - start a transaction (session.begin()) > - retrieve the "next row in table X that has not yet been processed" > - set a "being_processed" flag in the row so no other query will return it > - close the transaction (session.commit()) > > The rest of the thread then performs the work, saves the results back, and > cleans up. This is all working well except for one thing - multiple threads > are retrieving the same row. My first solution was to pull the work above > into the main thread, but this won't work as I'd like to run this same script > on *multiple* machines - the database needs to be the gatekeeper. > > Does anyone have any suggestions on how I can make a database-wide exclusive > transaction through SQLAlchemy (or otherwise!)? I'm using SQLAlchemy 0.6.7.
Hi Demitri, The problem is that the flag is set in a transaction A, so transaction B is not able to see the change in state until A is committed (in read-committed mode, the default), thus creating a race condition. You need to lock the table or use another form of synchronization to make sure every connection sees a synchronized view of the table. http://www.postgresql.org/docs/9.0/interactive/sql-lock.html http://www.postgresql.org/docs/9.0/interactive/sql-select.html#SQL-FOR-UPDATE-SHARE Cheers, M -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
