How to lock using sqlalchemy
mc
I need to lock a table for WRITE.
The scenario is that I have a SELECT followed by an INSERT, where the
INSERT depends on the outcome of the SELECT. I have to make sure that
no other process is modifying the table after my SELECT but before my
INSERT.
1) How do i do it via SQLAlchemy ?
2) Is there any other method (not via locks) to achieve my goal?
TIA
Andreas Jung
You might check the docs and the release notes.
-aj
> --~--~---------~--~----~------------~-------~--~----~
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group. To post to this group, send email to
> sqlal...@googlegroups.com
> To unsubscribe from this group, send email to
> sqlalchemy-...@googlegroups.com For more options, visit this
> group at http://groups.google.com/group/sqlalchemy?hl=en
> -~----------~----~----~----~------~----~------~--~---
--
ZOPYX Ltd. & Co. KG - Charlottenstr. 37/1 - 72070 Tübingen - Germany
Web: www.zopyx.com - Email: in...@zopyx.com - Phone +49 - 7071 - 793376
Registergericht: Amtsgericht Stuttgart, Handelsregister A 381535
Geschäftsführer/Gesellschafter: ZOPYX Limited, Birmingham, UK
------------------------------------------------------------------------
E-Publishing, Python, Zope & Plone development, Consulting
Michael Bayer
the "traditional" way to do this is via SELECT...FOR UPDATE. using
a select(), add "for_update=True" as a KW argument. also you have to
be within a transaction.
Michael Bayer
On Jun 29, 2007, at 4:55 AM, Andreas Jung wrote:
> At least the select() method has an optional parameter 'lockmode'.
> You might check the docs and the release notes.
>
lockmode is specific to the ORM. these days it looks like
query.with_lockmode('read').filter(...).. it results in FOR UPDATE
statements.
mc
problem.
As I understand, "FOR UPDATE" locks the rows that were selected.
My problem is as follows:
I select for something.
If that row does not exists, I need to insert it.
I want to avoid the situation where some other client inserts the row
after I have selected (and found out it does not exist) but
before I insert it myself.
In direct SQL, I would LOCK the table for WRITE before the select, and
release the locks after the insert.
Can that be done through SQLAlchemy?
Michael Bayer
On Jun 30, 7:05 pm, mc <mos...@gmail.com> wrote:
> My problem is as follows:
> I select for something.
> If that row does not exists, I need to insert it.
> I want to avoid the situation where some other client inserts the row
> after I have selected (and found out it does not exist) but
> before I insert it myself.
>
> In direct SQL, I would LOCK the table for WRITE before the select, and
> release the locks after the insert.
>
> Can that be done through SQLAlchemy?
im not a fan of pessimistic locking but you can issue "LOCK TABLE" on
the connection easily enough.
mc
You say you are not a fan. What is the preferred way to solve the
problem I described?
Michael Bayer
On Jul 1, 2007, at 11:36 AM, mc wrote:
>
> Thanks.
>
> You say you are not a fan. What is the preferred way to solve the
> problem I described?
optimistically. i would ensure that appropriate constraints are
placed upon the table such that two conflicting INSERT statements
would result in one of them raising an exception; when the second
client encounters this exception, it starts over again and re-SELECTs
the row from the table. The case for the optimistic approach is one
of "how often will a confict reasonably take place ?" I think
conficts on INSERT, for all the use cases I can think of (such as
inserting unique keywords), are exceedingly rare, since they
correspond usually to end-user activities, where two users come up
with the same new information at the exact same time. Even if you
did have thousands of users hammering an application where many are
expected to come up with the exact same information (such as,
everyone is going to tag their photos with "kids" and "pets" which
get added to a table of unique keywords), once a fair degree of all
that "unique" data is inserted, then you'd no longer have conflicts,
and the pessimistic locking would then add tremendous and almost
always unnecessary latency to an applciation that has thousands of
users hammering it. If I were running a really big website, id even
try pre-populate the table with expected values before going live.
on the other hand, if end users are not the issue, and you are
instead writing an application that expects to have INSERT conflicts
because it spawns a huge number of worker threads that are all
operating upon the same data, locking the whole table for each INSERT
will completely defeat the purpose of having worker threads, and you
might as well not use them.
mc
It was very clear and helpful and help set my mind straight on the
issue.
One of the things that confused me was the term "optimistic
locking" (used elsewhere, not by yourself).
In the optimistic approach, there is no locking at all.