如何在 sqlalchemy 中强制执行 sqlite 选择以更新事务行为 [英] how to enforce sqlite select for update transaction behavior in sqlalchemy

问题描述

昨天我正在处理一些需要选择...更新"概念的 sqlalchemy 东西，以避免竞争条件.向查询添加 .with_lockmode('update') 对 InnoDB 和 Postgres 有效，但对于 sqlite 我最终不得不潜入一个

Yesterday I was working with some sqlalchemy stuff that needed a "select ... for update" concept to avoid a race condition. Adding .with_lockmode('update') to the query works a treat on InnoDB and Postgres, but for sqlite I end up having to sneak in a

if session.bind.name == 'sqlite':
    session.execute('begin immediate transaction')

在进行选择之前.

这似乎暂时有效，但感觉像是作弊.有没有更好的方法来做到这一点?

This seems to work for now, but it feels like cheating. Is there a better way to do this?

推荐答案

SELECT ... FOR UPDATE OF ... 不受支持.这是可以理解的考虑到 SQLite 在该行锁定中的机制是多余的因为在更新它的任何一点时整个数据库都被锁定.然而，如果未来版本的 SQLite 支持 SQL，那就太好了如果没有别的原因，可互换性的原因.唯一的功能所需的是确保保留"如果数据库上有锁还没有.

SELECT ... FOR UPDATE OF ... is not supported. This is understandable considering the mechanics of SQLite in that row locking is redundant as the entire database is locked when updating any bit of it. However, it would be good if a future version of SQLite supports it for SQL interchageability reasons if nothing else. The only functionality required is to ensure a "RESERVED" lock is placed on the database if not already there.

摘自https://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSql

[编辑] 另见 https://sqlite.org/isolation.html 谢谢@michauwilliam.

[EDIT] also see https://sqlite.org/isolation.html thanks @michauwilliam.

我认为您必须同步对整个数据库的访问.正常的同步机制也应该在这里应用文件锁，进程同步等

i think you have to synchronize the access to the whole database. normal synchronization mechanism should also apply here file lock, process synchronization etc

这篇关于如何在 sqlalchemy 中强制执行 sqlite 选择以更新事务行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！