When committed changes become visible to connections that ATTACH?
(1) By Vlad Voinov (vvoinov) on 2020-05-12 16:18:56 [source]
Greetings! I have one sqlite database per process running on the same physical machine where each process can update only its own database. All databases are in WAL mode and stored on the same local file system. Each process is different, has its own responsibilities and process-specific database schema. Previously, when one process needed to read data from another process, it would make an RPC call. We are replacing such RPCs with an ability to attach other processes' databases and query "remote" tables directly, including queries that join locally-owned tables and tables from another process (this is done using temp views). With multiple threads sharing a single read-only connection where we first run the ATTACH statement and possibly one or more temp view create statements, are there any guarantees when committed transactions performed by the owning process become visible to readers in another process? So far, it appears as if so as long as there are overlapping reader threads executing their sequence of prepare() and series of step() calls, new readers that start their work even seconds after the remote process commits its updates, can still be getting old data from "remote" tables as if no transaction ever happened. Is that correct? If so, how could one ensure that updates committed by the owning process are visible to readers from another process? Other than creating a new connection every time. FWIW, read connections are created using private cache + full mutex option, and all databases are in WAL mode. Thanks!
(2) By Richard Hipp (drh) on 2020-05-12 16:43:16 in reply to 1 [link] [source]
On a single database connection, when a query begins, it starts a read transaction. Other queries that come along before the first query ended piggy-back onto the original read transaction. This continues, holding the transaction open, until all queries are complete.
While a read-transaction is active, SQLite sees a single consistent snapshot of the data. Changes added by other database connections after the read transaction started are invisible to the read transaction. This is a feature, not a bug.
If you want to see all the latest changes entered by other database connections, you have to end your read transaction and start a new one.