Simplest way to retry SQLite query if DB is locked?

Question

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

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;

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

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

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?

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.

Answer 1

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.

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

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.