Announcement Announcement Module
Collapse
No announcement yet.
MSSQL locking Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • MSSQL locking

    Since there is a lot of Hibernate users here I hope somebody will be able to help me. I also posted this at Hibernate forum, but there seems to be just so much traffic that it's probably lost somewere :? .
    I hope I'm not being anoying, since it is not quite Spring topic - but since Spring uses Hibernate a lot, who knows .

    ---

    Addition to below topic.

    I somehow solved this problem of synchronization on database level (and not on Java object) with modifying WITH(rowlock, holdlock) clause to WITH(tablockx, holdlock). So I don't get deadlock now, but the thing gets so slow - reasonable, since whole table is locked, just one thread working on it, with others waiting - and that is not what I wont, since number of threads accessing the same table will be eventually very high.

    Then trying with (rowlock, holdlock) on select and insert - again failing to deadlock. Applying clause (paglock, holdlock) to both - the same.

    Still seeking better solution.

    ---

    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.Dri ver

    hibernate.dialect=net.sf.hibernate.dialect.SQLServ erDialect
    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.addDiagnos tic(SQLDiagnostic.java:367)
    at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(Td sCore.java:2217)
    at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCor e.java:1696)
    at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(T dsCore.java:522)
    at net.sourceforge.jtds.jdbc.JtdsStatement.processRes ults(JtdsStatement.java:386)
    at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQL (JtdsStatement.java:369)
    at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.ex ecuteUpdate(JtdsPreparedStatement.java:382)
    at org.apache.commons.dbcp.DelegatingPreparedStatemen t.executeUpdate(DelegatingPreparedStatement.java:1 01)
    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.e xecute(ScheduledIdentityInsertion.java:29)
    at net.sf.hibernate.impl.SessionImpl.doSave(SessionIm pl.java:932)
    at net.sf.hibernate.impl.SessionImpl.doSave(SessionIm pl.java:857)
    at net.sf.hibernate.impl.SessionImpl.saveWithGenerate dIdentifier(SessionImpl.java:775)
    at net.sf.hibernate.impl.SessionImpl.save(SessionImpl .java:738)
    at net.sf.hibernate.impl.SessionImpl.saveOrUpdate(Ses sionImpl.java:1388)
    at org.springframework.orm.hibernate.HibernateTemplat e$13.doInHibernate(HibernateTemplate.java:320)
    at org.springframework.orm.hibernate.HibernateTemplat e.execute(HibernateTemplate.java:176)
    at org.springframework.orm.hibernate.HibernateTemplat e.saveOrUpdate(HibernateTemplate.java:317)
    at com.generalynx.ecos.data.dao.HibernateBasicDAO.sav eMissingData(HibernateBasicDAO.java:216)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Nativ e Method)
Working...
X