Just like in SQLite, in Dqlite all SQL statements are executed within a transaction which is started either implicitly or explicitly. Explicit transactions are the ones started with the BEGIN statement. Implicit transactions are started when executing a single statement without having started an explicit transaction with BEGIN. Explicit transactions get committed using the COMMIT statement, and implicit transactions are committed automatically.
A transaction always starts as a read transaction and gets upgraded to write transaction only once it actually starts to make changes to the database (e.g. via INSERT or UPDATE ).
At any time any number of read transactions can be active, but at most one write transaction is allowed. If a read transaction tries to upgrade to write transaction while a write transaction is in progress, an error will be returned.
Transactions are fully isolated and if a read transaction is started before or during a write transaction it will observe a snapshot of the database taken before the write transaction started.
More precisely, Dqlite transactions can be serialized, however they cannot be linearized. They are serializable because Dqlite allows at most one writer at a time and readers always observe a consistent (i.e. committed) snapshot of the database, therefore transactions can’t “interleave”. They are not linearizable because there’s no guarantee that a read transaction will always observe a snapshot of the most recently committed version of the database that existed when the read transaction started. In other words, stale reads are allowed.
However, stale reads are rare in practice. They can happen only in case a read transaction is started on a leader that was disconnected from a majority of voting nodes but that didn’t yet realise it and didn’t yet step down from leader. If in the meantime another leader was elected by the rest of the voting nodes and that leader committed a write transaction before the old leader started serving the read transaction, then that read transaction might observe stale data, since read transactions are served by a leader without making sure that it is actually still the leader and hence no write transaction might have happened before (so called “quorum reads”).
In case a stale reads actually occurs, the data observed by the read transaction can’t be arbitrarily old. A disconnected leader will take at most an election timeout to realise it’s not leader anymore, therefore the data it serves can be at most a few hundred milliseconds old (with default Dqlite settings).