How do you control locking with Hibernate on MSSQL DB?

Currently I'm using native SQL:

<sql-query name="missingDataQuery">
<return alias="md" class="com.generalynx.ecos.data.MissingData"/>
SELECT {md.*}
FROM missingdata {md}
WITH(rowlock, holdlock)
WHERE {md}.egidasource_id = :sourceId
AND {md}.status = :status
</sql-query>

Is there a way to do this 'object oriented'?
Is there a way to control which hint goes into 'what' clause?

Since I have this problem:
I have scheduled data getting service which starts in n threads. And all of these threads are reading/inserting/updating the same table (missingdata). No service/thread reads the same data (row) = intersection of read collections in each thread is empty.

In each thread I start a transaction (which last for 2-3 min :-(), reading short (< 10) list of MissingData, making each of them 'dirty' by changing property and inserting a few new MissingData.
Before I had problems with updating - so I locked each read row (see query). But now I get a deadlock while inserting.

Is there a way to control locking on inserts?

What is to do here?

Thanx and rgds,
Ales

-----------------------------------------------

Hibernate version: 2.1.6

Name and version of the database you are using:
MSSQL 2000

Driver:
jdbc.driverClassName=net.sourceforge.jtds.jdbc.Driver

hibernate.dialect=net.sf.hibernate.dialect.SQLServerDialect
hibernate.jdbc.use_scrollable_resultset=false
hibernate.jdbc.batch_size=25
hibernate.jdbc.batch_versioned_data=true

Mapping
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="com.generalynx.ecos.data.MissingData"
table="missingdata"
>

<id
name="id"
column="missingdata_id"
type="int"
unsaved-value="0"
>
<generator class="native">
</generator>
</id>

<property
name="date"
type="java.util.Date"
column="day_date"
/>

<property
name="number"
type="int"
column="number"
/>

<many-to-one
name="source"
class="com.generalynx.ecos.data.Source"
cascade="none"
outer-join="auto"
column="egidasource_id"
update="false"
/>

<property
name="status"
type="int"
column="status"
/>

</class>

<sql-query name="missingDataQuery">
<return alias="md" class="com.generalynx.ecos.data.MissingData"/>
SELECT {md.*}
FROM missingdata {md}
WITH(rowlock, holdlock)
WHERE {md}.egidasource_id = :sourceId
AND {md}.status = :status
</sql-query>

</hibernate-mapping>

Exception trace:
Ecos ERROR [03-11-2004 11:55:10] JDBCException.java - could not insert: [com.generalynx.ecos.data.MissingData]
java.sql.SQLException: Transaction (Process ID 66) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2217)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:1696)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:522)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:386)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL(JtdsStatement.java:369)
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeUpdate(JtdsPreparedStatement.java:382)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:528)
at net.sf.hibernate.persister.EntityPersister.insert(EntityPersister.java:432)
at net.sf.hibernate.impl.ScheduledIdentityInsertion.execute(ScheduledIdentityInsertion.java:29)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:932)
at net.sf.hibernate.impl.SessionImpl.doSave(SessionImpl.java:857)
at net.sf.hibernate.impl.SessionImpl.saveWithGeneratedIdentifier(SessionImpl.java:775)
at net.sf.hibernate.impl.SessionImpl.save(SessionImpl.java:738)
at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:1388)
at org.springframework.orm.hibernate.HibernateTemplate$13.doInHibernate(HibernateTemplate.java:320)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:176)
at org.springframework.orm.hibernate.HibernateTemplate.saveOrUpdate(HibernateTemplate.java:317)
at com.generalynx.ecos.data.dao.HibernateBasicDAO.saveMissingData(HibernateBasicDAO.java:216)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

Anyone having any experince?

Really stuck on a subject, any help appreciated.

Ales

Issue title Pessimistic locking for MS SQL Server

http://opensource.atlassian.com/project ... key=HB-760

Finally found it :-)[/b]