On Mon, 6 Oct 2008 11:24:00 -0700 (PDT) Michael Bayer <[EMAIL PROTECTED]> wrote:
> > On Oct 6, 2:19 pm, coder_gus <[EMAIL PROTECTED]> wrote: > > Hi, I am writing an application server using twisted and sqlalchemy. > > > > On the server - database relation I use a pool of threads each with > > its own database connection, session etc. > > > > The problem is that I have 2 tables (one with products and one with > > events that might happen to a product) and I think I might have > > concurrency problems with them (i.e: when selecting a chunk of 50 > > products from the first table, I need to know for certain that > > another thread is not inserting the same event in the second table > > as I am doing with the already selected chunk from the first table). > > > > I am using a transactional, autoflushing session. I was thinking > > about table locking but, it doesn't seem like a good option. > > > > Any advices? > > Thanks. > > you might want to use a SELECT....FOR UPDATE so that the selected rows > are locked for the duration of that transaction. > Hmm, as I read it the OP wants to protect concurrent insertions, two writers attempting to write duplicate rows, in which case I don't think SELECT FOR UPDATE would help. Doesn't it only lock the selected rows? If that's the case, then this class of concurrency hazard seems to be at it's heart a consistency hazard. I think the easiest way to deal with it would be if you can find a way to describe your notion of "duplicate event" to the DBMS you're using with e.g. UNIQUE or CHECK constraints, such that the DBMS itself can detect attempts to insert duplicate events and reject them as constraint violations. If you can find a way to do that, DBMS's with optimistic concurrency control (the ones where this race could potentially occur) will automatically handle the race condition, arbitrating between two transactions that are attempting to write the same event (thus causing a constraint violation) by aborting one of them. When this happens, SA throw an exception from the commit() call that you can optionally catch in your Python code. If you *can't* find a way to do that, then I think you're probably right in that you'll have to resort to table-locking. Ick :) Hope that makes some sense. -Kyle --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
