firebird-automations
commented
13 years ago Commented by: Sean Leyne (seanleyne)
I can't see how the engine can prevent this condition from occuring since the "error" is due to poor database design (bad trigger logic).
SInce the error is a design issue, I don't see any reason why anything would be logged in the firebird.log file -- this file logs engine errors, not schema/design errors.
Submitted by: @pavel-zotov
DDL:
create table tmp (id integer not null,val integer); commit; alter table tmp add primary key (id); insert into tmp values(1,1); commit;
create or alter trigger dbtg_rollback active on transaction rollback position 0 as begin in autonomous transaction do insert into tmp(id,val) values(1,1); end; commit;
recreate table t(id int not null primary key); commit; insert into t values(1); insert into t values(2); insert into t values(3); insert into t values(5); insert into t values(4); insert into t values(6); commit;
Scenario:
Attach_0: commit; set transaction read write read committed; update t set id=-id where id=4;
Then run 300 ISQL sesions that attempts to select table "t" but can not doing this due to NORECVERSION level of their RC and WAIT clause:
Script file 'SEL.SQL': commit; set transaction read write read committed; select * from t where id+0 between 5 and 6;
Batch file 'SEL.BAT': for /l %%i in (1, 1, %1) do ( start isql -i sel.sql -o sel.log test.fdb )
Run sample for 300 ISQLs:
SEL.BAT 300 [Enter].
This leads to appearing 300 'dead' windows that all are waiting for Attach_0 free record that was updated at the first step. After waiting \~10 minutes we kill all these windows via PSKILL.EXE utility.
Since that moment Firebird starts a huge number of transactions and they are in 'hang' state a few minutes. After that a hude number of rollbacks begin but this process seems to be infinite. Audit log raise in size infinitely but fiebird.log still remains empty.