Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.
“To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine [and OS instance]. Thus, WAL mode does not work on a network filesystem.”
“It is not possible to change the page size after entering WAL mode.”
“In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index.”
To backup you should use the `vacuum into` statement. That can be safely run from another process, as it takes a read lock and doesn't block writers when using the WAL mode.
When backing up a SQLite database that is in running use, you should never use `.backup`. That command will restart the backup from scratch every time the database is written to, so if you're backing up a big database with a lot of periodic writes the backup process may never complete.
You can sometimes use the C backup API from the same process that is writing to the database, since the C backup API is aware of writes _from the same process_. It will still have to restart the backup procedure if writes from another process interfere.
It is not safe to use network file systems (NFS, SMB, or otherwise) in WAL mode, since they cannot access the shared memory.
There are a few more limitations that I can dig up.
https://sqlite.org/lang_attach.html
Transactions involving multiple attached databases are atomic, assuming that the main database is not ":memory:" and the journal_mode is not WAL. If the main database is ":memory:" or if the journal_mode is WAL, then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not.