如何在sqlalchemy中强制执行sqlite的select for update事务行为
昨天我在处理一些sqlalchemy的东西，需要用到“选择...更新”的概念，以避免竞争条件。给查询加上.with_lockmode('update')在InnoDB和Postgres上效果很好，但在sqlite上，我最终不得不偷偷加一个
if session.bind.name == 'sqlite':
session.execute('begin immediate transaction')
然后再进行选择。
现在这样似乎能用，但我觉得这有点不太正当。有没有更好的方法呢？
3 个回答
你可以用条件更新来解决这个问题，因为更新操作会返回受影响的行数。
row := "SELECT * from queue WHERE status = 'unclaimed' LIMIT 1"
res := "UPDATE queue SET status = 'claimed' where id = ? AND status = 'unclaimed'"
if (res.affectedRowCount == 0) // try again in a loop...
这里加上的条件 status = 'unclaimed' 确保只有第一个服务器能成功把这一行标记为已认领。第二次更新会显示受影响的行数为0，因为这一行已经不再是未认领状态了。
我觉得在SQLite中使用SELECT FOR UPDATE是很重要的。因为在我开始写之前，无法锁定数据库，这样就太晚了。让我给你讲个例子：
我有两台服务器和一个数据库队列表。每台服务器都在寻找工作，当它找到一个任务时，会在队列表中更新状态，标记为“我拿到了”，这样另一台服务器就不会再去抢同样的工作。我需要保留这个记录，以便在恢复时使用。
服务器1读取第一个未被认领的任务，并把它放在内存中。然后服务器2也读取了同一个记录，现在它的内存中也有这个任务。接着，服务器1更新了这个记录，锁定了数据库，完成更新后再解锁。然后服务器2也锁定了数据库，进行更新并解锁。结果是，两台服务器都在处理同一个任务。表格显示是服务器2在处理，而服务器1的更新就丢失了。
我通过创建一个锁定数据库表来解决这个问题。服务器1开始一个事务，写入锁定表，这样就锁定了数据库以进行写入。服务器2现在尝试开始一个事务并写入锁定表，但被阻止了。接着，服务器1读取第一个队列记录，并用“我拿到了”的代码更新它。然后，它删除刚刚写入的锁定记录，提交事务并释放锁定。现在服务器2可以开始它的事务，写入锁定表，读取第二个队列记录，用它的“我拿到了”的代码更新，删除它的锁定记录，提交事务，这样数据库就可以被下一台寻找工作的服务器使用了。
在SQLite中，使用“SELECT ... FOR UPDATE OF ...”这个语句是不支持的。这是可以理解的，因为在SQLite的工作机制中，行锁是多余的，因为在更新数据库的任何部分时，整个数据库都会被锁定。不过，如果将来SQLite的版本能够支持这个功能，那就很好了，至少可以在SQL的兼容性方面有所帮助。所需的唯一功能是确保在数据库上放置一个“保留”锁，如果还没有的话。
摘自 https://www2.sqlite.org/cvstrac/wiki?p=UnsupportedSql
[编辑] 还可以查看 https://sqlite.org/isolation.html 感谢 @michauwilliam。
我认为你需要同步对整个数据库的访问。正常的同步机制在这里也适用，比如文件锁、进程同步等等。