Proposal: add Connection.setReadonly(boolean)

[meltsufin]

Cloud Spanner supports read-only transactions, which is one of its 3 major transaction types. We would like to support this in our R2DBC driver for Spanner.

Instructing users to just downcast to our own SpannerConnection whenever they need to set transaction type doesn't seem very user friendly.

The other alternative we're considering is using the isolation levels setting, but that doesn't fit cleanly into the transaction mutability concept.

Note that JDBC has a Connection.setReadonly(boolean) method. So, Cloud Spanner is not the first one to think of something like this, and it could benefit other implementations as well.

Ref: GoogleCloudPlatform/cloud-spanner-r2dbc#40

[mp911de]

Related: #2, #14.

Based on the feedback we received in #2, we suggest defaulting on ConnectionFactory level (i.e. by configuring ConnectionFactoryOptions) so connections can be created with a default behavior regarding transactions.

A few vendors (Postgres, MariaDB, MySQL, Firebase) support read-only flags on their connection with most drivers having the restriction that transaction mutability can be only set before starting the transaction and not during the transaction itself.

An overloaded method beginTransaction() accepting TransactionMutability or TransactionOptions could provide a variant for client libraries to pass on transaction options.

/cc @davecramer

[meltsufin]

Spanner also has the requirement that the transaction type cannot be changed during an active transaction. However, the same connection can be used to start transactions with different mutability settings. Given that the connections are usually pooled, it would be inelegant to maintain two different pools when the setting is at the ConnectionFactory level.

So it seems that the best compromise/workaround for us at the moment is to implement both, the connection factory setting, and the beginTransaction(boolean) with downcasting to SpannerConnection.

Perhaps, we're the only R2DBC driver so far that would like to reverse the decision in #14, but here it is FWIW.

[dzou]

Just to add another note on the transaction type discussion: If we offered some method of providing a notion of "transaction type" to the Connection class, it would also help solve a Spanner idiosyncrasy in which there are multiple modes in which queries can be run.

We could make several modes in which a connection can be active:

• Read-only
• read-write
• partitioned DML/Batch DML
• DDL

The tricky thing is that Spanner APIs offer different endpoints that the user must call depending on if you execute a DDL, Batch DML, or Read/write SQL statement. The Spanner JDBC driver currently must parse the statement to determine the correct Spanner endpoint to call. Allowing for some method of passing additional info to the connection would allow the user to choose which kind of queries they intend to execute in a connection.

See context: GoogleCloudPlatform/cloud-spanner-r2dbc#92, GoogleCloudPlatform/cloud-spanner-r2dbc#88

[mp911de]

Related: I submitted a PR (#159) for extensible transaction definitions for discussion that is intended to address the needs for specifying transactional attributes in an extensible way.

[mp911de]

Fixed via #159.