Hey Lukasz, Sorry I was sick last week so did not get to respond to you. Responses inline...
On Sun 06 May 2012 10:11:23 AM CDT, Łukasz Antoniak wrote: > Hello all! > > I have just a very short question regarding concurrency aspects when > temporary tables are utilized to process multi-table bulk > operations. I am trying to refactor part of Envers code which currently > executes SELECT FOR UPDATE and UPDATE, to process with a > single UPDATE statement. The root cause is that Hibernate produces invalid > SQL query with UPGRADE lock for inheritance mappings on > Oracle, PostgreSQL and SQL Server (HHH-3298). UPDATE statement should modify > only one row. For updating inheritance relation > Hibernate uses temporary table: > 14:52:01,687 DEBUG SQL:104 - insert into HT_ParentEntity_AUD select > parententi0_.id as id, parententi0_.REV as REV from ( select > id, REV, REVTYPE, REVEND, data, null as numVal, 0 as clazz_ from > ParentEntity_AUD union all select id, REV, REVTYPE, REVEND, data, > numVal, 1 as clazz_ from ChildEntity_AUD ) parententi0_ where > parententi0_.id=? and (parententi0_.REVEND is null) and > parententi0_.REV<>? > 14:52:01,781 DEBUG SQL:104 - update ParentEntity_AUD set REVEND=? where (id, > REV) IN (select id, REV from HT_ParentEntity_AUD) > 14:52:01,796 DEBUG SQL:104 - delete from HT_ParentEntity_AUD > > Questions: > 1. What occurs when two different sessions try updating same data? When > temporary table is begin created (I cannot see it in the > standard output)? Assuming that it is created just before "insert into > HT_ParentEntity_AUD ...", the second session that executes > this statement should fail. Am I right? Hibernate prefers to use what ANSI SQL terms LOCAL TEMPORARY TABLES. It should be local to each DB session (connection). So, no, each connection should have its own copy of that temporary table. Now this is different on different databases as you might imagine. On Oracle for example, Hibernate actually uses what Oracle calls GLOBAL temporary tables; but what you see is that GLOBAL temp tables in Oracle operate much like ANSI LOCAL temp tables because Oracle actually keeps track of which connection added which rows to the GLOBAL temp table. There was an email to the dev list little over a year ago about all this. > 2. Would my modification affect performance? Currently Envers executes: > 22:04:02,734 DEBUG SQL:104 - select parententi0_.id as id12_, > parententi0_.REV as REV12_, parententi0_.REVTYPE as REVTYPE12_, > parententi0_.REVEND as REVEND12_, parententi0_.data as data12_, > parententi0_.numVal as numVal13_, parententi0_.clazz_ as clazz_ > from ( select id, REV, REVTYPE, REVEND, data, null as numVal, 0 as clazz_ > from ParentEntity_AUD union all select id, REV, REVTYPE, > REVEND, data, numVal, 1 as clazz_ from ChildEntity_AUD ) parententi0_ where > parententi0_.id=? and (parententi0_.REVEND is null) > for update > 22:04:02,750 DEBUG SQL:104 - update ParentEntity_AUD set REVEND=? where id=? > and REV=? > I'm not sure if creating temporary table, inserting data and then removing it > is the right thing for updating just one record. There is an improvement out there in JIRA about circumventing temporary tables if the where-clause or set-clause names only columns from the root table. That would be your condition here. Of course, this is a pretty simplistic SQL statement; perhaps you could just execute that SQL. -- st...@hibernate.org http://hibernate.org _______________________________________________ hibernate-dev mailing list hibernate-dev@lists.jboss.org https://lists.jboss.org/mailman/listinfo/hibernate-dev
