SQLITE_BUSY errors

skobkin commented

2026-02-28 21:58:24 +03:00

Owner

While tracerouting on local dev environment:

time="2026-02-28 21:30:53.163" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.183" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.197" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.213" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.230" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.235" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.253" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.264" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:30:53.281" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:31:02.181" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:31:02.201" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)"                                                                                                    
time="2026-02-28 21:31:02.214" level=ERROR msg="insert log event failed" pkg=internal/ingest kind=routing node_id=!a55e5e56 err="database is locked (5) (SQLITE_BUSY)"                                                          
time="2026-02-28 21:31:02.215" level=ERROR msg="upsert node failed" pkg=internal/ingest node_id=!a55e5e56 err="database is locked (5) (SQLITE_BUSY)"

While tracerouting on local dev environment: ``` time="2026-02-28 21:30:53.163" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.183" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.197" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.213" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.230" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.235" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.253" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.264" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:30:53.281" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:31:02.181" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:31:02.201" level=ERROR msg="log events" pkg=internal/api/http err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:31:02.214" level=ERROR msg="insert log event failed" pkg=internal/ingest kind=routing node_id=!a55e5e56 err="database is locked (5) (SQLITE_BUSY)" time="2026-02-28 21:31:02.215" level=ERROR msg="upsert node failed" pkg=internal/ingest node_id=!a55e5e56 err="database is locked (5) (SQLITE_BUSY)" ```

skobkin added the

bug

label

2026-02-28 21:58:24 +03:00

skobkin self-assigned this

2026-02-28 21:58:24 +03:00

skobkin commented

2026-02-28 23:06:19 +03:00

Author

Owner

SQLite performance optimization status

Status
- Implemented:
  - file-backed SQLite open hardening in internal/persistence/sqlite/store.go
    - journal_mode=WAL
    - busy_timeout=5000
    - foreign_keys=ON
    - SetMaxOpenConns(1) / SetMaxIdleConns(1)
    - startup logging of effective settings
  - regression coverage in internal/persistence/sqlite/store_open_test.go and internal/persistence/sqlite/store_write_test.go
  - write-path reductions:
    - UpsertNode(...) no longer does a pre-read existence probe
    - UpsertPosition(...) updates related rows in one transaction
    - InsertLogEvent(...) caches log_channels IDs
    - log pruning is thresholded instead of running on every insert
  - frontend log-page fetch cleanup in web/src/App.tsx to avoid redundant /api/v1/log/events reads and ignore stale overlapping responses
Remaining work
- Verify under real MQTT + log-page traffic that SQLITE_BUSY / database is locked errors are now rare enough.
- Decide only if measurements justify it:
  - synchronous=NORMAL
  - retry behavior for short busy windows
  - config exposure for SQLite tuning
Validation focus
- exercise concurrent ingest plus log-page reads against a file-backed DB
- confirm write-path correctness and log-pruning limits remain intact

## SQLite performance optimization status - Status - Implemented: - file-backed SQLite open hardening in `internal/persistence/sqlite/store.go` - `journal_mode=WAL` - `busy_timeout=5000` - `foreign_keys=ON` - `SetMaxOpenConns(1)` / `SetMaxIdleConns(1)` - startup logging of effective settings - regression coverage in `internal/persistence/sqlite/store_open_test.go` and `internal/persistence/sqlite/store_write_test.go` - write-path reductions: - `UpsertNode(...)` no longer does a pre-read existence probe - `UpsertPosition(...)` updates related rows in one transaction - `InsertLogEvent(...)` caches `log_channels` IDs - log pruning is thresholded instead of running on every insert - frontend log-page fetch cleanup in `web/src/App.tsx` to avoid redundant `/api/v1/log/events` reads and ignore stale overlapping responses - Remaining work - Verify under real MQTT + log-page traffic that `SQLITE_BUSY` / `database is locked` errors are now rare enough. - Decide only if measurements justify it: - `synchronous=NORMAL` - retry behavior for short busy windows - config exposure for SQLite tuning - Validation focus - exercise concurrent ingest plus log-page reads against a file-backed DB - confirm write-path correctness and log-pruning limits remain intact

skobkin referenced this issue from a commit

2026-02-28 23:16:30 +03:00

fix(persistence): harden database open settings to reduce SQLITE_BUSY (#18)

skobkin referenced this issue from a commit

2026-02-28 23:16:30 +03:00

fix(web): avoid duplicate log page fetches (#18)

skobkin referenced this issue from a commit

2026-02-28 23:16:30 +03:00

fix(persistence): optimize sqlite write paths for nodes and logs (#18)

skobkin commented

2026-03-12 00:24:45 +03:00

Author