Repository navigation
Statement destructor blocks on connection mutex held during busy-handler wait #360
Description
Summary
statement_type_destructor acquires the owning connection's mutex (statement_acquire_lock) before sqlite3_finalize. A connection that is waiting in the busy handler (for example on BEGIN IMMEDIATE while another connection holds the write lock) holds that same mutex for the whole busy wait. So any process that drops the last reference to a statement belonging to the waiting connection blocks inside the destructor until the waiter's busy_timeout expires.
If the process that blocks is the one holding SQLite's write lock, the waiter cannot succeed (it is waiting for that holder), so the stall always runs to the full busy_timeout and the waiter then fails with database is locked. With DBConnection/Ecto this happens naturally: Exqlite.Connection.handle_execute/4 prepares a fresh statement per execute, the statement term becomes garbage in the calling process, and long-lived callers use different pool connections over time. We saw repeated writer-lock holds of 5/10/15 s (busy_timeout: 15_000) on SELECTs over tiny tables, each ending within milliseconds of another connection's BEGIN IMMEDIATE failing.
Reproduction (exqlite 0.40.0, also present in 0.41.0)
alias Exqlite.Sqlite3
db = Path.join(System.tmp_dir!(), "destructor_#{System.unique_integer([:positive])}.db")
{:ok, setup} = Sqlite3.open(db)
:ok = Sqlite3.execute(setup, "PRAGMA journal_mode=WAL; CREATE TABLE t(id INTEGER PRIMARY KEY)")
{:ok, holder} = Sqlite3.open(db)
{:ok, w} = Sqlite3.open(db)
:ok = Sqlite3.set_busy_timeout(w, 3_000)
parent = self()
writer = spawn(fn ->
{:ok, foreign_stmt} = Sqlite3.prepare(w, "SELECT 1") # statement owned by w
:ok = Sqlite3.execute(holder, "BEGIN IMMEDIATE")
send(parent, :holding)
receive do :go -> :ok end
_ = foreign_stmt
{us, _} = :timer.tc(fn ->
:erlang.garbage_collect() # drops the last reference
{:ok, s} = Sqlite3.prepare(holder, "SELECT count(*) FROM t"); Sqlite3.step(holder, s)
end)
send(parent, {:holder_ms, div(us, 1000)})
:ok = Sqlite3.execute(holder, "COMMIT")
end)
receive do :holding -> :ok end
waiter = Task.async(fn ->
{us, r} = :timer.tc(fn -> Sqlite3.execute(w, "BEGIN IMMEDIATE") end)
{div(us, 1000), r}
end)
Process.sleep(300)
send(writer, :go)
receive do {:holder_ms, ms} -> IO.puts("holder GC + SELECT: #{ms} ms") end
IO.inspect(Task.await(waiter, 10_000), label: "waiter (ms, result)")Output:
holder GC + SELECT: 3214 ms
waiter (ms, result): {3504, {:error, "database is locked"}}
Control: preparing foreign_stmt on holder instead of w gives holder GC + SELECT: 7 ms and the waiter acquires the lock normally.
Why it matters
- The destructor runs in whichever process drops the last reference, usually during GC on a normal scheduler, so an unrelated process can block for up to
busy_timeout. - Raising
busy_timeoutto ride out contention makes each stall longer.
Possible direction
Avoid blocking in the destructor: for example enif_mutex_trylock, and if the connection is busy, defer finalisation (queue the sqlite3_stmt* on the connection and finalise it on the connection's next locked call or on close). sqlite3_finalize without exqlite's mutex would still wait on SQLite's db->mutex, which the busy-waiting step also holds in serialized mode, so deferral seems necessary.
Our workaround is a short busy_timeout (250 ms) with the long wait retried in Elixir between attempts.
Activity
warmwaffles commentedon Sep 23, 2026
Did you hit this while running in a production environment?
Edit: I am curious what the work load is that caused this. Just need some more context so that a robust solution can be obtained.
andreasronge commentedon Sep 28, 2026
Yes, in production. The application is a small Phoenix LiveView + Oban service on one SQLite file (WAL, ecto_sqlite3 0.24.1, exqlite 0.40.0, pool size 5) running on a 4-core VM that also runs CPU-heavy build and test jobs.
Workload
BEGIN IMMEDIATEtransaction every 5 s that updates about a dozen rows.handle_execute/4prepares a fresh statement each time. A long-lived process checks out different pool connections over time, so sooner or later its heap holds garbage statements prepared on a connection that is currently in the busy handler.Symptom
With
busy_timeout: 15_000, we saw write-lock holds of almost exactly 15 s (5 s and 10 s at earlier settings) on SELECTs over tables with fewer than 100 rows. Each hold ended within milliseconds of another connection'sBEGIN IMMEDIATEfailing withdatabase is locked/Database busy. Telemetry showed the time was spent neither in SQLite nor in the pool queue. Downstream, an internal RPC with a 10 s socket timeout failed whenever it hit a stall, so real work failed rather than just ran slowly. The reproduction in the issue came from isolating that pattern; it does not reproduce without a statement from another connection.Workaround
A thin Ecto adapter wrapper sets
busy_timeout: 250and retries a busyBEGIN(or a statement outside a transaction) in Elixir, between attempts, up to the real deadline, so no exqlite mutex is held while waiting. Over the last two days: no transaction held the write lock for more than 1 s, no writer waited more than 1 s, and no job failed on a lock. The cost is about tendisconnected: database is lockedlog lines a day, one per busyBEGINthat is retried on a fresh connection.We'd be glad to test a fix. Deferring
sqlite3_finalizewhenenif_mutex_trylockfails, and draining the deferred statements on the connection's next locked call and on close, would let us remove the wrapper.warmwaffles commentedon Sep 28, 2026
If you can reliably reproduce it locally in your project, downgrade exqlite to
v0.34.0. Ifmix deps.getgives you grief, just useforce: true. Nothing interface wise between Ecto and this should require a version bump.Changes were made in
v0.35.0that introduced newer mutex locking, and I want to attempt to rule it out.andreasronge commentedon Sep 29, 2026
Tried v0.34.0 and reproduced the stall. The relevant locking predates v0.35.0:
statement_type_destructoralready acquired the owning connection's mutex throughstatement_acquire_lock, andexqlite_executealready held that mutex acrosssqlite3_exec, including the busy wait.Five runs each on macOS,
busy_timeout3000. Each entry is the holder's GC + SELECT time in ms and the waiter's result (L =database is locked):PRAGMA busy_timeoutPRAGMA busy_timeoutset_busy_timeout/2set_busy_timeout/2PRAGMA busy_timeoutset_busy_timeout/2A correction to my original report: the repro has a scheduling race. The 300 ms sleep makes it likely, but does not guarantee, that the waiter is already in the busy handler when the holder garbage-collects, so the stall is timing-dependent rather than deterministic (16 of 25 non-control runs). It occurred with both SQLite's
busy_timeouthandler and exqlite's own handler, and was not observed in the five control runs. Versions withoutset_busy_timeout/2were tested withPRAGMA busy_timeout.Why removing exqlite's mutex from the destructor would not be enough (line numbers at v0.40.0):
SQLITE_THREADSAFE=1(Makefile:96), so connections use serialized mode by default.sqlite3_stepandsqlite3_execholddb->mutexfor the whole call, and the busy handler runs with it held. SQLite asserts this inbtreeInvokeBusyHandler(c_src/sqlite3.c:75732), and the default handler sleeps without releasing it.sqlite3_finalizeenters the samedb->mutex(c_src/sqlite3.c:93797).So a destructor that called
sqlite3_finalizewithout exqlite's mutex would still wait for the busy-waiting connection. The finalize itself has to be deferred.A wider effect: resource destructors are not dirty NIF calls. When the last reference is dropped by an ordinary process GC, the blocking
enif_mutex_lockinstatement_type_destructor(c_src/sqlite3_nif.c:1415) can hold a normal scheduler thread for up tobusy_timeout. Thebind_*NIFs, which also run on normal schedulers, wait on the same mutex.Fix sketch:
prepareso the destructor never allocates.sqlite3_stmt*onto that queue. It takes no connection mutex and makes no SQLite call.sqlite3_close_v2.Happy to test a patch against our workload.
jounimakela commentedon Sep 29, 2026
We also hit this while running in production with versions
0.36.0and0.40.0. SQLite in WAL mode and pool size of 10.Our workload is small, ~5 inserts per second, each from a new short-lived process that exits after its insert. The only transactions are Oban's Stager, Lifeline and Pruner.
We first noticed "Database busy" in the error logs, which I thought could be fixed with
default_transaction_mode: :immediateand 5 second busy timeout. Instead, it made the problem more frequent and resulted in a cluster of 2-4 failed inserts every ~1.5 to 14 minutes.After debugging, I noticed that the whole application stopped for ~5 seconds, which is suspiciously close to the new busy timeout. When I lowered
busy_timeoutto 2 seconds, the stops became ~2 seconds, so the freeze follows the busy timeout. Digging further, I came to the same conclusion as the author.As a workaround, I first raised the Oban Stager interval to 60 seconds, which made the problem much rarer for us (a cluster every ~4.4 hours), and finally set the database pool size to 1, which shouldn't crash, and hasn't.
The problem reproduces on
0.34.0with a standalone script, without Ecto or Oban.warmwaffles commentedon Sep 29, 2026
This is good information. I'll work on a solution soon. I appreciate the detail.
warmwaffles commentedon Sep 29, 2026
@andreasronge and @jounimakela I stood up a fix for this in #361
I've improved the busy handler to not block as bad as it did. Unfortunately, this fix is on a branch so you'll need to point your dep at the branch. This will necessitate that you have gcc or clang installed in order to compile the dependency as I don't have a precompiled binary up. That will be created if this is successful and I cut a new release for
v0.42.0warmwaffles commentedon Sep 29, 2026
Something to note about that fix, if you can load test against it and keep tabs on memory usage that would be good. It adds a linked list to cleanup prepared statements at a later time (ms later). The fix will increase memory usage because it introduces a linked list that we use to defer finalizing sqlite3 statements. It should eventually be reclaimed by the running process once the finalization has been performed.
fix: finish deferred statement cleanup without blocking
Update every dependency; exqlite 0.43 fixes the SQLite lock stalls (#416