如果数据库被锁定，重试 SQLite 查询的最简单方法是什么? [英] Simplest way to retry SQLite query if DB is locked?

问题描述

我不太确定该问哪里，我希望它就在这里.

I as not quite sure where to ask, I hope it is right here.

我搜索的是在数据库繁忙时重试 SQLite 查询的最简单解决方案.我在服务器上使用 quassel 作为我的 IRC 客户端，并且我想将旧日志移动到一个单独的数据库以保持它使用的较小.我为此编写的脚本是:

What I search for is the simplest solution to retry a SQLite query if the DB is busy. I use quassel as my IRC client on an server and I want to move old logs to a separate DB to keep the one it uses small. The script I wrote to do that is:

CREATE TEMP TABLE delfrom (id integer,val integer);
ATTACH '/home/irc/oldlog.db' as log;
BEGIN IMMEDIATE;
REPLACE INTO delfrom (id,val) select 1337,messageid from backlog where time < strftime('%s', 'now','-14 days') ORDER BY messageid DESC LIMIT 1;
INSERT INTO log.log (messageid,time,bufferid,type,flags,senderid,message) SELECT messageid,time,bufferid,type,flags,senderid,message FROM backlog WHERE messageid < (SELECT val FROM delfrom where id=1337);
DELETE FROM backlog WHERE messageid < (SELECT val FROM delfrom where id=1337);
PRAGMA incremental_vacuum;
COMMIT;

我使用 sqlite3 quassel-storage.sqlite < 运行它movelog.sql

And I run it using sqlite3 quassel-storage.sqlite < movelog.sql

问题是，由于 quassel 在执行时正在运行，有时 BEGIN IMMEDIATE; 会因为数据库被锁定而失败.

The problem is, since quassel is running while this executes, sometimes the BEGIN IMMEDIATE; fails because the DB is locked.

有人可以建议我一种简单的方法来更改该设置，以便每隔几秒钟重试一次查询，直到它起作用吗?我读到 python SQLite 包装器内置了?有没有一种特殊的方式我必须激活它，更重要的是，我可以使用 python 附加第二个数据库吗?sqlite3.connect 有一个超时参数，但我不太确定它是如何工作的.Python 是否锁定整个数据库以在每次连接时写入?

Can someone suggest me an easy way to change that setup so the query is just retried every few seconds until it works? I read that the python SQLite wrapper has that built in? Is there a special way I have to activate this, and more important, can I attach the second DB using python? There is a timeout parameter to sqlite3.connect but I am not quite sure how that works. Does Python lock the whole DB for writing on every connect?

我并不执着于使用 Python.我更喜欢的解决方案是，当发生此错误时，sqlite3 返回 0，然后将其包装在 shell 中的一个循环中，但这似乎不起作用.

I am in no way fixated to using Python. The solution I'd prefer would be that sqlite3 returns 0 when this error occurs and then just wrap it in an loop in the shell, but that seems not to work.

推荐答案

如果 table 被锁定，Python 将定期重试.如果数据库被锁定，它不会重试.表锁只在同一个进程内通过线程、共享连接或其他方法传播.

Python will retry regularly if the table is locked. It will not retry if the Database is locked. Table locks are only propagated inside the same process, by se of threads, shared connections or other methods.

当多个进程写入文件时会导致数据库锁定，并且(简单地说)只要日志存在就存在.

Database locks result when multiple processes write to the file, and (put simply) exist as long as the Journal exists.

为了避免这种情况，可以使用 WAL 模式进行日志记录.( pragma journal_mode=wal; )

To avoid this, the WAL mode can be used for journalling. ( pragma journal_mode=wal; )

要旋转数据库锁，你需要用这样的东西包装 execute 函数:

To spin on database locks, you need to wrap the execute function with something like this:

for x in range(0, timeout):
  try:
    with connection:
      connection.execute(sql)
  except:
     time.sleep(1)
     pass
  finally:
     break
else:
    with connection:
        connection.execute(sql)  

最后一个连接块将使其正确返回异常.这应该通过检查数据库锁定的异常并以其他方式引发原始异常来改进，但这留给读者作为练习.

The last connection block will make it return exceptions properly. This should be improved by checking the exception for database locked and otherwise raising the original exception, but that is left as an exercise for the reader.

这篇关于如果数据库被锁定，重试 SQLite 查询的最简单方法是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！