Hmmm. It also appears that the 4 step solution I gave above doesn't
work consistently. Any ideas as to why not?
On May 27, 1:07 am, Jeff <jeffalst...@gmail.com> wrote:
> 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.
