10:11 a.m.
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
1:27 p.m.
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(a)hibernate.org
http://hibernate.org
4613
days inactive
4621
days old
1 comments
2 participants
participants (2)
-
Steve Ebersole -
Łukasz Antoniak