Ping. Can we at least open an issue to track this? I don't want this to fall through the cracks.
Gili On Tuesday, July 30, 2013 8:11:33 PM UTC-4, Gili wrote: > > Hi Thomas, > > Good thing you asked for the updated test, because I now see a huge > performance difference: > > Multiple PreparedStatement: 4.623 seconds > One PreparedStatement : 1.944 seconds > > I've attached the updated testcase for your review. > > On a side-note, it took me a while to track down a deadlock for the > second case. H2 was throwing: > > org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "DEPARTMENTS" > > I tracked it down to the following: > > 1. The testcase invokes "delete from departments" > 2. TriggerCreateOnce.fire() invokes PreparedStatement.execute(), where > the PreparedStatement is for "DELETE FROM permissions WHERE id=?" > 3. The PreparedStatement invokes ConstraintReferential.existsRow() to > ensure that no rows reference "permissions" as a foreign key, but in so > doing blocks trying to establish a table lock. > > I believe this is caused by the fact that DEPARTMENTS has a ON DELETE > CASCADE constraint on the permissions table. The CASCADE tries to lock > DEPARTMENTS but the PreparedStatement (running in the system connection) > already has it locked. Using the system connections from Trigger.init() > lead to very ambiguous and hard-to-debug failures :( > > Anyway, I modified the PreparedStatement to use "SELECT * FROM" > instead of "DELETE FROM" to work around this issue. > > So, in conclusion: > > - I believe I demonstrated there is a noticeable performance benefit > for the Trigger2 interface. > - I believe there is an ease-of-use benefit to steering users away > from the use of system connection (due to the aforementioned problems). > > Action items: > > - Can H2 throw a deadlock exception when a system connection and user > connection running under the same thread attempt to lock the same table > (instead of a "Timeout" exception)? > - What are the next steps for Trigger2? > - If we introduce Trigger2.initTransaction()/closeTransaction() is > there still a legitimate need for system connections, or can we remove > them > from init()? > > Thanks, > Gili > > On 30/07/2013 4:59 PM, Thomas Mueller wrote: > > Hi, > > > In your test case, you didn't actually *execute* the statement. So it > was "prepare a statement" versus "do nothing" > > I think a more realistic use case is: (a) prepare, bind the values, and > execute a simple statement, versus (b) just bind the values and execute it. > > Regards, > Thomas > > > > > On Mon, Jul 29, 2013 at 11:42 PM, cowwoc <[email protected]> wrote: > >> On 29/07/2013 4:53 PM, Thomas Mueller wrote: >> >> Hi, >> >> In your test case, you didn't actually *execute* the statement. So it >> was "prepare a statement" versus "do nothing". Well, if the difference in >> time is so small, then I guess it doesn't make much sense to support this >> feature. >> >> >> That was by design. I thought we were trying to measure the >> difference between preparing a statement once per transaction (as I was >> proposing) versus preparing it once per trigger fire(). Was that not the >> case? >> >> >> >> So, instead of continuing to discuss this back and forth, let's just >> keep the current trigger interface as it is, and whenever we do *have* to >> change it, then let's keep this discussion in mind. Specially, instead of >> passing 6 parameters, pass an object that contains that data (Metadata in >> your case). This was done in other places already: CreateTableData. >> >> >> Okay. >> >> Gili >> >> >> Regards, >> Thomas >> >> >> >> On Sat, Jul 20, 2013 at 8:37 PM, cowwoc <[email protected]> wrote: >> >>> Hi Thomas, >>> >>> >>> On 20/07/2013 1:10 PM, Thomas Mueller wrote: >>> >>> Hi, >>> >>> > but that can lead to deadlocks ... >>> > e.g. see this previous discussion: >>> >>> As part of that discussion, I wrote: "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, I wonder if caching prepared statements is really a problem? >>> >>> >>> I wrote a quick benchmark against an in-memory database that >>> inserts a million rows, then drops them. I ran this against a trigger that >>> creates a new PreparedStatement in fire() and with a trigger that creates a >>> new PreparedStatement in init(). >>> >>> Multiple PreparedStatement: 1.746 seconds >>> One PreparedStatement : 1.427 seconds >>> >>> So we're talking about an overhead of 0.319 ms per invocation. >>> Please double check the attached benchmark to make sure I'm not doing >>> anything wrong. >>> >>> >>> >>> > I am under the impression that it doesn't matter whether a Trigger's >>> resources get cleaned up due to the Trigger being dropped or the database >>> being closed. Do you have a use-case that counters that? >>> >>> The use case is: you might want to drop a table when the trigger is >>> removed, but do nothing if the database is closed. >>> >>> >>> Fair enough, so let's keep them separate. >>> >>> 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. >>> For more options, visit https://groups.google.com/groups/opt_out. >>> >>> >>> >> >> -- >> 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/Sb3T1aVwoCE/unsubscribe. >> To unsubscribe from this group and all its topics, 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. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> >> >> -- >> 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. >> For more options, visit https://groups.google.com/groups/opt_out. >> >> >> > > -- > 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/Sb3T1aVwoCE/unsubscribe. > To unsubscribe from this group and all its topics, 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. > For more options, visit https://groups.google.com/groups/opt_out. > > > > > -- 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. For more options, visit https://groups.google.com/groups/opt_out.
