[hibernate-dev] Multi-table Bulk Operations with Temporary Tables
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