I have multiple processes accessing a table. All of these processes
want to read a set of rows from the table, and if the rows are not
present they will make a calculation and insert the rows themselves.
The issue comes where process A does a query to see if the target set
of rows is present in the table, and they're not, and then another
starts calculating. While it's calculating, process B inserts the
rows. Then process A inserts the rows, and now we have two copies of
these sets of rows. Bad.
The current solution is to have a process:
1. Lock the table with session.execute("LOCK TABLES table WRITE")
2. Query
3. If need be, calculate and commit
4. Unlock with session.execute("UNLOCK TABLES")
Then if another process wants to query while the calculation is
happening, it will have to wait until the table unlocks, and it will
see the result of the commit. The wait time of the locked table is
acceptable.
That should work, but is ugly and, worse, requires explicit MySQL
code. I understand this could lead to dependency on the backend
implementation (ie. If this code was ever used on an sqlite
database?). Regardless, I'd like to do this completely within
sqlalchemy. Is there a way to do this? It seems like there might be a
way to do this with transactions, but I can't parse it. Thoughts?
Thanks!
--
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.
