Feature request: TransactionBuilder.setMode()

[rhashimoto]

Some SQL dialects have different ways to start a transaction for write or read-only. I'm writing a driver for SQLite, which has BEGIN DEFERRED and BEGIN IMMEDIATE. Postgresql and MySQL have START TRANSACTION READ ONLY and START TRANSACTION READ WRITE. It would be great if an implementation of Driver.beginTransaction() could get enough information to choose among these variants.

I'm suggesting a new method TransactionBuilder.setMode(mode), where mode can be 'read-only'|'read-write'. This optional value would be passed to the driver in a new mode property on TransactionSettings.

In SQLite, the value of using BEGIN IMMEDIATE for a write transaction is that potential deadlock will be detected by attempting to acquire an exclusive lock immediately. This would allow the driver to implement backoff and retry in beginTransaction() instead of requiring handling at the application level.

Note that I'm only requesting a way for drivers to get this information, not necessarily for any currently provided drivers to add support.

Thanks for your consideration!

[igalklebanov]

Hey 👋

We're able to add things that are optional for existing drivers. e.g. savepoint methods in the upcoming ControlledTransaction feature are optional, and we don't expect community drivers to immediately support savepoints.

I'm not sure about the naming of this method.

PostgreSQL docs refer to them as "modes", together with isolation levels.

START TRANSACTION [ transaction_mode [, ...] ]

where transaction_mode is one of:

    ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
    READ WRITE | READ ONLY
    [ NOT ] DEFERRABLE

The DEFERRABLE transaction_mode is a PostgreSQL language extension.

The SQL standard requires commas between successive transaction_modes, but for historical reasons PostgreSQL allows the commas to be omitted.
https://www.postgresql.org/docs/current/sql-start-transaction.html

MySQL docs refer to them as "characteristics".

START TRANSACTION
   [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

https://dev.mysql.com/doc/refman/8.4/en/commit.html

SQLite docs refer to them as "behaviors".

The default transaction behavior is DEFERRED.
https://www.sqlite.org/lang_transaction.html#deferred_immediate_and_exclusive_transactions

Also, putting all of these together in one method will be confusing for users:

e.g. PostgreSQL has the deferrable option, and SQLite has the deferred option.

It seems that, unlike with isolation levels, the specs here diverge more.

[rhashimoto]

How about .setReadOnly(value: boolean), so it only specifies (optionally) whether the transaction is read-only or read-write? This would sidestep terminology differences.

Or what about just adding .settings(value: TransactionSettings)? This would provide a general way to communicate with custom drivers.

[igalklebanov]

This is unintuitive. Naming has to be close to spec.

[igalklebanov]

PostgreSQL docs refer to them as "access modes":

The transaction access mode determines whether the transaction is read/write or read-only. Read/write is the default.
https://www.postgresql.org/docs/current/sql-set-transaction.html

MySQL docs also refer to them as "access modes":

SET [GLOBAL | SESSION] TRANSACTION
    transaction_characteristic [, transaction_characteristic] ...

transaction_characteristic: {
    ISOLATION LEVEL level
  | access_mode
}

level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}

access_mode: {
     READ WRITE
   | READ ONLY
}

https://dev.mysql.com/doc/refman/9.1/en/set-transaction.html#set-transaction-access-mode

This pushes us strongly towards

setAccessMode(accessMode: 'read write' | 'read only')

[igalklebanov]

PostgreSQL's deferrable and not deferrable "transaction modes" (a word used to also describe isolation levels and "access modes") seem to belong with SQLite's deferred, immediate, exclusive "behaviors".

PostgresSQL docs also refer to them as just "properties"

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. When all three of these properties are selected for a transaction, the transaction may block when first acquiring its snapshot, after which it is able to run without the normal overhead of a SERIALIZABLE transaction and without any risk of contributing to or being canceled by a serialization failure. This mode is well suited for long-running reports or backups.
https://www.postgresql.org/docs/current/sql-set-transaction.html

This pushes us towards

setBehavior(behavior: 'deferrable' | 'not deferrable' | 'deferred' | 'immediate' | 'exclusive')

Which is not ideal - wish PostgreSQL (the popular one) had a proper name for this, but its the lesser evil.