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
