Single attachment produces "SQLSTATE = 40001 / deadlock / read conflicts with
concurrent update" when doing DDL which has block with SET AUTODDL OFF and
attempting to compile SP which write into read-only view
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Key: CORE-4961
URL: http://tracker.firebirdsql.org/browse/CORE-4961
Project: Firebird Core
Issue Type: Bug
Components: Engine
Reporter: Pavel Zotov
Consider following script (WI-V3.0.0.32070):
===
show version;
create or alter procedure sp_update as begin end;
create or alter view v_test(pid, f01) as select 1 pid, 2 f01 from
rdb$database;
commit;
recreate table test1(id int primary key);
recreate table test2(id int primary key, pid int, f01 numeric(12,2));
commit;
create or alter view v_test as
select a.id as pid, b.f01
from test1 a left join test2 b on a.id=b.pid;
commit;
set autoddl off; ------------------------------------------- [ 1 ]
set term ^;
create or alter procedure sp_dummy as
declare c int;
begin
select count(*) from rdb$types into c;
end
^
-- commit ^ --------- [ 2 ]
create or alter procedure sp_update as
begin
update v_test set f01 = 1 where pid = 0;
end
^
set term ;^
commit;
set autoddl on;
set term ^;
create trigger test1_bi for test1 active before insert position 0 as
begin
if (new.id is null) then execute procedure sp_dummy;
end
^
set term ;^
commit;
set list on;
set blob all;
select rdb$procedure_name, rdb$procedure_source from rdb$procedures;
show procedure sp_dummy;
quit;
===
When 'commit' that is marked as "[ 2 ]" is commented, this script produces:
===
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_UPDATE's BLR
-cannot update read-only view V_TEST
After line 34 in file meta-deadlock.sql
Statement failed, SQLSTATE = 40001
unsuccessful metadata update
-CREATE TRIGGER TEST1_BI failed
-deadlock
-read conflicts with concurrent update
------------------------------------------------ [ A ]
-concurrent transaction number is 589
After line 39 in file meta-deadlock.sql
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_UPDATE's BLR
-cannot update read-only view V_TEST
After line 45 in file meta-deadlock.sql
RDB$PROCEDURE_NAME SP_UPDATE
RDB$PROCEDURE_SOURCE 1a:3ee
begin
update v_test set f01 = 1 where pid = 0;
end
RDB$PROCEDURE_NAME SP_DUMMY
RDB$PROCEDURE_SOURCE 1a:3e5
declare c int;
begin
select count(*) from rdb$types into c;
end
Statement failed, SQLSTATE = 40001
deadlock
-read conflicts with concurrent update
-------------------------------------------------- [ B ]
-concurrent transaction number is 589
After line 50 in file meta-deadlock.sql
Command error: show procedure sp_dummy
===
Line "[ A ]" relates to 'create trigger' statement and line '[ B ]' - to 'show
procedure sp_dummy'.
Both these message looks very strange. Record in RDB$PROCEDURES for 'SP_DUMMY'
entry is locked.
But "who" does this if there is COMMIT statement before "create trigger
test1_bi" ?
PS. When 'commit' that is marked as "[ 2 ]" is UNcommented, I get only
===
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_UPDATE's BLR
-cannot update read-only view V_TEST
After line 34 in file meta-deadlock.sql
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_UPDATE's BLR
-cannot update read-only view V_TEST
After line 45 in file meta-deadlock.sql
===
PPS. When [1] is Commented, I get:
===
Statement failed, SQLSTATE = 2F000
Error while parsing procedure SP_UPDATE's BLR
-cannot update read-only view V_TEST
After line 24 in file meta-deadlock.sql
==
(i.e. only one message rather than two).
--
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
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
