I am trying SELECT FOR SHARE a set of rows in a table so that they are
locked until the end of the transaction. I am using SQLAlchemy 0.7.9 to do
this in a PostgreSQL 9.1.6 database. This is the python code in question:
NUM_TERMS = 10
conn = engine.connect()
get_terms = select([search_terms.c.term_id, search_terms.c.term],
and_(search_terms.c.lock==False,
search_terms.c.status==False),
order_by=search_terms.c.term,
limit=NUM_TERMS, for_update="read")
trans = conn.begin()try:
search_terms = conn.execute(get_terms).fetchall()
for term in search_terms:
lock_terms = update(search_terms).\
where(search_terms.c.term_id==term.term_id).\
values(lock=True)
conn.execute(lock_terms)
if trans.commit():
<do things with the search terms>except:
trans.rollback()
The problem is the SQL query generated by the select code above is not FOR
SHARE, it's FOR UPDATE:
SELECT search_terms.term_id, search_terms.term
FROM search_terms
WHERE search_terms.lock = :lock_1 AND search_terms.status = :status_1
ORDER BY search_terms.term
LIMIT :param_1 FOR UPDATE
According to the SQLAlchemy API
docs<http://docs.sqlalchemy.org/en/rel_0_7/core/expression_api.html#sqlalchemy.sql.expression.select>,
under
the "for_update" parameter description:
With the Postgresql dialect, the values “read” and "read_nowait" translate
to FOR SHARE and FOR SHARE NOWAIT, respectively.
According to the above, the compiled SQL statement should be FOR SHARE, but
it is not. Where is the error in my code?
If you have a good answer, please also answer it on
stackoverflow<http://stackoverflow.com/questions/13983048/how-to-select-for-share-using-sqlalchemy-with-postgresql>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sqlalchemy/-/wmFM_7UILRoJ.
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.
