Hi Noel, Here is a self-contained testcase: https://bitbucket.org/cowwoc/h2triggerdeadlock/downloads. You should be getting the following output:
pre-delete department. Connection: conn1: url=jdbc:h2:mem:test1 user=SA pre-remove PUBLIC.DEPARTMENTS.permission. Connection: conn4: url=jdbc:default:connection user=SA pre-remove PUBLIC.COMPANIES.permission. Connection: conn5: url=jdbc:default:connection user=SA post-remove PUBLIC.COMPANIES.permission. Connection: conn5: url=jdbc:default:connection user=SA Exception in thread "main" org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "DEPARTMENTS"; SQL statement: DELETE FROM permissions WHERE id=? [50200-171] at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) at org.h2.message.DbException.get(DbException.java:169) at org.h2.message.DbException.get(DbException.java:146) at org.h2.table.RegularTable.doLock(RegularTable.java:501) at org.h2.table.RegularTable.lock(RegularTable.java:435) at org.h2.constraint.ConstraintReferential.existsRow(ConstraintReferential.java:368) at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:413) at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:431) at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:307) at org.h2.table.Table.fireConstraints(Table.java:873) at org.h2.table.Table.fireAfterRow(Table.java:890) at org.h2.command.dml.Delete.update(Delete.java:99) at org.h2.constraint.ConstraintReferential.updateWithSkipCheck(ConstraintReferential.java:465) at org.h2.constraint.ConstraintReferential.checkRowRefTable(ConstraintReferential.java:436) at org.h2.constraint.ConstraintReferential.checkRow(ConstraintReferential.java:307) at org.h2.table.Table.fireConstraints(Table.java:873) at org.h2.table.Table.fireAfterRow(Table.java:890) at org.h2.command.dml.Delete.update(Delete.java:99) at org.h2.command.CommandContainer.update(CommandContainer.java:75) at org.h2.command.Command.executeUpdate(Command.java:230) at org.h2.jdbc.JdbcPreparedStatement.execute(JdbcPreparedStatement.java:194) at org.bitbucket.cowwoc.h2triggerdeadlock.DeletePermissionTrigger.fire(DeletePermissionTrigger.java:54) at org.h2.schema.TriggerObject.fireRow(TriggerObject.java:201) at org.h2.table.Table.fireRow(Table.java:897) at org.h2.table.Table.fireAfterRow(Table.java:888) at org.h2.command.dml.Delete.update(Delete.java:99) at org.h2.command.CommandContainer.update(CommandContainer.java:75) at org.h2.command.Command.executeUpdate(Command.java:230) at org.h2.jdbc.JdbcStatement.executeUpdateInternal(JdbcStatement.java:125) at org.h2.jdbc.JdbcStatement.executeUpdate(JdbcStatement.java:110) at org.bitbucket.cowwoc.h2triggerdeadlock.App.main(App.java:45) Please let me know what you think. Thanks, Gili On Friday, May 24, 2013 3:22:24 AM UTC-4, Noel Grandin wrote: > > It's a new internal Connection, but it's running on the same internal > Session, so it's running inside the context of the original transaction. > We do that to make sure that some bits of internal state don't > accidentally "pollute" the original Connection object. > You should not be getting a deadlock. > If you are, that is a bug somewhere. > Do you have a reproducible test-case? > Or at the very least a Java thread-stack-dump? > > Also, > (a) what version are you running? > (b) what does your database URL look like? > > On 2013-05-24 02:17, Gili wrote: > > Hi, > > > > Based on the third answer at > > > https://groups.google.com/forum/#!msg/h2-database/VoE3AU7mSuM/YAReMtnRsZQJ > > < > https://groups.google.com/forum/#%21msg/h2-database/VoE3AU7mSuM/YAReMtnRsZQJ> > I > > am expecting a AFTER DELETE Trigger to get the same Connection as the > > statement that executed the DELETE operation, but it does not. I am > > seeing two separate connections and if they attempt to write to the > > same table the operation deadlocks. > > > > Did this behavior change since June 2010 (when linked post was made)? > > Are trigger supposed to get the same connection as the original > > operation? If not, is it possible to configure them to do so? > > > > I ask because I'm still trying to solve this > > question: http://stackoverflow.com/q/16705097/14731 > > > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/h2-database?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
