how to set query to be read uncommited?

Question

with hibernate, how can I set a query to be read uncommitted? I don't want this to be a global setting, just want to do it on a per query basis.

Answer 1

by default, statements are not committed in hibernate until you do it explicitly (or the connection is returned to the pool and the driver happens to do a commit for you, which isn't guaranteed). so, it seems to do what you want already.

you can also just rollback the current transaction if you want to nullify the statements made since the beginning of the transaction.

in mysql, you could do something like:

set session transaction isolation level read uncommitted

or use the method axtavt suggested,

call getTransactionIsolation() before the query to get the current mode so you can put it back after the stmt if you like.

Answer 2

Have you tried session.connection().setTransactionIsolation(...)?

P.S. For modern MVCC-based DBMS you don't need to use "read uncomitted" to avoid locks. Some DBMS even don't have this isolation level implemented.

Answer 3

Set the transaction isolation level:

session.connection().setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

Answer 4

The Hibernate API itself provides no way to do this programmatically, that I'm aware of. The docs do say that you can specify the isolation level as a configuration property:

hibernate.connection.isolation - Sets the JDBC transaction isolation level. Check java.sql.Connection for meaningful values

But of course this applies to the whole SessionFactory, not just to specific queries.

It's tempting to fiddle with the underlying java.sql.Connection isolation level, but I'd be very careful about doing that sort of thing, you run the risk of conflicting with hibernate's own locking strategies, which use the JDBC isolation level to determine which LockMode will be used. If you change this directly on the Connection, you might get odd results. You may not have a choice, of course, but be careful.

A "better" solution would be to use Spring's Hibernate Transaction API, which allows you to decouple the transaction semantics like isolation level from the Hibernate API completely, in a predictable and reliable way.