Hi there

I' m having a problem with the following scenario, which I hope is something some of you might have seen before.
I have looked it up in the forum without avail, so any help will be really appreciated
I'm testing concurrent access , therefore, I chose to launch two threads, each with their own session and making two different instances persistent.
it fails when updating one of the related tables with the below described exception.
any hint?
I have versioned data, as described in one of the mapping documents here attached.

Hibernate version:
2.0.6
Mapping documents:

<hibernate-mapping>

<class name="com.accovia.bookingFileManager.dao.domain.PersistentBkTraveler" table="bk_traveller">
<!-- Primary key -->
<id name="id" type="big_decimal" column="bk_traveller_id">
<generator class="com.accovia.bookingFileManager.dao.services.BigDecimalGenerator" />
</id>

<!-- -->
<version name="version" type="short" column="VERSION" />

<!-- -->
<component name="travelerReferenceID" class="com.accovia.travel.core.traveler.TravelerReferenceID">
<property name="registryCode" column="traveller_reg_cd" type="string" length="16" />
<property name="key" column="traveller_ref_key" type="string" length="16" />
</component>

<!-- Person's description -->
<component name="personDesc" class="com.accovia.business.core.person.PersonDesc">
<property name="fullName" column="name" type="string" length="50" />
<property name="birthDate" column="birth_dt" type="date">
<column name="birthdate" sql-type="DATE" />
</property>
<component name="ageWrapper" class="com.accovia.business.generic.age.AgeSimpleImpl">
<component name="ageCategoryRange" class="com.accovia.business.generic.age.AgeCategoryRangeImpl">
<property name="categoryCode" column="age_category_cd" type="string" length="16"></property>
</component>
</component>
</component>
<!--end Person's description -->



<!-- many end of a many to many relationship, and it uses an associated
table bk_item_traveller
-->

<set name="persistentBookTxItems" cascade="none" inverse="true" table="bk_item_traveller" outer-join="false">
<key column="bk_traveller_id"/>
<many-to-many class="com.accovia.bookingFileManager.dao.domain.PersistentBkItem" column="bk_item_id"/>
</set>

</class>
</hibernate-mapping>



Code between sessionFactory.openSession() and session.close():

session = open();
stmt= session.connection().createStatement();
//String SETLOCKMODEQUERY = "set lock mode to wait 3";
//String SETISOLATIONLEVEL="set isolation to repeatable read";

//stmt.executeUpdate(SETLOCKMODEQUERY);
//stmt.executeUpdate(SETISOLATIONLEVEL);

aPersistentBookingHeader = (PersistentBookingHeader) storeObject(session, aPersistentBookingHeader);

} catch (DAOException e) {
logger.error(e.getMessage(), e);
throw e;
}catch (SQLException ex)
{
logger.error(ex.getMessage(), ex);
}catch(HibernateException ex)
{
logger.error(ex.getMessage(), ex);
}
finally {
logger.debug("Exit method saveBookingFileDesc");
try {
stmt.close();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
//close();
}
return aPersistentBookingHeader;
}




Full stack trace of any exception that occurs:

- SQL Error: -244, SQLState: IX000
- Could not do a physical-order read to fetch next row.
- SQL Error: -107, SQLState: IX000
- ISAM error: record is locked.
- SQL Error: -244, SQLState: IX000
- Could not do a physical-order read to fetch next row.
- SQL Error: -107, SQLState: IX000
- ISAM error: record is locked.
- Could not synchronize database state with session
java.sql.SQLException: Could not do a physical-order read to fetch next row.
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:2996)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3310)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2263)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2183)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:692)
at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:308)
at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:857)
at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:279)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:639)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:611)
at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:31)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2100)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2062)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2005)
at com.accovia.infra.dao.BaseHibernateDAO.storeObject(BaseHibernateDAO.java:208)
at com.accovia.bookingFileManager.dao.BookingFileManagerDaoImplThread.savePersistentBookingHeader(BookingFileManagerDaoImplThread.java:50)
at com.accovia.bookingFileManager.tests.UserRequest.run(UserRequest.java:46)
- Hibernate exception: Could not synchronize database state with session
com.accovia.infra.dao.DAOException: Hibernate exception: Could not synchronize database state with session
at com.accovia.infra.dao.BaseHibernateDAO.storeObject(BaseHibernateDAO.java:214)
at com.accovia.bookingFileManager.dao.BookingFileManagerDaoImplThread.savePersistentBookingHeader(BookingFileManagerDaoImplThread.java:50)
at com.accovia.bookingFileManager.tests.UserRequest.run(UserRequest.java:46)
Caused by:
net.sf.hibernate.JDBCException: Could not synchronize database state with session
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2069)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2005)
at com.accovia.infra.dao.BaseHibernateDAO.storeObject(BaseHibernateDAO.java:208)
... 2 more
Caused by:
java.sql.SQLException: Could not do a physical-order read to fetch next row.
at com.informix.jdbc.IfxSqli.addException(IfxSqli.java:2996)
at com.informix.jdbc.IfxSqli.receiveError(IfxSqli.java:3310)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2263)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2183)
at com.informix.jdbc.IfxSqli.executeCommand(IfxSqli.java:692)
at com.informix.jdbc.IfxResultSet.executeUpdate(IfxResultSet.java:308)
at com.informix.jdbc.IfxStatement.executeUpdateImpl(IfxStatement.java:857)
at com.informix.jdbc.IfxPreparedStatement.executeUpdate(IfxPreparedStatement.java:279)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:639)
at net.sf.hibernate.persister.EntityPersister.update(EntityPersister.java:611)
at net.sf.hibernate.impl.ScheduledUpdate.execute(ScheduledUpdate.java:31)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2100)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2062)
... 4 more
Caused by:
java.sql.SQLException: ISAM error: record is locked.


Name and version of the database you are using:


Informix 7.1

The generated SQL (show_sql=true):
Hibernate: insert into informix.bk_segment (VERSION, parent_bk_seg_id, channel_cd, bk_ref_key, bk_reg_cd, status_cd, booking_id, bk_segment_id) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_lts (VERSION, channel_cd, status_cd, bk_segment_id, ext_ref_key, ext_ref_cd, bk_lts_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.booking_header (VERSION, desc, country_cd, lang_cd, departure_dt, return_dt, client_reg_cd, client_ref_key, destination_cd, origin_cd, booking_reg_cd, booking_ref_key, contract_no, start_stay_dt, end_stay_dt, net_amt, curr_cd, min_deposit_amt, group_cd, bk_status_cd, user_cd, create_dtt, mod_dtt, brand_cd, sales_channel_cd, booking_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_item (VERSION, desc, bk_lts_id, salesprc_reg_cd, salesprc_ref_key, categ_cd, net_sales_price, salespers_reg_cd, salespers_ref_key, product_cd, qty_sales_price, tx_limit_dtt, inventory_reg_cd, inventory_ref_key, service_type_cd, product_reg_cd, bk_status_cd, start_dt, end_dt, resource_type, product_status_cd, user_cd, create_dtt, mod_dtt, bk_item_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_segment (VERSION, parent_bk_seg_id, channel_cd, bk_ref_key, bk_reg_cd, status_cd, booking_id, bk_segment_id) values (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_traveller (VERSION, traveller_reg_cd, traveller_ref_key, name, birth_dt, age_category_cd, bk_traveller_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_lts (VERSION, channel_cd, status_cd, bk_segment_id, ext_ref_key, ext_ref_cd, bk_lts_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_item (VERSION, desc, bk_lts_id, salesprc_reg_cd, salesprc_ref_key, categ_cd, net_sales_price, salespers_reg_cd, salespers_ref_key, product_cd, qty_sales_price, tx_limit_dtt, inventory_reg_cd, inventory_ref_key, service_type_cd, product_reg_cd, bk_status_cd, start_dt, end_dt, resource_type, product_status_cd, user_cd, create_dtt, mod_dtt, bk_item_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_item (VERSION, desc, bk_lts_id, salesprc_reg_cd, salesprc_ref_key, categ_cd, net_sales_price, salespers_reg_cd, salespers_ref_key, product_cd, qty_sales_price, tx_limit_dtt, inventory_reg_cd, inventory_ref_key, service_type_cd, product_reg_cd, bk_status_cd, start_dt, end_dt, resource_type, product_status_cd, user_cd, create_dtt, mod_dtt, bk_item_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_traveller (VERSION, traveller_reg_cd, traveller_ref_key, name, birth_dt, age_category_cd, bk_traveller_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_traveller (VERSION, traveller_reg_cd, traveller_ref_key, name, birth_dt, age_category_cd, bk_traveller_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_lts (VERSION, channel_cd, status_cd, bk_segment_id, ext_ref_key, ext_ref_cd, bk_lts_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_item (VERSION, desc, bk_lts_id, salesprc_reg_cd, salesprc_ref_key, categ_cd, net_sales_price, salespers_reg_cd, salespers_ref_key, product_cd, qty_sales_price, tx_limit_dtt, inventory_reg_cd, inventory_ref_key, service_type_cd, product_reg_cd, bk_status_cd, start_dt, end_dt, resource_type, product_status_cd, user_cd, create_dtt, mod_dtt, bk_item_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: update informix.bk_traveller set VERSION=?, traveller_reg_cd=?, traveller_ref_key=?, name=?, birth_dt=?, age_category_cd=? where bk_traveller_id=? and VERSION=?
Hibernate: insert into informix.bk_traveller (VERSION, traveller_reg_cd, traveller_ref_key, name, birth_dt, age_category_cd, bk_traveller_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into informix.bk_lts (VERSION, channel_cd, status_cd, bk_segment_id, ext_ref_key, ext_ref_cd, bk_lts_id) values (?, ?, ?, ?, ?, ?, ?)
Hibernate: update informix.bk_traveller set VERSION=?, traveller_reg_cd=?, traveller_ref_key=?, name=?, birth_dt=?, age_category_cd=? where bk_traveller_id=? and VERSION=?\

Debug level Hibernate log excerpt:

I am also having same issue

ISAM error : record is locked.

Any help would be appreciated.

Thanks

Hi ,

I set isolation level in hibernate configation file and lock issue resolved.But we are doing Dirty Read by setting isolation level.

Use "synchronized" in your method.

Regards