我正在尝试SELECT FOR SHARE表中的一组行,以便它们被锁定直到事务结束 . 我使用SQLAlchemy 0.7.9在PostgreSQL 9.1.6数据库中执行此操作 . 这是有问题的python代码:
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()
问题是上面的选择代码生成的SQL查询不是FOR SHARE,它是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
根据SQLAlchemy API docs,在"for_update"参数说明下:
使用Postgresql方言,值“read”和“read_nowait”分别转换为FOR SHARE和FOR SHARE NOWAIT .
根据以上所述,编译的SQL语句应该是FOR SHARE,但事实并非如此 . 我的代码中的错误在哪里?
1 回答
无法重现:
输出显示,作为独立编译以及Postgresql对话,我们得到FOR SHARE . 测试结果为0.7.9和0.8.0b2 . 如果你能提供一个完整的可运行的测试用例,那么可能会有更多的亮点: