Issue Details (XML | Word | Printable)

Key: CORE-3537
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Vlad Khorsun
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

There is no need to undo changes made in GTT created with ON COMMIT DELETE ROWS option when transaction is rolled back.

Created: 24/Jun/11 12:35 PM   Updated: 27/May/15 06:50 PM
Component/s: Engine
Affects Version/s: 2.1.0, 2.1.1, 2.1.2, 2.1.3, 2.5.0, 2.1.4
Fix Version/s: 2.5.1, 3.0 Alpha 1

QA Status: Done successfully
Test Details:
Multiple measurements of ratio between fetches and marks when operation is done on:
1) fixed tab; 2) GTT with on commit PRESERVE rows and 3) GTT with on commit DELETE rows
-- show that it's value is ~2.7 for FB 2.5 and ~2.2 for FB 3.0


 Description  « Hide
When transaction is rolled back it should undo all changes made in its context.
If amount of such changes was not big, Firebird undoes them and marks
transaction in TIP as commited. For GTT created with ON COMMIT DELETE
ROWS option such undo have no sence as all its data will be released anyway
after rollback.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 09/May/15 09:16 PM
> If amount of such changes was ***not*** big, Firebird undoes them and marks transaction in TIP as commited.
> For GTT created with ON COMMIT DELETE ROWS option such undo have no sence

How it can be verified that FB does not do undo at ROLLBACK for GTT with 'ON COMMIT DELETE ROWS' ?

For example, consider following test:

===
recreate table t_fix(
  s1 varchar(200)
  unique using index t_fix_s1
);

recreate global temporary table t_gtt_del_rows(
  s1 varchar(200)
  unique using index t_gtt_del_rows_s
) on commit DELETE rows;

recreate global temporary table t_gtt_sav_rows(
  s1 varchar(200)
  unique using index t_gtt_sav_rows_s
) on commit PRESERVE rows;

commit;

set term ^;
execute block as
  declare n int = 20000;
  declare k int;
begin
  k=n;
  while(k>0) do insert into t_fix(s1) values( rpad('', 200, uuid_to_char(gen_uuid()) ) ) returning :k-1 into k;
end
^
rollback ----------------- (1)
^

execute block as
  declare n int = 20000;
  declare k int;
begin
  k=n;
  while(k>0) do insert into t_gtt_del_rows(s1) values( rpad('', 200, uuid_to_char(gen_uuid()) ) ) returning :k-1 into k;
end
^
rollback ----------------- (2)
^

execute block as
  declare n int = 20000;
  declare k int;
begin
  k=n;
  while(k>0) do insert into t_gtt_sav_rows(s1) values( rpad('', 200, uuid_to_char(gen_uuid()) ) ) returning :k-1 into k;
end
^
rollback ----------------- (3)
^

===

Every EB makes (as it seems to me) NOT big amount of changes: insert 20'000 rows with length = 200 characters.
If we run this test on WI-T3.0.0.31828 and watch in the trace statistics for ROLLBACK statements than we can see following values for (1), (2) and (3):

(1): 2593 ms, 28568 read(s), 22137 write(s), 298186 fetch(es), 72187 mark(s)
(2): 20 ms, 1663 read(s), 182 write(s), 3802 fetch(es), 1902 mark(s)
(3): 1313 ms, 28799 read(s), 21796 write(s), 298217 fetch(es), 72155 mark(s)

Statistics in ROLLBACK for GTT on commit DELETE rows, shown above in (2), really is much less than for two other cases.
But it is NOT zero. So, what else FB did in this "(2)" ? From where these non-zero values ?

Attila Molnár added a comment - 11/May/15 05:48 AM - edited
Hi!

If no undo log exists for GTT, then rollback to <savepoint> does not work on it?

Vlad Khorsun added a comment - 12/May/15 08:26 AM
Pavel,

> How it can be verified that FB does not do undo at ROLLBACK for GTT with 'ON COMMIT DELETE ROWS' ?
...
> Statistics in ROLLBACK for GTT on commit DELETE rows, shown above in (2), really is much less than for two other cases.
I.e. you answer yourself on your question above.

> But it is NOT zero. So, what else FB did in this "(2)" ? From where these non-zero values ?
Engine still must mark formerly used pages (pointer pages, data pages and index pages) as free on page inventory page(s).

Vlad Khorsun added a comment - 12/May/15 08:28 AM
Attila,

> If no undo log exists for GTT, then rollback to <savepoint> does not work on it?

undo log *exists* for GTT's

Pavel Zotov added a comment - 12/May/15 10:38 AM
> Engine still must mark formerly used pages (pointer pages, data pages and index pages) as free on page inventory page(s).

Vlad, thanks. Now I recall that you've explained this to me some years ago, but again forgot :-[