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 On Thursday, May 23, 2013 8:17:30 PM UTC-4, Gili wrote: > > 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 > -- 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.
