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 <cow...@bbs.darktech.org> 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 <cow...@bbs.darktech.org> 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 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. >>> 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 >> 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. >> 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 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. >> 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 > 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. > 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 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. For more options, visit https://groups.google.com/groups/opt_out.
