| Author | Andy Green <andy@warmcat.com> 2026-09-28 13:31 UTC | | Committer | Andy Green <andy@warmcat.com> 2026-09-28 15:17 UTC | | Tree | d9a9875764745186c4c731b351612a39af4aa008 Raw Patch | | | common: stop event db opens taking the write lock, and wait out contention | common: stop event db opens taking the write lock, and wait out contention
sai-server logged runs of "database is locked" inserting a live
event's task logs, losing those log lines.
sai-server and sai-web each hold their own connection to the same
per-event sqlite3 files, and sai_event_db_ensure_open(), which both use,
unconditionally dropped and rebuilt idx_task_uuid every time it opened
one: a write transaction. sai-web opens an event's db the first time
anything asks about the event (the sidebar summaries, the tasks pane,
the rss feed), which for a new event is exactly while sai-server is
streaming its logs in. No connection had a busy timeout, so sai-server's
inserts that met the lock failed at once.
- only rebuild idx_task_uuid when it is a legacy one lacking the run
column; the rest of the open-time schema work already writes nothing
on an existing db
- give the event db and events db connections in both daemons a short
busy timeout, so a write meeting the other daemon's lock waits for it
rather than failing (sai-web writes the events db too, for its
saiweb_state)
- fix sai_event_db_close()'s inverted refcount test: idle_since was
stamped while the db was still in use and not when it went idle, so
s-central.c's 60s idle reaper closed dbs on its next pass, and every
reopen ran the index rebuild again
Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
|
diff --git a/src/common/c-sqlite3.c b/src/common/c-sqlite3.c
index e8e9111..b1532aa 100644
--- a/src/common/c-sqlite3.c
+++ b/src/common/c-sqlite3.c
@@ -27,6 +27,37 @@
#include "include/private.h"
+/*
+ * Event dbs made before tasks could have several runs have idx_task_uuid on
+ * tasks(uuid) alone, and need it rebuilt on tasks(uuid, run). Only do that
+ * when the index needs it: rebuilding it takes the db write lock, and both
+ * sai-server and sai-web open event dbs while sai-server is writing a live
+ * event's logs into them.
+ */
+static int
+sai_task_index_needs_run(sqlite3 *pdb)
+{
+ sqlite3_stmt *sm;
+ const char *sql;
+ int r = 0;
+
+ if (sqlite3_prepare_v2(pdb, "SELECT sql FROM sqlite_master WHERE "
+ "type='index' AND name='idx_task_uuid'", -1,
+ &sm, NULL) != SQLITE_OK)
+ return 0;
+
+ if (sqlite3_step(sm) == SQLITE_ROW) {
+ sql = (const char *)sqlite3_column_text(sm, 0);
+ r = !sql || !strstr(sql, "run");
+ } else
+ /* missing entirely */
+ r = 1;
+
+ sqlite3_finalize(sm);
+
+ return r;
+}
+
int
sai_event_db_ensure_open(struct lws_context *cx, lws_dll2_owner_t *sqlite3_cache,
const char *sqlite3_path_lhs, const char *event_uuid,
@@ -68,7 +99,16 @@ sai_event_db_ensure_open(struct lws_context *cx, lws_dll2_owner_t *sqlite3_cache
return 2;
}
- /* create / add to the schema for the tables we will have in here */
+ sqlite3_busy_timeout(*ppdb, SAI_SQLITE3_BUSY_TIMEOUT_MS);
+
+ /*
+ * create / add to the schema for the tables we will have in here.
+ *
+ * On an existing db, none of this writes: "if not exists" finds the
+ * table or index already there, an ALTER TABLE adding a column that
+ * exists fails when it is prepared, and journal_mode=WAL is already
+ * set. That matters since the other daemon may be writing to it.
+ */
if (lws_struct_sq3_create_table(*ppdb, lsm_schema_sq3_map_task)) {
lwsl_err("%s: unable to create task table in %s\n", __func__, filepath);
@@ -110,10 +150,14 @@ sai_event_db_ensure_open(struct lws_context *cx, lws_dll2_owner_t *sqlite3_cache
sai_sqlite3_statement(*ppdb, "CREATE INDEX IF NOT EXISTS idx_art_task ON artifacts(task_uuid);", "create artifact index");
-
- /* Migrate the unique index to include the run column */
- sqlite3_exec(*ppdb, "DROP INDEX IF EXISTS idx_task_uuid;", NULL, NULL, NULL);
- sqlite3_exec(*ppdb, "CREATE UNIQUE INDEX idx_task_uuid ON tasks(uuid, run);", NULL, NULL, NULL);
+ if (sai_task_index_needs_run(*ppdb)) {
+ lwsl_notice("%s: migrating idx_task_uuid in %s\n", __func__,
+ filepath);
+ sqlite3_exec(*ppdb, "DROP INDEX IF EXISTS idx_task_uuid;",
+ NULL, NULL, NULL);
+ sai_sqlite3_statement(*ppdb, "CREATE UNIQUE INDEX idx_task_uuid "
+ "ON tasks(uuid, run);", "migrate task index");
+ }
sc = malloc(sizeof(*sc));
if (!sc) {
@@ -150,8 +194,8 @@ sai_event_db_close(lws_dll2_owner_t *sqlite3_cache, sqlite3 **ppdb)
if (sc->pdb == *ppdb) {
*ppdb = NULL;
- if (--sc->refcount) {
- lwsl_notice("%s: zero refcount to idle\n",
+ if (!--sc->refcount) {
+ lwsl_info("%s: zero refcount to idle\n",
__func__);
/*
* He's not currently in use then... don't
diff --git a/src/common/include/private.h b/src/common/include/private.h
index 348edb2..d20aca5 100644
--- a/src/common/include/private.h
+++ b/src/common/include/private.h
@@ -71,6 +71,15 @@
#define SAI_BUILDER_INSTANCE_LIMIT 256
+/*
+ * sai-server and sai-web each hold their own connections to the same sqlite3
+ * files (the events db and the per-event dbs). Without a busy timeout, a
+ * write that meets the other process's write lock fails at once with "database
+ * is locked" and the data (eg, task log lines) is lost. Wait this long for
+ * the lock instead: writes are small, so contention is brief.
+ */
+#define SAI_SQLITE3_BUSY_TIMEOUT_MS 500
+
struct sai_plat;
struct sai_builder;
struct saib_opaque_spawn;
diff --git a/src/server/s-comms.c b/src/server/s-comms.c
index b97dc2f..de8f740 100644
--- a/src/server/s-comms.c
+++ b/src/server/s-comms.c
@@ -315,6 +315,9 @@ s_callback_ws(struct lws *wsi, enum lws_callback_reasons reason, void *user,
return -1;
}
+ /* the other daemon has this db open too */
+ sqlite3_busy_timeout(vhd->server.pdb, SAI_SQLITE3_BUSY_TIMEOUT_MS);
+
sai_sqlite3_statement(vhd->server.pdb,
"PRAGMA journal_mode=WAL;", "set WAL");
diff --git a/src/web/w-comms.c b/src/web/w-comms.c
index c701fa8..65be1df 100644
--- a/src/web/w-comms.c
+++ b/src/web/w-comms.c
@@ -322,6 +322,9 @@ w_callback_ws(struct lws *wsi, enum lws_callback_reasons reason, void *user,
return -1;
}
+ /* the other daemon has this db open too */
+ sqlite3_busy_timeout(vhd->pdb, SAI_SQLITE3_BUSY_TIMEOUT_MS);
+
sai_sqlite3_statement(vhd->pdb,
"PRAGMA journal_mode=WAL;", "set WAL");
|