SQLite で「database is locked」が出て書き込めない

要約

2 つ目の接続から書き込もうとすると、しばらく待たされた後に失敗する。

sqlite3.OperationalError: database is locked

SQLite は同時に 1 つの書き込みしか受け付けない。
公式ドキュメントの SQLITE_BUSY の説明のとおり、別の接続が書き込みトランザクションの途中なら、後から来た接続はそれが終わるまで待つ必要がある。
待ち時間（busy timeout）を過ぎると、この database is locked が返る。
Python の sqlite3 では connect() の timeout が待ち時間で、既定は 5 秒だ。

よくある原因

1. コミットしていない接続がある: Python の sqlite3 は既定で INSERT / UPDATE / DELETE の前に暗黙にトランザクションを開く。commit() を呼ぶまで、他の接続は書き込めない。
2. 失敗した接続がロックを持ち続けている: 書き込みに失敗した接続は、rollback() するまでトランザクションの中にいる（con.in_transaction が True のまま）。
   そのまま SELECT を実行すると読み取りのロックを持ち続け、今度は相手側の commit() が database is locked で失敗する。
3. 待ち時間が短い: timeout=0.5 なら 0.5 秒で諦める。
   相手が 1 秒で終わる処理でも失敗する。
4. ロールバックジャーナルのまま使っている: 既定のジャーナルでは、読み取り中の接続があると書き込み側がコミットできない。
5. ネットワーク上のファイル: SQLite の書き込みはファイルロックに頼っている。
   公式ドキュメントは、ネットワークファイルシステムではこのロックが正しく動かない例があると注意している。
   WAL モードも同じホストのプロセスで共有メモリを使う仕組みなので、ネットワークファイルシステムでは使えない。

解決策

1. トランザクションを短くし、失敗したら戻す

接続を with で使うと、ブロックを抜けるときに成功なら commit()、例外なら rollback() が呼ばれる。

import sqlite3
 
con = sqlite3.connect("app.db")
con.execute("CREATE TABLE IF NOT EXISTS t(x)")
with con:
    con.execute("INSERT INTO t VALUES (?)", (1,))
con.close()

with は接続を閉じないので、使い終わったら close() する。
書き込みの前に重い処理を挟まず、トランザクションを開いている時間を短くする。

2. 待ち時間を延ばす

con = sqlite3.connect("app.db", timeout=10.0)

Python 以外のクライアントでは、SQL で設定できる。

PRAGMA busy_timeout = 10000;  -- ミリ秒

待ち時間は接続ごとの設定なので、すべての接続で設定する。

3. WAL モードにする

PRAGMA journal_mode=WAL;

WAL モードでは、書き込み中でも他の接続は読み取れる。
一度設定するとデータベースファイルに記録され、以降の接続にも引き継がれる。
書き込みは引き続き 1 つずつなので、手順 1 と 2 は WAL でも必要になる。
ファイルの横にできる -wal はデータベースの一部なので、コピーや移動のときは本体と一緒に扱う（-shm は共有メモリ用の索引ファイル）。

4. ネットワーク上に置かない

NFS や SMB の共有フォルダに置いたデータベースを複数のマシンから書き込む構成は避け、ローカルのディスクに置く。
複数のサーバーから同時に書き込む用途には、サーバー型のデータベースを検討する。