Support for read-only transactions with Oracle 12c JDBC driver [SPR-15210]

[spring-projects-issues]

Philippe Marschall opened SPR-15210 and commented

Previously (with the 9i and 10g) Oracle JDBC driver read only Spring transactions resulted in read only Oracle transactions. With the 12c Oracle JDBC driver this is no longer the case (I'm unsure about the behavior of the 11g driver).
Read only Oracle transactions are a nice feature because the give you read consistency including repeatable reads.
Read only Spring transactions used to create read only Oracle transactions because the DataSourceTransactionManager calls Connection.setReadOnly(true). With old versions of the Oracle JDBC driver this used to create a read only transaction. This was a bug in the driver that was later corrected. The purpose of the Connection.setReadOnly(true) is to create a read only connection, not a read only transaction.

Unfortunately JDBC offers no portable way to create a read only transaction.
Oracle, PostgreS and MySQL all require a variant of

SET TRANSACTION READ ONLY

with possibly an isolation level. This can also be done in a START TRANSACTION.

We considered making a subclass of DataSourceTransactionManager but the requirement to register a DataSourceTransactionObject which is private makes this tricky. Also DataSourceUtils.prepareConnectionForTransaction does not offer an easy way to plug in vendor specific behavior.

---

Issue Links:

• Rollback of transaction participating in nested transaction should not enforce rollback of global transaction [SPR-6568] #11234 Rollback of transaction participating in nested transaction should not enforce rollback of global transaction
• Database.SQL_SERVER should set more recent Hibernate dialect with Spring 4.3.x [SPR-15255] #19820 Database.SQL_SERVER should set more recent Hibernate dialect with Spring 4.3.x

[spring-projects-issues]

Philippe Marschall commented

To be clear that Connection.setReadOnly(true) no longer creates a read only transaction is Oracle driver version specific and not Oracle database version specific. If you check the Oracle driver compatibility matrix you can combine different driver and database versions.

[spring-projects-issues]

Juergen Hoeller commented

I've added a protected prepareTransactionalConnection template method to DataSourceTransactionManager, invoked right after the auto-commit switch. In contrast to the Connection.setReadOnly call, this doesn't happen before but rather immediately after transaction begin, as it is intended for modifying semantics of the current transaction (and not connection-level state that the JDBC driver does not allow to be modified during a transaction).

Additionally, there's a dedicated enforceReadOnly flag on DataSourceTransactionManager now: Setting this to true makes our default prepareTransactionalConnection implementation send an explicit "SET TRANSACTION READ ONLY" statement to the database. This seems worth an explicit option since it is rather commonly understood, even if the primary target is an Oracle database with a recent Oracle JDBC driver.

[spring-projects-issues]

Philippe Marschall commented

Outstanding, thank you.