Hi,

We found that when we use Criteria API to retrieve persisted objects from database, the database is issuing a Shared lock on the Page of the table where the Objects are persisted.
We are wondering if there is a way to avoid this. We need to read object for readonly, not producing shared lock.

We are using Hibernate 3.2.6 and SQL Server 2005 Database.
We found that on Query interface there is a setReadOnly(true) method, but this method is not present on Criteria API. And also there is not clear docs about what this method provides (is this really avoid shared locks?)

Thank you in advance.
Daniel

This problem seems to be more a SQL Server behavior than an Hibernate issue. From what we were researched SQL Server do a Shared lock on read. In SQL Server if you define a cursor you could specified the FOR READ ONLY clause to avoid this, I'm not sure if there is an equivalent way of doing this from JDBC. If I could find this, maybe we could extend SQLServerDialect to provide a way of reading with no lock.

In the mean time we also found that if we define ISOLATION LEVEL to DIRTY READ, we can avoid locking, but this is not a very good solution, because we can't find a way of doing this on a session level, we have to do it at configuration level so every session will produce dirty read records.

Well that's what we could found from the moment, and let me say that it sounds incredible that nobody is run into this problem, which creates serious problems of performance in high-traffic databases, I hope that someone could confirm or deny our research, and tell us if we are doing something wrong.

Regards
Daniel