Statement destructor blocks on connection mutex held during busy-handler wait

[andreasronge]

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_timeout to 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.

[warmwaffles]

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.