Oracle Read Only Transactions

The Oracle database supports read only transactions. They give you read consistency including repeatable reads. This means you only see changes that were committed before the read only transaction was started. You do not see any changes that are committed by other transactions while the read only transaction is running. In addition the only allowed queries are SELECT statements without the FOR UPDATE clause. Read only transactions can be a very useful feature as they give you a consistent view of the database across several queries.

JDBC

Unfortunately read only Oracle database transactions are hard to use with JDBC. Calling Connection.setReadOnly(true) with the 12c driver no longer establishes a read only transaction as it did the with 9i and 10g drivers (I am unsure about the behavior of the 11g drivers). This is an intentional change as establishing a read only transaction is not the purpose of the method.

Spring

Spring supports the concept of read only Spring transactions, typically used with @Transactional(readOnly=true). When read only transactions Spring are used together with the DataSourceTransactionManager or the HibernateTransactionManager a call to Connection.setReadOnly(true) is made. This doesn’t happen with the JtaTransactionManager typically used in Java EE environments. But as we said before because Connection.setReadOnly(true) does not give you a read only transaction, read only Spring transactions do not give you read only Oracle database transactions.

Summary

If you want a read only Oracle database transaction you have to call

SET TRANSACTION READ ONLY;

If you want to avoid this but still want read consistency and repeatable reads with Oracle you have to use the serializable isolation level (which theoretically is only snapshot isolation).

Update

This has changed in Spring 4.3.7.