Re:Siren Call of SQLite on the Server
by kbolino on Tuesday February 18, 2025 @10:40AM
(#43090778)
There are no data corruption risks in general for multiple processes on the same local filesystem, but the lock obtained by write operations is pretty aggressive. Multiple readers are okay but a single writer will cause transient errors in all other concurrent read and write operations (edit: though you can turn this into just blocking instead with pragma busy_timeout).
[ Parent
| on HN ]
> Multiple readers are okay but a single writer will cause transient errors in all other concurrent read and write operations
While in WAL mode, readers and writers don't block one another.
> WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently. https://www.sqlite.org/wal.html
> Multiple readers are okay but a single writer will cause transient errors in all other concurrent read and write operations
They don't cause "transient errors", they cause transaction failures, which you should be handling by retrying the transaction anyway.
If you could retry it, then obviously the error was transient. I don't know what the issue is with me describing it that way.
But you generally won't have to write retry logic if you have the file open in only one place. The other issues that can arise, like constraint violations or no space left on disk, usually aren't transient and thus can't be fixed with retry anyway.