Row level lock in Hibernate

[Jaikiran Pai]

Is there any way by which we can configure Row level locking of tables while using Hibernate. Currently, in our application, Hibernate generates table level lock queries. Our's is a J2EE application on Jboss-3.2.3 and Hibernate 2.1.3?

Thank you.

[Paul Sturrock]

Hibernate doesn't lock tables. All concurrent access issues are delegated to the RDBMS. How are you getting Hibernate to lock tables?

[Jaikiran Pai]

I have NOT mentioned explicitly any locking configurations in Hibernate. But whenever queries are fired through Hibernate i see logs similar to the one below:

[B]2005-12-09 16:08:17,
645 DEBUG [net.sf.hibernate.SQL] LOCKING TABLE SM_TABLE_RUN FOR ACCESS [/B] select smTABLEr0_.TABLE_ID as TABLE_ID__,
 smTABLEr0_.TABLE_VERSION_ID as TABLE_2___,
 smTABLEr0_.RUN_ID as RUN_ID__,
 smTABLEr0_.TABLE_ID as TABLE_ID0_,
 smTABLEr0_.TABLE_VERSION_ID as TABLE_2_0_,
 smTABLEr0_.RUN_ID as RUN_ID0_,
 smTABLEr0_.UPDATE_DTTM as UPDATE_D4_0_,
 smTABLEr0_.RUN_TYPE_CD as RUN_TYPE5_0_,
 smTABLEr0_.UPDATE_USER as UPDATE_U6_0_,
 smTABLEr0_.RUN_COUNT_ID as RUN_COUN7_0_,
 smTABLEr0_.RUN_ID as RUN_ID0_,
 smTABLEr0_.TABLE_ID as TABLE_ID0_,
 smTABLEr0_.TABLE_VERSION_ID as TABLE_2_0_ from SM_TABLE_RUN smTABLEr0_ where smTABLEr0_.TABLE_ID=? and smTABLEr0_.TABLE_VERSION_ID=? 

[Edited code to improve formatting - Paul Sturrock]
[ December 14, 2005: Message edited by: Paul Sturrock ]

[Jaikiran Pai]

Is there any way this can be avoided?

[Paul Sturrock]

Hmm. Can you post the code that generates this SQL? Also which Dialect are you using?

[Jaikiran Pai]

We are using Teradata dialect :

 (hibernate.dialect=net.sf.hibernate.dialect.TeradataDialect) 

As far as the code is concerned, we have created a hibernate object which is mapped to this SM_TABLE_RUN. Then we just invoke the get method on our persistence manager which invokes Hibernate session.get method.
This behavior is observed for all sort of database operations (insert,update,delete etc...) and not just select operation.

P.S.: Hibernate is being used as a MBean in our application

[Jaikiran Pai]

Even a HQL like:

from com.dbdata.hibernateobject.CmProgram  

is issuing the following lock statements:

2005-12-09 16:07:24,458 DEBUG [net.sf.hibernate.SQL] LOCKING TABLE CM_PROGRAM FOR ACCESS select 
cmmarketin0_.PROGRAM_ID as MARKETIN1_, cmmarketin0_.PROGRAM_VERSION_ID as MARKETIN2_, 
cmmarketin0_.UPDATE_DTTM as UPDATE_D3_, cmmarketin0_.OWNER_DIVISION_ID as OWNER_DI4_, cmmarketin0_.NAME 
as NAME, cmmarketin0_.DESCRIPTION as DESCRIPT6_, cmmarketin0_.STATUS_CD as STATUS_CD from CM_PROGRAM 
cmmarketin0_  

[Paul Sturrock]

Teradata? That is not a supported database as far as I'm aware. You might want to speak to whoever wrote your Dialect, they will probably have more of a clue (since it wasn't the Hibernate developers). Also, doesn't Teradata use a serializable transaction isolation level by default?
[ December 14, 2005: Message edited by: Paul Sturrock ]

[Jaikiran Pai]

Thanks Paul, Will try to get in touch with the guy who coded the dialect.

[Jaikiran Pai]

Originally posted by Paul Sturrock:

Also, doesn't Teradata use a serializable transaction isolation level by default?

I have been informed that Teradata does NOT use a serializable transaction isolation level

[Paul Sturrock]

Hmm again. Are you explicitly applying a LockMode to the Session when you open it? I understood that it is posisble to add pessimistic locking to a row with this, but I've never heard of it locking a table.

[Scott Johnson]

Calling LOCKING TABLE XXX FOR ACCESS is telling Teradata that dirty or phantom reads (TRANSACTION_READ_UNCOMMITTED) are acceptable.

This is a good thing [for concurrency].

You are declaring that others can access and update the table while you are selecting from it.

The valid lock levels are Exclusive, Write, Read and Access.
[ December 14, 2005: Message edited by: Scott Johnson ]