[hibernate-dev] Multi-table Bulk Operations with Temporary Tables

[Steve Ebersole]

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.

--
steve at hibernate.org
http://hibernate.org