Updating in-process connections on write

(1) By Wout Mertens (wmertens) on 2021-10-08 10:57:32 [link] [source]

Hi,

I'm using the node-sqlite3 library in my program and I'm opening 2 connections to the same DB file in the same process, one read-only (RO) and the other for transacting (RW).

I'm doing this so that reads from the application don't see intermediate values during transactions, but maybe that's misguided. In any case it seems easier than wrapping everything in read transactions.

The problem I'm having is that sometimes, after completing a write transaction, the RO connection doesn't "see" the change. I would have expected connections from the same process to notify one another.

As a workaround, I'm setting pragma user_version in the transaction and then polling the RO connection (using a prepared query) until it sees the same user_version.

Is there a better way to do this?

Thanks,

Wout.

(2) By Gunter Hick (gunter_hick) on 2021-10-08 11:44:34 in reply to 1 [link] [source]

Sharing a connection between threads that use implicit transactions means that none of the threads can be sure when a transaction begins or ends. They share a common transaction that begins when any thread starts reading and ends when all threads are done with reading simultaneously. This is not transparent to the individual threads. E.g. if two threads alternate in reading, each starting a new SELECT before the other one ends it's SELECT, the transaction never commits, and thus never sees the result of a write transaction on the other connection.

Using a separate connection for each thread gives each thread control over it's own transaction. No transaction can see "intermediate" values if you properly BEGIN and END any write transactions.

(3) By Keith Medcalf (kmedcalf) on 2021-10-09 03:38:20 in reply to 1 [link] [source]

What do you mean by a read-only connection?

Do you mean a connection on which you have issued PRAGMA QUERY_ONLY=1; https://sqlite.org/pragma.html#pragma_query_only; the SQLITE_OPEN_READONLY flag was passed to sqlite3_open_v2 https://sqlite.org/c3ref/open.html; or, something else?

(4) By Wout Mertens (wmertens) on 2021-10-09 05:23:50 in reply to 3 [link] [source]

Yes, the latter, I pass SQLITE_OPEN_READONLY during open().

I wish there was something like PRAGMA really_check_for_change :)

(5) By Wout Mertens (wmertens) on 2021-10-09 05:29:46 in reply to 2 [link] [source]

Ok thank you, that validates me using two connections.

My writes are multi-step affairs (from event sourcing processing) so the transactions should be hidden from reads.

(6.1) By Keith Medcalf (kmedcalf) on 2021-10-09 05:32:07 edited from 6.0 in reply to 1 [source]

The problem I'm having is that sometimes, after completing a write transaction, the RO connection doesn't "see" the change.

That is correct. WHen using the WAL journal mode, a connection will not "see" any changes made to the database on a different connection until the specific sequence (1) commits, either implicitly or explicitly, the changes; and then (2) the original connection (the one that you want to see the changes) must BEGIN A TRANSACTION, either explicitly or implicitly, in order to see the changes to the database made on the other connection.

This means that, for a connection, if the following holds:

sqlite3_txn_state returns 0 https://sqlite.org/c3ref/txn_state.html

Then a SELECT issued against that connection will "see" all changes committed to the database BEFORE the above condition was tested.

((Actually, that would be all changes committed before the statement which starts the transaction, not from the time the condition was tested.))

(7) By Keith Medcalf (kmedcalf) on 2021-10-09 05:39:57 in reply to 4 [link] [source]

There is. PRAGMA data_version' https://sqlite.org/pragma.html#pragma_data_version

However, the data_version will not change if the request is not made when no transaction is in progress on that connection, even if a change was previously committed on another connection.

(8) By Wout Mertens (wmertens) on 2021-10-09 07:19:18 in reply to 7 [link] [source]

I'll set up a test rig and experiment. Possibly just reading data_version might be enough then.