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
On 27/05/2013 3:17 AM, Noel Grandin wrote:
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.
On 2013-05-24 15:16, Gili wrote:
Hi Noel,
Here is a self-contained testcase:
https://bitbucket.org/cowwoc/h2triggerdeadlock/downloads. You should
be getting the following output:
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@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.
