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 sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.