Hello,
I am using SQLite and have encountered an issue with OTAA JOINs.
I had Claude summarize the issue and how to reproduce it, which I've listed below.
Summary
With the SQLite backend, some transactions in chirpstack/src/storage read a row first and write afterwards inside a deferred transaction (diesel's transaction(), which issues a plain BEGIN). When another pooled connection writes to the database between that read and the write, SQLite rejects the write at once with SQLITE_BUSY. The busy handler (busy_timeout = 1000) is not invoked in this situation, so the error surfaces immediately as database is locked.
On the PostgreSQL backend these transactions lock the rows they read with SELECT ... FOR UPDATE (#[cfg(feature = "postgres")] query.for_update()). The SQLite backend has no counterpart, so the read-then-write race is left open.
On our gateways this makes OTAA join requests fail and makes DeviceService.Create return Internal: database is locked. It happens with both the default rollback journal and journal_mode = WAL. The more concurrent writes the gateway handles (uplinks, downlinks, tx acks), the more often it happens.
Environment
ChirpStack v4.19.2, SQLite build (chirpstack -c /etc/chirpstack/), as installed on Kerlink KerOS 6 gateways (service chirpstack-sqlite). Crates per Cargo.lock: diesel 2.3.10, diesel-async 0.9.2, libsqlite3-sys 0.37.0.
Hardware: ARMv7, NXP i.MX6 SoloX (one Cortex-A9 core at 792 MHz), about 220 MiB RAM, eMMC storage. We also see it on a second gateway model.
Region AS923. Local Redis. The gateway is connected through chirpstack-mqtt-forwarder (Semtech UDP backend).
[sqlite] configuration:
[sqlite]
path="sqlite:///var/lib/chirpstack/chirpstack.sqlite"
pragmas=[
"busy_timeout = 1000",
"foreign_keys = ON",
"journal_mode = WAL",
]
max_open_connections not set: default 4
We added journal_mode = WAL ourselves. The same errors occur without it, using the default rollback journal (see the numbers below).
One other process on the gateway opens chirpstack.sqlite, and only read-only (mode=ro, PRAGMA query_only = 1). It never writes, so it cannot be one of the writers involved.
What happened
- OTAA join requests fail
2026-11-01T06:45:08.849932Z INFO up{deduplication_id=9d08f641-...}: chirpstack::uplink: Uplink received f_type=JoinRequest
2026-11-01T06:45:08.933854Z ERROR up{deduplication_id=9d08f641-...}: chirpstack::uplink::join: Handle join-request error error=database is locked
The error is logged 84 ms after the uplink was received, so ChirpStack did not wait for the 1000 ms busy timeout.
No DevNonce is stored for the device afterwards (device_keys.dev_nonces is still {}), and no Join-Accept is sent.
In one hour (06:00 to 07:00 UTC), 8 of 25 join requests failed this way.
The gateway was busy at the time, but far from saturated. It had 1,999 devices registered, about 0.4 data uplinks/s from about 110 active devices, and about 0.2 downlinks/s. Total CPU use was about 22% on the single core.
Data uplinks were not affected: their path (get_for_phypayload_and_incr_f_cnt_up, then partial_update) does not use a read-then-write transaction. - DeviceService.Create returns Internal: database is locked
We register devices through the gRPC API (DeviceService.Create, then CreateKeys or Activate). Our client retries a call once, 200 ms after an Internal error.
Bulk registration under load: 9 registrations failed twice in a row (with the retry) on the gateway above, while it handled the uplink load described in section 1.
Churn test with no radio traffic: on another gateway, we ran 200 cycles of "create device, then delete it" through the API. These calls failed with Internal: database is locked:
Journal mode Run 1 Run 2
default (rollback journal) 15/200 (7.5%) 16/200 (8.0%)
WAL 11/200 (5.5%) 8/200 (4.0%)
In an earlier run with the rollback journal, ChirpStack's request log showed that two failing DeviceService/Create calls finished after 41 ms and 31 ms. Again, the busy timeout was not used.
What we expected
With busy_timeout = 1000, a transaction that meets a concurrent writer should wait, up to one second, and then succeed. Join requests and device creation should not fail under a light, single-gateway load.
Analysis
The affected functions open a deferred transaction, read, and then write. For example, storage::device_keys::validate_incr_join_and_store_dev_nonce (device_keys.rs L108-L145):
let mut c = get_async_db_conn().await?;
let dk: DeviceKeys = c
.transaction::<DeviceKeys, Error, _>(async |c| { // BEGIN (deferred)
let query = device_keys::dsl::device_keys.find(&dev_eui);
#[cfg(feature = "postgres")]
let query = query.for_update(); // row lock on PostgreSQL only
let mut dk: DeviceKeys = query.first(c).await?; // SQLite: read transaction starts here
// ... check and insert the DevNonce ...
diesel::update(device_keys::dsl::device_keys.find(&dev_eui))
.set(/* ... */)
.get_result(c) // SQLite: upgrade to write fails here
.await
})
.await?;
SQLite documents this case:
Transactions: a deferred transaction starts as a read transaction. "If some other database connection has already modified the database or is already in the process of modifying the database, then upgrading to a write transaction is not possible and the write statement will fail with SQLITE_BUSY."
WAL mode (SQLITE_BUSY_SNAPSHOT): if another connection committed after the read snapshot was taken, the write fails with SQLITE_BUSY_SNAPSHOT. Waiting cannot help, because the snapshot is already stale.
Rollback journal (sqlite3_busy_handler): a connection that holds a SHARED lock and wants a RESERVED lock, while another connection holds RESERVED and waits for EXCLUSIVE, gets SQLITE_BUSY without the busy handler being called. Calling it could deadlock.
ChirpStack keeps a pool of 4 connections (max_open_connections, default 4). Uplink handling, downlink handling, tx acks and the scheduler run concurrently on these connections. So a write commonly lands between the read and the write of a join or a device creation.
The PostgreSQL branch already serialises these transactions with for_update(). On SQLite, the equivalent is to take the write lock when the transaction begins: BEGIN IMMEDIATE. A BEGIN IMMEDIATE that meets another writer waits through the busy handler, and its reads can never become stale.
Affected functions (v4.19.2)
Each of these is a deferred transaction that reads first and writes later, and each has a #[cfg(feature = "postgres")] for_update() with no SQLite equivalent:
Function Where
storage::device_keys::validate_incr_join_and_store_dev_nonce device_keys.rs L108-L145
storage::device::create device.rs L253-L288
storage::gateway::create gateway.rs L190-L232
storage::multicast::add_device multicast.rs L291-L331
storage::multicast::add_gateway multicast.rs L351-L399
storage::multicast::enqueue multicast.rs L441-L620
storage::relay::add_device relay.rs L181-L273
We have only observed failures in the first two. The others follow the same pattern.
Transactions whose first statement is a write, such as device::get_with_class_b_c_queue_items, multicast::get_schedulable_queue_items and fuota::create_job, are not affected. A transaction that writes first takes the write lock at its first statement and waits through the busy handler.
Reproduction (without ChirpStack)
The script below models the pattern: 4 connections, busy_timeout of 1000 ms, and transactions that SELECT a row and then UPDATE it. A 5 ms pause between the two statements stands in for the async hop between them. The script is attached as chirpstack-sqlite-locked-repro.py. It needs only the Python standard library.
"""Read-then-write transactions on SQLite with several connections.
Mimics ChirpStack 4.19.2 with the SQLite backend: a pool of 4 connections,
busy_timeout = 1000 ms, and transactions that SELECT a row and then UPDATE it
(as storage::device_keys::validate_incr_join_and_store_dev_nonce does).
Each worker pauses briefly between the SELECT and the UPDATE, standing in for
the async hop between the two statements, and again between transactions.
python locked_repro.py <directory>
"""
import pathlib
import sqlite3
import sys
import threading
import time
WORKERS = 4 # ChirpStack's default max_open_connections
ROUNDS = 300 # transactions per worker
PAUSE = 0.005 # seconds between SELECT and UPDATE, and between transactions
def setup(path, journal):
for suffix in ("", "-wal", "-shm", "-journal"):
pathlib.Path(str(path) + suffix).unlink(missing_ok=True)
c = sqlite3.connect(path)
c.execute(f"PRAGMA journal_mode={journal}")
c.execute("CREATE TABLE k (id INTEGER PRIMARY KEY, n INTEGER)")
c.executemany("INSERT INTO k VALUES (?, 0)", [(i,) for i in range(8)])
c.commit()
c.close()
def run(path, begin, serialize):
lock = threading.Lock() if serialize else None
stats = {"committed": 0, "failed": {}, "waited_ms": []}
guard = threading.Lock()
def failed(where, started):
with guard:
stats["failed"][where] = stats["failed"].get(where, 0) + 1
stats["waited_ms"].append((time.perf_counter() - started) * 1000)
def work():
# timeout=1.0 is busy_timeout = 1000; isolation_level=None lets us
# issue BEGIN ourselves.
c = sqlite3.connect(path, timeout=1.0, isolation_level=None)
for i in range(ROUNDS):
if lock:
lock.acquire()
try:
started = time.perf_counter()
try:
c.execute(begin)
except sqlite3.OperationalError:
failed("BEGIN", started)
continue
n = c.execute("SELECT n FROM k WHERE id = ?", (i % 8,)).fetchone()[0]
time.sleep(PAUSE)
started = time.perf_counter()
try:
c.execute("UPDATE k SET n = ? WHERE id = ?", (n + 1, i % 8))
c.execute("COMMIT")
with guard:
stats["committed"] += 1
except sqlite3.OperationalError:
failed("UPDATE", started)
c.execute("ROLLBACK")
finally:
if lock:
lock.release()
time.sleep(PAUSE)
c.close()
start = time.time()
threads = [threading.Thread(target=work) for _ in range(WORKERS)]
for t in threads:
t.start()
for t in threads:
t.join()
return stats, time.time() - start
def main():
directory = pathlib.Path(sys.argv[1])
cases = (
("WAL, BEGIN (deferred), 4 connections", "WAL", "BEGIN", False),
("DELETE journal, BEGIN (deferred), 4 connections", "DELETE", "BEGIN", False),
("WAL, BEGIN IMMEDIATE, 4 connections", "WAL", "BEGIN IMMEDIATE", False),
("WAL, BEGIN (deferred), 1 connection at a time", "WAL", "BEGIN", True),
)
print(f"SQLite {sqlite3.sqlite_version}, {WORKERS} workers x {ROUNDS} transactions, busy_timeout 1000 ms")
for i, (name, journal, begin, serialize) in enumerate(cases):
path = directory / f"repro-{i}.sqlite"
setup(path, journal)
stats, seconds = run(path, begin, serialize)
failed = sum(stats["failed"].values())
total = failed + stats["committed"]
waited = stats["waited_ms"]
detail = ""
if waited:
waited.sort()
detail = (f" (at {', '.join(f'{k} {v}' for k, v in stats['failed'].items())};"
f" waited before failing: median {waited[len(waited) // 2]:.1f} ms, max {waited[-1]:.1f} ms)")
print(f"{name}: 'database is locked' {failed}/{total} ({100 * failed / total:.1f}%){detail}, {seconds:.1f} s")
main()
Output (Windows 11, Python 3.14.5, SQLite 3.50.4; two runs gave the same picture):
SQLite 3.50.4, 4 workers x 300 transactions, busy_timeout 1000 ms
WAL, BEGIN (deferred), 4 connections: 'database is locked' 900/1200 (75.0%) (at UPDATE 900; waited before failing: median 0.0 ms, max 0.8 ms), 3.4 s
DELETE journal, BEGIN (deferred), 4 connections: 'database is locked' 588/1200 (49.0%) (at UPDATE 588; waited before failing: median 0.1 ms, max 0.4 ms), 11.7 s
WAL, BEGIN IMMEDIATE, 4 connections: 'database is locked' 6/1200 (0.5%) (at BEGIN 6; waited before failing: median 1165.5 ms, max 1182.2 ms), 13.0 s
WAL, BEGIN (deferred), 1 connection at a time: 'database is locked' 0/1200 (0.0%), 8.0 s
What the output shows:
Deferred transactions: they fail at the UPDATE, after 0 to 0.8 ms, with both journal modes. The busy timeout plays no part.
BEGIN IMMEDIATE: with the same contention, almost every transaction succeeds. The few failures come only after the full busy timeout (about 1.17 s), at BEGIN. That is the expected, configurable behaviour, and the model is far more contended than a real gateway. Each worker spends half of its time inside a transaction.
One connection: serialising all transactions also removes the errors.
Suggested fix
On SQLite, start the read-then-write transactions above with BEGIN IMMEDIATE. diesel-async 0.9 already provides this for the SQLite wrapper: SyncConnectionWrapper::immediate_transaction (diesel-async 0.9.2, src/sync_connection_wrapper/sqlite.rs). For example:
let mut c = get_async_db_conn().await?;
#[cfg(feature = "sqlite")]
let dk: DeviceKeys = c
.immediate_transaction::<DeviceKeys, Error, _>(async |c| { /* same body */ })
.await?;
#[cfg(feature = "postgres")]
let dk: DeviceKeys = c
.transaction::<DeviceKeys, Error, _>(async |c| { /* same body, with for_update() */ })
.await?;
A small helper in storage (for example write_transaction) could choose immediate_transaction on SQLite and transaction on PostgreSQL, so that the bodies do not have to be duplicated. We have not built or tested this patch.