|
From: Pavel Z. (JIRA) <tr...@fi...> - 2014-07-04 19:36:58
|
Changed data not visible in WHEN-section if exception occured inside SP that has been called from this code
-----------------------------------------------------------------------------------------------------------
Key: CORE-4483
URL: http://tracker.firebirdsql.org/browse/CORE-4483
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0 Alpha 2
Reporter: Pavel Zotov
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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
|