You are still correct for network filesystems, but there are no issues opening multiple connections on the same host (even across OCI container boundaries).
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
They don't cause "transient errors", they cause transaction failures, which you should be handling by retrying the transaction anyway.
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.