https://www.sqlite.org/wal.html#concurrency
"But for any particular reader, the end mark is unchanged for the duration of the transaction, thus ensuring that a single read transaction only sees the database content as it existed at a single point in time." Read transactions see one version of the entire database for their whole transaction. They won't see any changes made by other transactions after they start. I refer you again to the previously linked transactions page https://www.sqlite.org/lang_transaction.html "begin;" or "begin deferred;" doesn't do anything until you next access a file, at which point it will get a lock on the file. So if you want to control specifically when your read transaction _effectively_ starts you have to actually read something from the file to start it, at which point you'll get your lock, get your end mark in the WAL, and effectively freeze your view of the database. And a point that this is of course with an explicitly declared transaction started with a begin statement. If you're in autocommit mode then each of your selects is its own little transaction separate from any other statements before it or after it. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of test user Sent: Tuesday, July 30, 2019 3:01 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Explicit "read transaction" with journal_mode=WAL. Thanks David, `SELECT 1` = rows 0 was a mistake in the example. How sure are you that "any SELECT that reads from the DB file starts a read transaction"? Does the read transaction read from a snapshot of the entire DB, or are only specific tables in the read snapshot? On Tue, Jul 30, 2019 at 7:14 PM David Raymond <david.raym...@tomtom.com> wrote: > To get the read lock you're going to need to read something from the > database file. > > I think this page is your best bet: > https://www.sqlite.org/lang_transaction.html > > "Transactions can be deferred, immediate, or exclusive. The default > transaction behavior is deferred. Deferred means that no locks are acquired > on the database until the database is first accessed. Thus with a deferred > transaction, the BEGIN statement itself does nothing to the filesystem. > Locks are not acquired until the first read or write operation. The first > read operation against a database creates a SHARED lock and the first write > operation creates a RESERVED lock. Because the acquisition of locks is > deferred until they are needed, it is possible that another thread or > process could create a separate transaction and write to the database after > the BEGIN on the current thread has executed..." > > So after a "begin deferred" you have to actually do something that > requires file access in order to get the shared lock/start your read > snapshot on the file. So if you want to get that shared lock/read snapshot > you can always do something like a select from sqlite_master. "select 1;" > doesn't need to access the file to complete, so it doesn't take out the > shared lock (though it should be returning 1 row, are you sure it's 0?) > > > > -----Original Message----- > From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On > Behalf Of test user > Sent: Tuesday, July 30, 2019 1:45 PM > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Subject: [sqlite] Explicit "read transaction" with journal_mode=WAL. > > Hello, > > How can I start a "read transaction" from BEGIN? > > > I am using `journal_mode=WAL`. > > What I am trying to do: > > From the first `BEGIN` that returns `SQLITE_OK`, all SELECTs read from the > same snapshot/point in time. > > > The issue is that its hard to tell if I reading from a read snapshot (where > any successful commit on other connections since the reads BEGIN are > ignored). > > When is a read transaction started? > > > As an example, connection A and B: > > ``` > run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); > > run(B, "BEGIN"); > run(B, "SELECT * FROM t1"); = 0 rows > > run(A, "BEGIN"); > run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); > run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); > run(A, "COMMIT"); > > run(B, "SELECT * FROM t1"); = 0 rows; READ TRANSACTION started > ``` > > ``` > run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); > > run(B, "BEGIN"); > run(B, "SELECT 1"); = 0 rows > > run(A, "BEGIN"); > run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); > run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); > run(A, "COMMIT"); > > run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started > ``` > > > ``` > run(A, "CREATE TABLE t1(a PRIMARY KEY, b);"); > > run(B, "BEGIN"); > > > run(A, "BEGIN"); > run(A, "INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4)"); > run(A, "INSERT INTO t1 (a, b) VALUES (5, 6), (7, 8)"); > run(A, "COMMIT"); > > run(B, "SELECT * FROM t1"); = 4 rows; READ TRANSACTION *NOT* started > ``` > > > > https://www.sqlite.org/isolation.html > > Quote: "BEGIN IMMEDIATE command goes ahead and starts a write transaction" > > This is the only page where I can find a mention of the idea of "read > transaction" and "write transaction". > > > BEGIN IMMEDIATE allows the explicit start of a "write transaction". > > Does an API exist for a "read transaction"? > > Thanks > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
