On Jul 22, 2011, at 11:20 PM, Michael Bayer wrote: >> >> I approach the "SELECT...THEN INSERT" issue by SELECTING the full expanse of >> data I'm to work on in one big statement first, placing the results in a >> dictionary or similar using keys tailored towards the operation in progress. >> I then use the dictionary to consult for rows that already exist. >> >> Here comes the problems: I'm not the only user of the database so its state >> may have been updated inbetween. And I can't lock that much rows during the >> whole process either. > > not if you're using isolated transactions. this is the purpose of > transaction isolation. A high level of isolation will lock out concurrent > changes.
If you're dealing with INSERT and inserting conflicting keys, then scratch this line above, of course short of locking the whole table you'd need to stick with an optimistic approach to detect duplicates. 3 seconds for your ROLLBACK, where the 3 seconds is to reload everything, sounds like you are dealing with batches much too large if you have some kind of super-high volume concurrent INSERT of the same unique key type of situation going on. -- 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.
