Hi all,

I'm developing a project with hibernate and oracle and it works fine except for some strange blockings that appear performing select queries.

As far as I know Oracle allows all kind of selects to be performed even when doing update operations on the same rows. It looks like some update operations are blocking even the select queries.

I'm doing all my hibernate operations with the transaction object, I was wondering if this could be the problem I'm getting, maybe I should not use the transaction manager for doing select queries?

Thanks in advance

What transaction isolation level have you set in your Hibernate configuration file?

See "Using Oracle Lock Manager" in oracle documentation, it must help to find a problem (if this is related to oracle locks).

I'm using the default configuration for oracle and hibernate, by default oracle uses the READ COMMITED isolation level that should allow any select query even in the case of a row level block at the same time.

In hibernate I'm using the same isolation level, READ COMMITED.

Should I try with READ UNCOMMITED?

Could you post your hibernate.properties or hibernate.cfg.xml

I must admit that I haven't had any problems with Oracle locking tables on updates, and you appear to have it set at the weakest transactional isolation level.

Thats my hibernate.properties:

hibernate.query.substitutions true 1, false 0, yes 'Y', no 'N'

hibernate.dialect org.hibernate.dialect.Oracle9Dialect
hibernate.connection.driver_class oracle.jdbc.driver.OracleDriver
hibernate.connection.url jdbc:oracle:thin:@//192.168.1.12:1521/shoms
hibernate.connection.username ******
hibernate.connection.password ******

hibernate.query.substitutions yes 'Y', no 'N'

hibernate.connection.pool_size 2
hibernate.statement_cache.size 100

hibernate.show_sql = false
hibernate.cglib.use_reflection_optimizer = false
hibernate.connection.isolation = 2

level 2 is READ_COMMITED.

Ah I tell a lie, obviously you're not using the weakest transaction isolation level. Do you actually need to use READ_COMMITTED? If not, do you still have the same problems using the weakest level of NONE (or 1).

I found this article very useful for helping me with Oracle locking issues -
http://www.akadia.com/services/ora_locks_survival_guide.html

It may be worth using a tool to actually find out what kind of locks are being issued against your tables - Quest Software's TOAD is very good at showing session states.

Apologies, too little coffee this morning! The transaction isolation of 1 is READ_UNCOMMITTED, as you stated.

Give that a go if you can and see if it makes any difference.

Actually, READ UNCOMMITED should be all right for my purposes, so I'll give it a try.

Thanks a lot.