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

[Łukasz Antoniak]

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?
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.

Regards,
Lukasz