[kbolino]

The historical answer has generally been: it doesn't (at least, not well). The traditional recommendations were not to open multiple connections to the same file, and especially not to share the same file over a network filesystem. This remains the general advice, as far as I can tell, but it may be out of date against some newer features (like WAL mode) and better filesystems (like NFSv4).

[hruk]

You are still correct for network filesystems, but there are no issues opening multiple connections on the same host (even across OCI container boundaries).

[kbolino]

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).

[infamia]

> 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

[gwd]

> 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.

[kbolino]

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.