Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Jun 24, 2011 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @hvlad

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.

Commits: d5faade f12c243

====== 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

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.1 [ 10333 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Attila Molnár (e_pluribus_unum)

Hi!

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Attila,

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

undo log *exists* for GTT's

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

> 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 :-[

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment