Triggers using a different Connection than the original operation

[Gili]

Hi,

Based on the third answer at https://groups.google.com/forum/#!msg/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

 

Thanks,

Gili

[Gili]

As a follow-up, I think I discovered a bug in INSTEAD OF triggers.

1. I looked into the source-code and I can confirm the current implementation always runs triggers with a different connection than the original statement.

2. I attempted to use an INSTEAD OF DELETE trigger so that when someone attempts to delete a department row, I delete a permissions row instead (which then cascades to deleting the original department).

3. However, it seems that H2 locks the original table (companies) using the original connection before executing the trigger, so we get a deadlock.

Expected behavior: INSTEAD OF triggers should prevent the original connection from acquiring locks. They are supposed to replace a statement so why is part of it executing anyway?

I've got one outstanding question: why do triggers run inside a separate connection? Wouldn't it make much more sense to run them from within the same connection since they are supposed to run from within the same transaction and avoid this kind of locking problem?

Thanks,

Gili

[Noel Grandin]

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

[Gili]

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

[Noel Grandin]

The easiest workaround for now, is for you not to use PreparedStatements.
The problem is that the original Connection you are caching in the
init() method is linked to the system session, while the new Connection
is linked to the current user session.
That means that because you have generated PreparedStatements, your
trigger code is operating on a different session to the current user
transaction, which leads to a deadlock.

You're not doing anything wrong, but I can't see any way of fixing this
short of forbidding the use of PreparedStatement in Trigger, which is
unfortunate.

[cowwoc]

Hi Noel,

    First of all, thank you very much for tracking this down. I'm very happy that I've finally got a workaround.

    The next question I would ask is: what do our databases do? Do they force users to create a new PreparedStatement every time the trigger is fired?

    I'd like to take this opportunity to propose the following changes to the Trigger interface (consider introducing a separate interface if you wish to maintain backwards compatibility):

1. Add a method that is invoked the first time a Trigger is fired per Transaction. Because a Trigger may be fired multiple times per transaction, we need a new method to create Transaction-specific resources like PreparedStatement.
2. Add a parameter to the fire() method which indicates the kind of operation being fired (INSERT, DELETE, UPDATE, SELECT, ROLLBACK). Currently there is no way to detect ROLLBACK or SELECT. See https://groups.google.com/d/msg/h2-database/QapWDppTpfc/YQhBwNqPbWkJ for a related discussion.
   
3. Add a parameter to the init() method which allows users to parametrize triggers. Meaning, allow users to pass a String from the CREATE TRIGGER command to trigger related-but-different behavior. For example, I have a trigger that prevents updates to fields I consider constants. Right now I have to create a separate trigger per table because they have different column names. If I could pass a String directly into the trigger I could use a single trigger with parametrized behavior (pass in a list of column names).
   

    What do you think?

Thanks,
Gili

[Noel Grandin]

No, I don't think we need to add extra complexity.
We'll figure the locking thing out eventually.
Most of the time it isn't a problem because people don't normally try
and update the same tables inside a trigger.

[cowwoc]

Fair enough. The first and third points have a workaround. What
about the second point (the user need to know which kind of operation is
being fired)? I don't know of a workaround for that... short of creating
a separate Trigger class for each kind of operation but this is a really
ugly hack. You could always add a new fire() method containing this
information that delegates to the old one for backwards compatibility.

Thanks,
Gili

[Gili]

So to follow-up on http://stackoverflow.com/q/16705097/14731, are triggers the only way to go? Or is there a better way to implement this?

Thank you,

Gil

On Thursday, May 23, 2013 8:17:30 PM UTC-4, Gili wrote:

[Thomas Mueller]

Hi,

I suggest to use PreparedStatement, and always create a new PreparedStatement (for each invokation of the trigger).

Internally, the database caches a low-level part of a PreparedStatement, so that creating the PreparedStatement should be fast. This is the "query cache", see also http://h2database.com/javadoc/org/h2/constant/DbSettings.html#QUERY_CACHE_SIZE - the documentation is actually wrong, as it doesn't just cache "select" statements as it used to, but also "delete", "insert", "update". I have updated the documentation: https://code.google.com/p/h2database/source/detail?r=4829

Regards,

Thomas

--
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 h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[cowwoc]

Hi Thomas,

    Okay, thank you.

    Please note that there is a typo in your commit. "if it return a single value" should be "if it returns a single value".

Gili

You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/B3xG488RBhI/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

> Please note that there is a typo in your commit

Thanks a lot! I will fix it.

Regards,

Thomas