I am running into a nasty insert problem:
the code "works" but during stress test, the inserts become slower and slower
from the console output:
-----------------------------------------------------------
Hibernate: select DIGITAL_GIFT_ID_SEQUENCE.nextval from dual
Hibernate: select DG_FULFILLMENT_ID_SEQ.nextval from dual
very long delay here...
Hibernate: insert into DIGITAL_GIFTS (PURCHASER_CUSTOMER_ID, RECIPIENT_CUSTOMER_ID, ORDER_ID, PRODUCT_ID, STATUS, PURCHASE_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATED, DIGITAL_GIFT_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: insert into DG_FULFILLMENTS (DIGITAL_GIFT_ID, STATUS, CLAIM_CODE, EMAIL_ADDRESS, RECIPIENT_CUSTOMER_ID, ISSUE_DATE, CLAIM_DATE, EXPIRATION_DATE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATED, DG_FULFILLMENT_ID) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
------------------------------------------------------------
there is a longer and longer delay between the 2 select from sequence and the actually inserts statements
the CPU usage also peaks.
once I restart the program, everything works fine(fast) again but very soon it become slow.
What does hibernate do between the 2 select statements and 2 inserts above?
Thanks in advance!
Hibernate version:3.0
Mapping documents:
<?xml version='1.0' encoding='utf-8'?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property> <property name="connection.url">jdbc:oracle:thin:@test123:1521:test</property> <property name="connection.username">tester</property> <property name="connection.password">12345</property> <property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="c3p0.min_size">2</property> <property name="c3p0.max_size">5</property> <property name="c3p0.max_statement">25</property> <property name="show_sql">true</property> <property name="transaction.factory_class"> org.hibernate.transaction.JDBCTransactionFactory </property> <property name="cache.provider_class"> org.hibernate.cache.HashtableCacheProvider </property> <!-- <property name="hbm2ddl.auto">create</property> -->
<mapping resource="db/Gift.hbm.xml"/> <mapping resource="db/Fulfillment.hbm.xml"/> <mapping resource="db/CurrentFulfillment.hbm.xml"/>
</session-factory>
</hibernate-configuration>
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping> <class name="db.Fulfillment" table="DG_FULFILLMENTS"> <id name="id" column="DG_FULFILLMENT_ID" type="long"> <!-- <generator class="increment"/> --> <generator class="sequence"> <param name="sequence">DG_FULFILLMENT_ID_SEQ</param> </generator> </id> <property name="giftID" column = "DIGITAL_GIFT_ID" type="long" not-null="true"/> <property name="status" column = "STATUS" type="java.lang.String" not-null="true"/> <property name="claimCode" column = "CLAIM_CODE" type="java.lang.String" not-null="true"/> <property name="recipientEMail" column = "EMAIL_ADDRESS" type="java.lang.String" not-null="true"/> <property name="recipientID" column = "RECIPIENT_CUSTOMER_ID" type="long" /> <property name="issueDate" column = "ISSUE_DATE" type="timestamp" not-null="true"/> <property name="claimDate" column = "CLAIM_DATE" type="timestamp" /> <property name="expirationDate" column = "EXPIRATION_DATE" type="timestamp" /> <property name="createdBy" column = "CREATED_BY" type="java.lang.String" not-null="false"/> <property name="creationDate" column = "CREATION_DATE" type="timestamp" not-null="false"/> <property name="lastUpdatedBy" column = "LAST_UPDATED_BY" type="java.lang.String" /> <property name="lastUpdateDate" column = "LAST_UPDATED" type="timestamp" /> </class> </hibernate-mapping>
<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping> <class name="db.Gift" table="DIGITAL_GIFTS"> <id name="id" column="DIGITAL_GIFT_ID" type="long" > <!-- <generator class="native"/> --> <generator class="sequence"> <param name="sequence">DIGITAL_GIFT_ID_SEQUENCE</param> </generator> </id> <property name="purchaserID" column = "PURCHASER_CUSTOMER_ID" type="long" not-null="true"/> <property name="recipientID" column = "RECIPIENT_CUSTOMER_ID" type="long"/> <property name="orderID" column = "ORDER_ID" type="long" not-null="true"/> <property name="PRODUCT_ID" column = "PRODUCT_ID" type="java.lang.String" not-null="true"/> <property name="status" column = "STATUS" type="java.lang.String" not-null="true"/> <property name="purchaseDate" column = "PURCHASE_DATE" type="timestamp" not-null="true"/> <property name="createdBy" column = "CREATED_BY" type="java.lang.String" not-null="false"/> <property name="creationDate" column = "CREATION_DATE" type="timestamp" not-null="false"/> <property name="lastUpdatedBy" column = "LAST_UPDATED_BY" type="java.lang.String"/> <property name="lastUpdateDate" column = "LAST_UPDATED" type="timestamp"/> </class> </hibernate-mapping>
Code between sessionFactory.openSession() and session.close():
Session session = HibernateUtil.currentSession(); Transaction tx = session.beginTransaction(); Date now = new Date(); //create a new gift record db.Gift g = new db.Gift(); g.setPurchaserID(AmzUid.decryptCustomerID(getPurchaserID())); g.setOrderID(getOrderID()); g.setPRODUCT_ID(getPRODUCT_ID()); g.setStatus(Const.GIFT_STATUS_ACTIVE); g.setPurchaseDate(getPurchaseDate());
//let audit trigger handle these audit fields //g.setCreatedBy(Const.DGS_NAME); //g.setCreationDate(now); session.save(g);
//create a new fulfillment record db.Fulfillment f = new db.Fulfillment(); f.setGiftID(g.getId()); f.setStatus(Const.FULFILLMENT_STATUS_NEW); f.setClaimCode(DigitalGiftingUtil.generateClaimCode()); f.setRecipientEMail(getRecipientEMail()); f.setIssueDate(now); f.setExpirationDate(getExpirationDate());
//let audit trigger handle these audit fields //f.setCreatedBy(Const.DGS_NAME); //f.setCreationDate(now); session.save(f); tx.commit();
Full stack trace of any exception that occurs:
Name and version of the database you are using: Oracle 9i
The generated SQL (show_sql=true):
Debug level Hibernate log excerpt:
|