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.
#11234 Rollback of transaction participating in nested transaction should not enforce rollback of global transaction
#19820 Database.SQL_SERVER should set more recent Hibernate dialect with Spring 4.3.x
The text was updated successfully, but these errors were encountered:
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.
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.