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
