An option to add along to the unique constraint, if you expect to get
collisions often, is to use a SAVEPOINT so that a process can roll back
partially if this particular INSERT fails, then use the row. The Session
offers SAVEPOINT via begin_nested():
session.begin_nested()
try:
session.add(thing_that_may_exist_already)
session.commit() # flushes, and commits only the "savepoint"
except exc.IntegrityError:
session.rollback()
thing_that_may_exist_already =
session.query(Thing).filter_by(<criteiron>).one()
the difference between using locks to prevent concurrent dupes versus using
constraints and expecting dupes to fail is known as pessimistic versus
optimistic locking.
On May 28, 2012, at 10:38 AM, Jeff wrote:
> The unique constraint sounds like a workable solution! I'll implement
> that with a try/except and report back if that was effective. Thanks!
>
> On May 28, 5:43 am, Simon King <[email protected]> wrote:
>> On Sun, May 27, 2012 at 6:18 PM, Jeff <[email protected]> wrote:
>>> Thanks,
>>
>>> I have indeed spent a lot of time looking at SELECT FOR UPDATE, but as
>>> far as I can tell that locks rows that have been selected. That is not
>>> helpful in this use case, in which the issue is rows not existing, and
>>> then later existing. Am I misunderstanding?
>>
>>> On May 27, 11:48 am, "A.M." <[email protected]> wrote:
>>>> On May 27, 2012, at 1:07 AM, Jeff 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.
>>
>>>> You should look at "SELECT FOR UPDATE".
>>
>>>> http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=lockmo...
>>
>>>> Cheers,
>>>> M
>>
>> Could you put unique constraints on the table so that the second
>> process will get an error when it tries to insert the duplicate rows?
>> It won't prevent you from performing the calculations twice, but at
>> least you won't get the duplicates.
>>
>> Another option would be to write some sort of "pending" marker into
>> the table, so that subsequent processes know that the result is
>> already being calculated.
>>
>> Simon
>
> --
> 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.
>
--
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.
