Can someone tell me how I go about using lock hints with SQL Server 2000 and Hibernate 2.1.4? I did some searching through the forum and through the CVS logs and, from what I gather, it appears that lock hints are not suppported in 2.1.4 (see http://opensource.atlassian.com/project ... wse/HB-760). Is this true? If so, what is the best road to follow with respect to applying the patch in bug Hibernate bug 760? I've tried several other options, including setting the readOnly option of the Session's connection to true as well as setLockMode on the Query but I'm thinking none of those options worked because Hibernate doesn't support locking hints in version 2.1.4. Any help would be fantastic. Thanks.

Actually, it's support for the NO LOCK SQL Server clause that I need. Has anyone out there been able to support the NO LOCK clause in the SELECT statements with SQL Server and Hibernate? Thanks.

I tried using lock hints with SQL Server 2000 and Hibernate 3.1.2. I tried setLockMode on HQL Query but hibernate fails to append lock hint in HQL queriy. I upgraded to version 3.2.6. The SQLServerDialect contains a method appendLockHint(LockMode mode, String tableAlias) but this code is not called up while converting HQL queries to SQL.

It seems that hibernate developers know about the problem and tried to fix it in the previous versions but they seem to have lost the patch. The link below points to a JIRA which describes it.

http://opensource.atlassian.com/project ... se/HB-1532

Also applyLocksToSql() of the dialect class (SybaseDialect) seem to apply lock hint only in case of upgrade. In case of read it doesn't seem to apply lock hint. But this only seems from the code which doen't actually runs.

What I thought is to use

query.setLockMode(LockMode.READ, tableAlias) or query.setLockMode(LockMode.UPGRADE, tableAlias) and see WITH (READPAST) or WITH (updlock, rowlock) appended to my query when it runs.

Or any other way to insert lock hint in HQL queries

Also while debugging through hibernate 3.2.6 source code I made changes to applyLocksToSql() to append lock hint when query has LockMode.READ.
I found that for select queries, it applies lock hint only when we select the entire object. In case of projections, it fails to apply lock hint.
E.g., for “from Broker b” it works fine but for “select b.brokerID from Broker b” it doesn’t.

Check out the Hibernate JavaDoc for LockMode. It might shed some light on what you might to do better optimize your particular solution.



http://www.hibernate.org/hib_docs/v3/api/org/hibernate/LockMode.html

I already have looked at the doc and am using the same strategy to acquire a pessimistic lock. But the code itself is not supporting the locking as was expected by me. If you go through my description above you would get an idea of what all combinations I used to get this working, but nothing seemed to have helped.