Yes, I understand, thank you for your answer. I was hoping that I could find something more pythonic to do the table locking - I know that it isn't quite portable and that's why there isn't something more code oriented, but I didn't want to get to issue a LOCK table <> for this.
Thanks. Kyle Schaffrick wrote: > 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 > > > > > ____________________________________________________________________________________ > Click here to become certified in medical billing and training at these > schools. > http://ads.lavabit.com/fc/Ioyw6kdeEaSzjSWNjKwc4O0fXNjfbcf7uVQWxA2MYuW88QaqyfdO6x/ > ____________________________________________________________________________________ > --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
