We have a problem with using database triggers for optimistic record locking. We want to use triggers TOGETHER with Hibernate support of the <version> in the mapping file. This is necessary, since we are expecting other applications to access & change the data in the database. The trigger catches inserts and updates. The insert trigger sets the version id column to 0 (matches Hibernate behaviour). The update trigger checks that the version id column sent from the update matches the expected value (old value from the database+1). So Hibernate can continue to handle versions as normal. The problem is that, at some points in the Hibernate code, Hibernate decides to do updates without specifying the version id value. This causes the trigger to fail. From the example below, we are trying to save a TestAssignment. A TestAssignment actually belongs to a Subcategory, but Subcategory is nonmutable (maintained in another system). The Subcategory has a 1-to-many relationship to TestAssignment. We can create a new TestAssignment in our app, but then we just save the TestAssignment, not the Subcategory. The relevant mappings are shown below. The problem is shown in the debug log below: when we insert a new TestAssignment, it starts a transaction, inserts the record (including the value of the version id), and then does an update of the same record!? This update does not include the version id (update in same txn as insert, so noone else could see the rec yet). This is fine for Hibernate, but causes havok in the trigger. Any ideas? Can we do something to remove the update? It is setting the foreign key columns to the subcategory, but the values are the same as in the insert. This is interesting, because the mapping for TestAssignment just treats these as regular properties, not an inverse many-to-1 for Subcategory. Are there other places in Hibernate that will do the same kind of thing? Are our triggers doomed? If we can't use triggers for locking, what are our alternatives?
Hibernate version: 2.1
Mapping documents:
Subcategory:
Code:
<class name="Subcategory" table="pgm_subcategory" mutable="false">
<composite-id name="catSubcatKey" class="Subcategory$CatSubcatKey">
<key-property name="categoryCode" column="pgm_category_code"
type="string" length="3"/>
<key-property name="subcategoryCode" column="pgm_subcategory_code"
type="string" length="3"/>
</composite-id>
...
<bag name="testAssignments" lazy="true">
<key>
<column name="pgm_category_code"/>
<column name="pgm_subcategory_code"/>
</key>
<one-to-many class="TestAssignment"/>
</bag>
</class>
TestAssignment:
Code:
<class name="TestAssignment" table="ppm_test_assignment"
select-before-update="true">
<id name="id" type="long" column="test_ass_id">
<generator class="native">
<param name="sequence">sq_ppta</param>
</generator>
</id>
<version name="version" column="record_version" type="long"/>
<!-- categoryID and subcategoryID represent the composite foreign key to
Subcategory -->
<property name="categoryID" column="pgm_category_code" type="string"
length="3" not-null="true"/>
<property name="subcategoryID" column="pgm_subcategory_code"
type="string" length="3" not-null="true"/>
...
</class>
N/A?
Full stack trace of any exception that occurs:See below (debug log)
Name and version of the database you are using:Oracle 9.2.0.5
The generated SQL (show_sql=true):See below (debug log)
Debug level Hibernate log excerpt:Code:
05/03/03 11:14:31 Hibernate: insert into ppm_test_assignment (record_version, pgm_category_code, pgm_subcategory_code, root_version_id, test_ass_id) values (?, ?, ?, ?, ?)
11:14:31,793 DEBUG net.sf.hibernate.impl.BatcherImpl:252 - preparing statement
11:14:31,793 DEBUG net.sf.hibernate.persister.EntityPersister:384 - Dehydrating entity: [..TestAssignment#46]
11:14:31,793 DEBUG net.sf.hibernate.type.LongType:46 - binding '0' to parameter: 1
11:14:31,793 DEBUG net.sf.hibernate.type.StringType:46 - binding '810' to parameter: 2
11:14:31,793 DEBUG net.sf.hibernate.type.StringType:46 - binding '815' to parameter: 3
11:14:31,793 DEBUG net.sf.hibernate.type.LongType:46 - binding '149' to parameter: 4
11:14:31,793 DEBUG net.sf.hibernate.type.LongType:46 - binding '46' to parameter: 5
11:14:31,823 DEBUG net.sf.hibernate.persister.EntityPersister:449 - Inserting entity: [...AssignmentPeriod#95]
11:14:31,823 DEBUG net.sf.hibernate.persister.EntityPersister:450 - Version: 0
11:14:31,823 DEBUG net.sf.hibernate.impl.BatcherImpl:210 - done closing: 0 open PreparedStatements, 0 open ResultSets
11:14:31,823 DEBUG net.sf.hibernate.impl.BatcherImpl:272 - closing statement
11:14:31,833 DEBUG net.sf.hibernate.impl.BatcherImpl:203 - about to open: 0 open PreparedStatements, 0 open ResultSets
11:14:31,833 DEBUG net.sf.hibernate.SQL:229 - insert into ppm_assignment_period (record_version, activation_date, deactivation_date, ass_period_code, test_ass_id, ass_period_id) values (?, ?, ?, ?, ?, ?)
05/03/03 11:14:31 Hibernate: insert into ppm_assignment_period (record_version, activation_date, deactivation_date, ass_period_code, test_ass_id, ass_period_id) values (?, ?, ?, ?, ?, ?)
11:14:31,833 DEBUG net.sf.hibernate.impl.BatcherImpl:252 - preparing statement
11:14:31,833 DEBUG net.sf.hibernate.persister.EntityPersister:384 - Dehydrating entity: [...AssignmentPeriod#95]
11:14:31,833 DEBUG net.sf.hibernate.type.LongType:46 - binding '0' to parameter: 1
11:14:31,833 DEBUG net.sf.hibernate.type.DateType:46 - binding '17 March 2005' to parameter: 2
11:14:31,833 DEBUG net.sf.hibernate.type.DateType:41 - binding null to parameter: 3
11:14:31,843 DEBUG net.sf.hibernate.engine.Cascades:341 - id unsaved-value strategy NULL
11:14:31,843 DEBUG net.sf.hibernate.type.IntegerType:46 - binding '0' to parameter: 4
11:14:31,843 DEBUG net.sf.hibernate.type.LongType:46 - binding '46' to parameter: 5
11:14:31,843 DEBUG net.sf.hibernate.type.LongType:46 - binding '95' to parameter: 6
11:14:31,853 DEBUG net.sf.hibernate.persister.EntityPersister:449 - Inserting entity: [...AssignmentPeriod#96]
11:14:31,853 DEBUG net.sf.hibernate.persister.EntityPersister:450 - Version: 0
11:14:31,853 DEBUG net.sf.hibernate.impl.BatcherImpl:114 - reusing prepared statement
11:14:31,853 DEBUG net.sf.hibernate.SQL:229 - insert into ppm_assignment_period (record_version, activation_date, deactivation_date, ass_period_code, test_ass_id, ass_period_id) values (?, ?, ?, ?, ?, ?)
05/03/03 11:14:31 Hibernate: insert into ppm_assignment_period (record_version, activation_date, deactivation_date, ass_period_code, test_ass_id, ass_period_id) values (?, ?, ?, ?, ?, ?)
11:14:31,863 DEBUG net.sf.hibernate.persister.EntityPersister:384 - Dehydrating entity: [...AssignmentPeriod#96]
11:14:31,863 DEBUG net.sf.hibernate.type.LongType:46 - binding '0' to parameter: 1
11:14:31,863 DEBUG net.sf.hibernate.type.DateType:46 - binding '17 March 2005' to parameter: 2
11:14:31,863 DEBUG net.sf.hibernate.type.DateType:41 - binding null to parameter: 3
11:14:31,863 DEBUG net.sf.hibernate.engine.Cascades:341 - id unsaved-value strategy NULL
11:14:31,863 DEBUG net.sf.hibernate.type.IntegerType:46 - binding '1' to parameter: 4
11:14:31,863 DEBUG net.sf.hibernate.type.LongType:46 - binding '46' to parameter: 5
11:14:31,863 DEBUG net.sf.hibernate.type.LongType:46 - binding '96' to parameter: 6
11:14:31,883 DEBUG net.sf.hibernate.impl.BatcherImpl:210 - done closing: 0 open PreparedStatements, 0 open ResultSets
11:14:31,883 DEBUG net.sf.hibernate.impl.BatcherImpl:272 - closing statement
11:14:31,883 DEBUG net.sf.hibernate.collection.BasicCollectionPersister:555 - Deleting rows of collection: [...Subcategory.testAssignments#...Subcategory$CatSubcatKey@c40edd]
11:14:31,893 DEBUG net.sf.hibernate.collection.BasicCollectionPersister:580 - no rows to delete
11:14:31,893 DEBUG net.sf.hibernate.collection.BasicCollectionPersister:712 - Updating rows of collection: ...Subcategory.testAssignments#...Subcategory$CatSubcatKey@c40edd
11:14:31,893 DEBUG net.sf.hibernate.collection.BasicCollectionPersister:717 - done updating rows: 0 updated
11:14:31,893 DEBUG net.sf.hibernate.collection.BasicCollectionPersister:594 - Inserting rows of collection: [...Subcategory.testAssignments#...Subcategory$CatSubcatKey@c40edd]
11:14:31,893 DEBUG net.sf.hibernate.impl.BatcherImpl:203 - about to open: 0 open PreparedStatements, 0 open ResultSets
11:14:31,893 DEBUG net.sf.hibernate.SQL:229 - update ppm_test_assignment set pgm_category_code=?, pgm_subcategory_code=? where test_ass_id=?
05/03/03 11:14:31 Hibernate: update ppm_test_assignment set pgm_category_code=?, pgm_subcategory_code=? where test_ass_id=?
11:14:31,893 DEBUG net.sf.hibernate.impl.BatcherImpl:252 - preparing statement
11:14:31,893 DEBUG net.sf.hibernate.type.StringType:46 - binding '810' to parameter: 1
11:14:31,893 DEBUG net.sf.hibernate.type.StringType:46 - binding '815' to parameter: 2
11:14:31,903 DEBUG net.sf.hibernate.type.LongType:46 - binding '46' to parameter: 3
11:14:31,933 DEBUG net.sf.hibernate.impl.BatcherImpl:210 - done closing: 0 open PreparedStatements, 0 open ResultSets
11:14:31,933 DEBUG net.sf.hibernate.impl.BatcherImpl:272 - closing statement
11:14:31,943 DEBUG net.sf.hibernate.util.JDBCExceptionReporter:49 - could not insert collection rows: [...Subcategory.testAssignments#...Subcategory$CatSubcatKey@c40edd]
java.sql.SQLException: ORA-20000: Concurrency Failure
ORA-06512: at "XX.TR_PPTA_BIUR", line 9
ORA-04088: error during execution of trigger XX.TR_PPTA_BIUR'
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:189)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:242)
at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:554)
at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1478)
at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:888)
at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2076)
at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1986)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2697)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:457)
at net.sf.hibernate.impl.NonBatchingBatcher.addToBatch(NonBatchingBatcher.java:22)
at net.sf.hibernate.collection.AbstractCollectionPersister.insertRows(AbstractCollectionPersister.java:610)
at net.sf.hibernate.impl.ScheduledCollectionUpdate.execute(ScheduledCollectionUpdate.java:49)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2438)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2394)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2260)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at ...
11:14:31,953 WARN net.sf.hibernate.util.JDBCExceptionReporter:57 - SQL Error: 20000, SQLState: 72000
11:14:31,953 ERROR net.sf.hibernate.util.JDBCExceptionReporter:58 - ORA-20000: Concurrency Failure
ORA-06512: at "XX.TR_PPTA_BIUR", line 9
ORA-04088: error during execution of trigger 'XX.TR_PPTA_BIUR'
11:14:31,953 ERROR net.sf.hibernate.impl.SessionImpl:2399 - Could not synchronize database state with session
11:14:31,963 DEBUG net.sf.hibernate.transaction.JDBCTransaction:82 - rollback
11:14:31,963 DEBUG net.sf.hibernate.impl.SessionImpl:594 - transaction completion
11:14:31,963 DEBUG net.sf.hibernate.transaction.JDBCTransaction:103 - re-enabling autocommit
11:14:31,973 DEBUG net.sf.hibernate.impl.SessionImpl:576 - closing session
Login
FAQ
Search