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.


Reply via email to