Re: [sqlalchemy] Exclusive SELECT?
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.
Re: [sqlalchemy] Exclusive SELECT?
Hi M,
Thanks very much for your help. Adding .with_lockmode('update') to my
session.query statement worked like a charm!
Now I just need to figure out how to catch exceptions that occur in the work
unit in the thread, but that's a topic for another list...
Cheers,
Demitri
--
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.
[sqlalchemy] Exclusive SELECT?
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. Cheers, Demitri -- 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.
