Changed data not visible in WHEN-section if exception occured inside SP that has been called from this code [CORE4483] #4803
Description
Submitted by: @pavel-zotov
Assigned to: @aafemt
Votes: 1
1. DDL:
create or alter procedure dlock(a_id int) as begin end;
recreate table tlog(id int);
recreate table dlist(id int primary key);
commit;
insert into dlist values(1);
commit;
set term ^;
create or alter procedure dlock(a_id int) as
declare v_id int;
begin
update dlist set id = id where id = :a_id;
--select id from dlist where id = :a_id for update with lock into v_id;
end
^set term ;^
commit;
2. Create THREE databases on:
1) LI-V2.5.3.26744 (yes, FB 2.5)
2) LI-T3.0.0.31202
3) LI-T3.0.0.31208
- and apply this DDL on them.
3. Create the following test (named e.g. 'catch.sql'):
show version;
rollback;
set transaction no wait;
set term ^;
execute block as
begin
delete from tlog;
insert into tlog(id) values(1);
rdb$set_context('USER_TRANSACTION', 'CNT_IN_MAIN_CODE',(select count(*) from tlog));
\-\-update dlist set id = id order by id rows 1;
execute procedure dlock\(1\);
when any do
begin
rdb$set_context('USER_TRANSACTION','CNT_IN_WHEN_SECTION',(select count(*) from tlog));
exception;
end
end
^ set term ;^
set list on;
select
rdb$get_context('USER_TRANSACTION','CNT_IN_MAIN_CODE') as CNT_IN_MAIN_CODE
,rdb$get_context('USER_TRANSACTION','CNT_IN_WHEN_SECTION') as CNT_IN_WHEN_SECTION
from rdb$database;
rollback;
The column CNT_IN_WHEN_SECTION will contain the number of rows in the table TLOG which should be seen inside WHEN-section.
Obviously, it should be equal to 1.
4. Open three times a couple of ISQL sessions (for each of server versions listed above) and do:
4.1) session #1:
SQL> select id from dlist; -- only to ensure that at least one row exists;
SQL> update dlist set id = id;
4.2) session #2:
SQL> in catch.sql;
I have the following results in session #2 (error "update conflicts" message was skipped):
1.LI-V2.5.3.26744:
CNT_IN_MAIN_CODE 1
CNT_IN_WHEN_SECTION 0
2. LI-T3.0.0.31202:
CNT_IN_MAIN_CODE 1
CNT_IN_WHEN_SECTION 1
3. LI-T3.0.0.31208:
CNT_IN_MAIN_CODE 1
CNT_IN_WHEN_SECTION 0
The result in LI-T3.0.0.31202 seems to me solely correct.
Something was broken after this build #31202 ?
PS.
No problem found if we change in DML script ("catch.sql"):
execute procedure dlock\(1\);
to:
update dlist set id = id order by id rows 1;
(i.e. this trouble is somehow related to call of SP)
Commits: 27395a0 FirebirdSQL/fbt-repository@0ae7bde
====== Test Details ======
Test contains two examples, both taken from discuss with dimitr, see letter 02-feb-2015 23:47.
Checked on WI-T4.0.0.127 - works OK.