Please can anyone help with this problem.
Within a session, I am deleting several rows from a table (no foreign keys), then inserting rows to the same table. I start a transaction prior to doing the deletes, and do not close it until the inserts are done.
The deletes work fine, but as soon as I try to insert i get the following message.

16:28:38,249 WARN JDBCExceptionReporter:71 - SQL Error: 1, SQLState: 23000
16:28:38,249 ERROR JDBCExceptionReporter:72 - ORA-00001: unique constraint (GPMD
.METRICS_UK_1) violated

16:28:38,249 WARN JDBCExceptionReporter:71 - SQL Error: 1, SQLState: 23000
16:28:38,249 ERROR JDBCExceptionReporter:72 - ORA-00001: unique constraint (GPMD
.METRICS_UK_1) violated

16:28:38,249 ERROR AbstractFlushingEventListener:300 - Could not synchronize dat
abase state with session
org.hibernate.exception.ConstraintViolationException: Could not execute JDBC bat
ch update
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j
ava:71)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp
er.java:43)
at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:
202)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:235)

at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139)

at org.hibernate.event.def.AbstractF
=======================================
The PK for this table is a sequence - column is 'METRICS_ID' and the sequence is 'Metrics_seq' - see mapping file below.
How could there be this error happen when the PK is generated?

>>>>>>>>>> mapping file >>>>>>>>>>>>>>
<?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>
<!--
OR MApping between METRICS AND Metrics.class
-->
<class name="com.gpmd.persistence.metrics.Metrics"
lazy="false"
table="METRICS">
<!--
<cache usage="read-write" />
-->
<id name="metricsId"
type="integer"
unsaved-value="null">
<column name="METRICS_ID" length="40" />
<generator class="sequence">
<param name="sequence">Metrics_seq</param>
</generator>
</id>

<property name="footprintNumber" type="string">
<column name="FOOTPRINT_NUMBER" length="30" not-null="true" />
</property>
<property name="metricsCode" type="string">
<column name="METRICS_CODE" length="24" not-null="true"/>
</property>
<property name="metricsDescription" type="string">
<column name="METRICS_DESCRIPTION" length="80" not-null="true" />
</property>
<property name="metricsIsCompleted" type="float">
<column name="METRICS_IS_COMPLETED" length="1" not-null="true" />
</property>
<property name="metricsBaselineDuration" type="float">
<column name="METRICS_BASELINE_DURATION" length="10" not-null="true" />
</property>
<property name="metricsActualDuration" type="float">
<column name="METRICS_ACTUAL_DURATION" length="10" not-null="false" />
</property>
<property name="metricsTargetStartDate" type="date">
<column name="METRICS_TARGET_START_DATE" length="7" not-null="false" />
</property>
<property name="metricsTargetEndDate" type="date">
<column name="METRICS_TARGET_END_DATE" length="7" not-null="false" />
</property>
<property name="metricsActualStartDate" type="date">
<column name="METRICS_ACTUAL_START_DATE" length="7" not-null="false" />
</property>
<property name="metricsActualEndDate" type="date">
<column name="METRICS_ACTUAL_END_DATE" length="7" not-null="false" />
</property>
<property name="metricsIsMilestone" type="float">
<column name="METRICS_IS_MILESTONE" length="1" not-null="true" />
</property>
<property name="metricsIsPq" type="float">
<column name="METRICS_IS_PQ" length="1" not-null="true" />
</property>
<property name="metricsMilestoneCode" type="string">
<column name="METRICS_MILESTONE_CODE" length="24" not-null="false" />
</property>
<property name="metricsForecastDuration" type="float">
<column name="METRICS_FORECAST_DURATION" length="10" not-null="false" />
</property>
<property name="metricsForecastStartDate" type="date">
<column name="METRICS_FORECAST_START_DATE" length="7" not-null="false" />
</property>
<property name="metricsForecastEndDate" type="date">
<column name="METRICS_FORECAST_END_DATE" length="7" not-null="false" />
</property>

</class>
</hibernate-mapping>

===============================
Hibernate version is: 3.1 and Oracle 9.

===============================


The generated SQL (show_sql=true):
===================================
Hibernate:
/* insert com.gpmd.persistence.metrics.Metrics
*/ insert
into
METRICS
(FOOTPRINT_NUMBER, METRICS_CODE, METRICS_DESCRIPTION, METRICS_IS_COM
PLETED, METRICS_BASELINE_DURATION, METRICS_ACTUAL_DURATION, METRICS_TARGET_START
_DATE, METRICS_TARGET_END_DATE, METRICS_ACTUAL_START_DATE, METRICS_ACTUAL_END_DA
TE, METRICS_IS_MILESTONE, METRICS_IS_PQ, METRICS_MILESTONE_CODE, METRICS_FORECAS
T_DURATION, METRICS_FORECAST_START_DATE, METRICS_FORECAST_END_DATE, METRICS_ID)

values
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
=====================================

Any help greatly appreciated...

What column(s) is the named constraint GPMD.METRICS_UK_1 on? Your DBMS is complaining because a unique constraint has been violated, not because of a key violation. It's a poor exception, it should tell you what column(s) is affectecd, but you can figure that out by examing the table's properties.

I worked around the issue by doing the deletes and inserts in two sessions.
creating a transaction for each. It is a repeatable process, so if it goes wrong i can always recover the data.
However, I'd still like to know why the two processes could not be done within a single session/transaction. The PKs of the new rows would always be unique and never conflict with the PKs of the rows being deleted. I should add that cases are common where the data to be inserted is identical to that being deleted, except for the PK. It could be that Hibernate sees the deleted set and the new set as identical for this reason-after all Hibernate is unaware of the new PKs which will be generated by the DB engine.
Any thoughts?

Could it have something to do with your sequence, Metrics_seq? If, when you're inserting, the sequence returns the id of one of the rows that you've just deleted (because that id is available inside the transaction) but for some reason gets confused because the deletes haven't been committed (and therefore, the id isn't available outside the transaction), then you might get a uniqueness constraint problem.

Unfortunately I know nothing about sequences, I've never worked with a DBMS that uses them, so I know nothing about what happens to them during uncommitted transactions.

That could be the reason, but I would have to say that the sequence returned by Oracle would be the next higher value, and thus not a value that currently is in use. So, I'm still unsure about the cause of the problem. If the DB had been better designed I would not have to do what I do. I would not have to delete then insert, I could simply update, then the problem would not occur. But we often have to work with 'poor' designs.
Thanks...

I have the same problem too, I am using the oracle sequence to generate the unique ID.

When there are more than 1 user accessing the same method, it will give this error. When there is only 1 user, it doesnt happen.

Does hibernate get the next sequence id and increase the sequence from oracle when the session.save is called?

Anyone have any idea?

If you enable show sql properties you will see the sequence request before the insert. It is using straight JDBC to do this as such its atomic.

It seems that the hibernate insert will cause duplicate key exception when there is multiple database action within one session.

I have insert and update within the same session, it will cause the problem, but this is very hard to simulate back.