Project homepage Mailing List  Warmcat.com  API Docs  Github Mirror 
    npro  
 Modern all-safe Rust Network Protocol library supporting h1, h2, h3, ws, wt sans-IO and with socket IO + tls
git clone https://npro.rs/repo/npro
 
root / src / server / s-task-helpers.c
Author[]Andy Green <andy@warmcat.com> 2026-09-28 13:31 UTC
Committer[]Andy Green <andy@warmcat.com> 2026-09-28 15:17 UTC
Treed9a9875764745186c4c731b351612a39af4aa008   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");
Page fetched 0s ago, creation time: 10ms (vhost etag hits: 0%, cache hits: 0%)