DB driver that supports read-only transaction

[blueboy]

Could someone tell me which DB and JDBC DB drivers support Spring's read-only transaction (conn.setReadOnly(true)?) if I use DataSourceTransactionManager?
I'm currently using MS SQL + JTurbo + IBatis, but the read-only transaction does not throw Exception when I do DB update.
Thanks.

[blueboy]

Could someone give me a hint?
Thanks.

[miluch]

If you take a look at java.sql.Connection javadoc you can find that setting readonly is just a hint for a driver (due to optimization) that can be ignored (as Oracle does) by DB.

Do you need to prevent issuing DML by setting readOnly ?

[blueboy]

If you take a look at java.sql.Connection javadoc you can find that setting readonly is just a hint for a driver (due to optimization) that can be ignored (as Oracle does) by DB.

Do you need to prevent issuing DML by setting readOnly ?

Thanks for answering
Yes...This is a requirement to prevent accidental DML in read-only transaction.
I know HibernateTransactionManager can throw exception, but unfortunately we are using IBatis...
Do you know any alternative solution? Thanks.

[miluch]

It is not completely right since HibernateTransactionManager only sets session flush mode to never but in the same transaction you can still modify your data using jdbc.

Solution on your problem mainly depends how you access your db and pn which level you wanna to protect it. On oracle you have additional isolation level :"read only" that really fits your needs.
But i would need to think how to enable this using Spring.
On the other side maybe Ibatis has some kind of hook you can use. In hibernate you have Interceptor (http://www.hibernate.org/hib_docs/v3...terceptor.html). Its onPrepareStatement method process all sqls. The easiest would be to find similar hook in Ibatis.

In the end you can think of creating proxy around your datasource to retrieve connection that can prohibits DML within read only transactions.

[blueboy]

It is not completely right since HibernateTransactionManager only sets session flush mode to never but in the same transaction you can still modify your data using jdbc.

Solution on your problem mainly depends how you access your db and pn which level you wanna to protect it. On oracle you have additional isolation level :"read only" that really fits your needs.
But i would need to think how to enable this using Spring.
On the other side maybe Ibatis has some kind of hook you can use. In hibernate you have Interceptor (http://www.hibernate.org/hib_docs/v3...terceptor.html). Its onPrepareStatement method process all sqls. The easiest would be to find similar hook in Ibatis.

In the end you can think of creating proxy around your datasource to retrieve connection that can prohibits DML within read only transactions.

Thanks for your help.
I'll continue my research based on your suggestions.