Read-only transactions

[andersio]

There are often cases where we need transaction isolation for a sequence of SELECT statements, but do not perform any writes.

While SQLite Write-Ahead Logging supports multiple concurrent readers with snapshot isolation e.g. via DEFERRED transactions, SQLDelight doesn't have such concept this at the framework level. So these use cases currently have to fallback to use a read-write transaction, as offered by transaction {} and transactionWithReturn {}.

The suggestion here is to:

1. introduce the read-only counterpart of the said two scope functions, persumably called readTransaction {} and readTransactionWithReturn {};

2. spawn a read-only transaction in drivers that can support it, or fallback to read-write transactions gracefully otherwise; and

3. runtime checks to prevent DML statements in read transactions, maybe via the readonly PRAGMA.

One thing I am not quite sure is how Android could support it though. requery/sqlite-android does offer support for DEFERRED transactions, but it is not part of the standard android.database.sqlite API surface on which the Android driver depends.

[AlecKazakova]

i dont want to add read-only APIs, they would functionally be the same. I'd much rather just encourage using SQL for these sorts of things since the sql files are dialect-specific but our runtime api is not:

beginTransaction: BEGIN DEFERRED TRANSACTION;
selectOne: SELECT ...
selectTwo: SELECT ...
endTransaction: END TRANSACTION;

and then you just call those methods instead of using the transaction API. This is already supported in the sqlite dialect afaik, but I haven't tried it myself so feel free to reopen if there are problems.

[andersio]

This does not work with today's Native Driver, which uses different database connections for select queries and mutation queries. BEGIN/END (or SAVEPOINT/RELEASE) is classified as mutation and is generated to use execute() instead of executeQuery(). So they have no effect on the connection used by queries.

[AlecKazakova]

is that because of https://github.com/cashapp/sqldelight/blob/master/drivers/native-driver/src/nativeMain/kotlin/com/squareup/sqldelight/drivers/native/NativeSqlDatabase.kt#L35 ? i suppose we could generate it to use executeQuery instead to avoid that

[andersio]

Even if we do that, this does not seem reliable in drivers or environments that do connection pooling, both the Android SQLite support library and probably the (future) Native Driver included.

Without the library being aware of the transaction scope, it is free to release the connection back to the connection pool after every executeQuery() call. The subsequent executeQuery() calls may then end up borrowing a connection different from the one which executed the opening BEGIN or SAVEPOINT.

[AlecKazakova]

i see, that makes sense. I'm still not convinced we have to introduce APIs to accomplish this. I'll think on it more. Also whats the issue with WAL that sqldelight doesnt understand?

[andersio]

Yeah, having a second thought about it, we may also reach a driver-level solution. More specifically, it remaps BEGIN * and END (or the savepoint equivalents) statements to call their internal transaction APIs instead. This is the approach used by Android SQLite Support library.

This way it would address also issues of read-write transactions written in SQL. I believe they do behave correctly on Android because of the aforementioned behaviour of the SQLite Support library. Meanwhile, they are subject to the class of issues described for in-SQL, read-only transactions above, when being executed on both JDBC driver and the Native driver:

• JDBC driver is not guaranteed to keep the same connection around, unless a driver-level transaction is opened.

• The current Native driver has exactly one writer connection, so connection alignment is not an issue. However, the mutual exclusion scope of the driver-level write lock does not cover the whole duration of the transaction, since there is not an open driver-level transaction. So any other thread could steal the lock & do their own thing, causing ACID violation.

• @kpgalligan's new native driver effort seemed to lean towards to drop the writer connection, in favour of a generic pool (#2125). So it will have issues in the same manner as the JDBC driver.

[AlecKazakova]

yep i like that idea