recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_bi'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1
);
create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1
);
create index tq2_pid on tq2(pid);
commit;


recreate table tdx(
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0)
);
recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate sequence g;
commit;

alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;

set term ^;
create or alter trigger tdx_bi for tdx active before insert as
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    --  which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new.pid );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for tdx active before update or delete as
    declare v_sel_id int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then  -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid;
            v_handle_mode='del in tq1'; delete from tq1 q where q.pid = old.pid;
            v_handle_mode='del in tq2'; delete from tq2 q where q.pid = old.pid;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid;
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              v_handle_mode='ins tq1, del in tq2 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq2 where pid = new.pid as cursor c
              do begin
                  v_handle_mode='try: ins tq1, del in tq2, id='||c.id;
                  insert into tq1(id, pid) values (c.id, c.pid);
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              v_handle_mode='ins tq2, del in tq1 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq1 where pid = new.pid as cursor c
              do begin
                  v_handle_mode='try: ins tq2, del in tq1, id='||c.id;
                  insert into tq2(id, pid) values (c.id, c.pid);
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode);

      exception;

    end
end

^set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
    -- number of added rows = new.qty:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);
            select id from tmx where id >= :v_rnd_id order by id rows 1 into sel_id;
            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation (violation of PRIMARY or UNIQUE KEY constraint "T1_XY" on table "T1")
                                ,335544349 -- no_dup (attempt to store duplicate value (visible to active transactions) in unique index "T2_XY") - without UNQ constraint
                                ,335544466 -- violation of FOREIGN KEY constraint @1 on table @2
                                ,335544838 -- Foreign key reference target does not exist (when attempt to ins/upd in DETAIL table FK-field with value for which parent ID has been changed or deleted - even in uncommitted concurrent Tx)
                                ,335544839 -- Foreign key references are present for the record  (when attempt to upd/del in PARENT table PK-field and rows in DETAIL (no-cascaded!) exists for old value)
                           ) -- in
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 500;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

set term ^;
execute block as
    declare n int = -1;
begin
    n=fn_halt_test();
    if ( n > 0 ) then
      exception ex_bad_error_encountered using (:n);
end
^ set term ;^
set bail off;

commit; set transaction snapshot no wait;
set term ^;
execute block returns(trn_id int, dts varchar(12), sel_id int, result varchar(3))
as
  declare n int = 10;
begin
  trn_id = current_transaction;
  while ( n>0 ) do begin
    select sel_id, result from p_tmx_hadle_doc into sel_id, result;
    dts=substring(cast(cast('now' as timestamp) as varchar(24)) from 12 for 12);
    suspend;
    n=n-1;
  end
end
^ set term ;^
--------------------

setlocal enabledelayedexpansion enableextensions

@rem path to ISQL utility, without trailing backslash:
set fbc=C:\MIX\firebird\fb25\bin

@rem path where temp logs will be created:
set tmpdir=c:\temp\tq

md %tmpdir% 2>nul
del %tmpdir%\tq_run_*.log 2>nul
del %tmpdir%\tq_run_*.err 2>nul
if not .%1.==.. set /a winq=%1
if .%winq%.==.0. set winq=10
@echo off
for /l %%i in (1, 1, %winq%) do (
  set str=%fbc%\isql 192.168.0.220/3333:/var/db/fb30/tq.fdb -n -i tq_run.sql 1^>%tmpdir%\tq_run_%%i.log 2^>%tmpdir%\tq_run_%%i.err
  echo str = !str!
  start /min cmd /c !str!
)
goto end
:end

recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_bi'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq2_pid on tq2(pid);
commit;

recreate table tdx( -- detail for `tmx` table
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0)
);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate sequence g;
commit;

-- FK constraints:
alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;
 alter table tq1 add constraint tq1_fk
    foreign key (pid) references tdx(id);
alter table tq2 add constraint tq2_fk
    foreign key (pid) references tdx(id); 
commit;

-- Triggers:
set term ^;
create or alter trigger tdx_ a i for tdx active  after  insert as 
    -- NB: changed 'before' to 'after' due to FK from TQ1 & TQ2 ==> TDX
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    -- which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new .id  );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for  tdx  active before update or delete as
    declare v_sel_id int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid; -- to be logged in case of exc.
            v_handle_mode='del in tq1, pid='||old.id; -- to be logged in case of exc.
            delete from tq1 q where q.pid = old .id ;

            v_handle_mode='del in tq2, pid='||old.id; -- to be logged in case of exc.
            delete from tq2 q where q.pid = old .id ;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid; -- to be logged in case of exc.
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              --v_handle_mode='ins tq1, del in tq2 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq2 where pid = new .id  as cursor c
              do begin
                  v_handle_mode='try ins tq1, del in tq2, id='||c.id; -- to be logged in case of exc.
                  insert into tq1(id, pid) values (c.id, c.pid); -- ::: NB ::: PK  c.id - does NOT change!
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              --v_handle_mode='ins tq2, del in tq1 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq1 where pid = new .id  as cursor c
              do begin
                  v_handle_mode='try ins tq2, del in tq1, id='||c.id; -- to be logged in case of exc.
                  insert into tq2(id, pid) values (c.id, c.pid);  -- ::: NB ::: PK  c.id - does NOT change!
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode);

      exception;

    end
end
^
set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- Creates new "doc" and filling it with random number of rows:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
        -- number of added rows = new.qty:
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.10 then add new "doc"
    -- else if :r < 0.90 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r <  0.10  ) then
        begin
          v_handle_mode='before call p_tmx_add_doc'; -- to be logged in case of exc.
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);
            select id from tmx where id >= :v_rnd_id order by id rows 1 into sel_id;
            if ( r <  0.90  ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id; -- to be logged in case of exc.
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id; -- to be logged in case of exc.
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation (violation of PRIMARY or UNIQUE KEY constraint "T1_XY" on table "T1")
                                ,335544349 -- no_dup (attempt to store duplicate value (visible to active transactions) in unique index "T2_XY") - without UNQ constraint
                                ,335544466 -- violation of FOREIGN KEY constraint @1 on table @2
                                ,335544838 -- Foreign key reference target does not exist (when attempt to ins/upd in DETAIL table FK-field with value for which parent ID has been changed or deleted - even in uncommitted concurrent Tx)
                                ,335544839 -- Foreign key references are present for the record  (when attempt to upd/del in PARENT table PK-field and rows in DETAIL (no-cascaded!) exists for old value)
                           ) -- in
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 100;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_ai'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1
);
-- replaced with FK: create index tq2_pid on tq2(pid);
commit;

recreate table tdx( -- detail for `tmx` table
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0)
);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate sequence g;
commit;

-- FK constraints:
alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;
alter table tq1 add constraint tq1_fk
    foreign key (pid) references tdx(id);
alter table tq2 add constraint tq2_fk
    foreign key (pid) references tdx(id);
commit;

-- Triggers:
set term ^;
create or alter trigger tdx_ai for tdx active after insert as
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    -- which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new.id );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for tdx active before update or delete as
    declare v_sel_id int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid; -- to be logged in case of exc.
            v_handle_mode='del in tq1, pid='||old.id; -- to be logged in case of exc.
            delete from tq1 q where q.pid = old.id;

            v_handle_mode='del in tq2, pid='||old.id; -- to be logged in case of exc.
            delete from tq2 q where q.pid = old.id;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid; -- to be logged in case of exc.
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              --v_handle_mode='ins tq1, del in tq2 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq2 where pid = new.id as cursor c
              do begin
                  v_handle_mode='try ins tq1, del in tq2, id='||c.id; -- to be logged in case of exc.
                  insert into tq1(id, pid) values (c.id, c.pid);
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              --v_handle_mode='ins tq2, del in tq1 - before cursor'; -- to be logged in case of exc.
              for select id, pid from tq1 where pid = new.id as cursor c
              do begin
                  v_handle_mode='try ins tq2, del in tq1, id='||c.id; -- to be logged in case of exc.
                  insert into tq2(id, pid) values (c.id, c.pid);
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode);

      exception;

    end
end
^
create or alter trigger  tmx_bud for tmx  active before update or delete as
  declare v_id int;
begin
    for
        select id from tdx d
        where d.pid = old.id
        for update with lock
        into v_id
    do begin
        -- nop! just ensure that we really lock all detail records --
    end
end
^
set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- Creates new "doc" and filling it with random number of rows:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
        -- number of added rows = new.qty:
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.10 then add new "doc"
    -- else if :r < 0.90 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.10) then
        begin
          v_handle_mode='before call p_tmx_add_doc'; -- to be logged in case of exc.
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);
            select id from tmx where id >= :v_rnd_id order by id rows 1 into sel_id;
            if ( r < 0.90 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id; -- to be logged in case of exc.
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id; -- to be logged in case of exc.
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation (violation of PRIMARY or UNIQUE KEY constraint "T1_XY" on table "T1")
                                ,335544349 -- no_dup (attempt to store duplicate value (visible to active transactions) in unique index "T2_XY") - without UNQ constraint
                                ,335544466 -- violation of FOREIGN KEY constraint @1 on table @2
                                ,335544838 -- Foreign key reference target does not exist (when attempt to ins/upd in DETAIL table FK-field with value for which parent ID has been changed or deleted - even in uncommitted concurrent Tx)
                                ,335544839 -- Foreign key references are present for the record  (when attempt to upd/del in PARENT table PK-field and rows in DETAIL (no-cascaded!) exists for old value)
                           ) -- in
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 30;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);

            select m.id
            from tmx m
            where m.id >= :v_rnd_id
                   and not exists(select * from gtt_trn_log g where g.sel_id = m.id) 
            order by id rows 1
            FOR UPDATE WITH LOCK
            into sel_id;

            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end

     insert into gtt_trn_log(sel_id) values(:sel_id); 

    suspend;

when any do
    begin
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode);

      exception;

    end
end

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_curr_trn int;
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);

            select m.id
            from tmx m
            where m.id >= :v_rnd_id
                   and not exists(select * from gtt_trn_log g where g.sel_id = m.id) 
            order by id rows 1
             FOR UPDATE WITH LOCK 
            into sel_id;

            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                     rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end
    if ( sel_id is NOT null ) then  insert into gtt_trn_log(sel_id) values(:sel_id); 

    suspend;

when any do
    begin
      v_curr_trn = current_transaction;
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode, trn_id)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode, :v_curr_trn);

      exception;

    end
end

recreate exception ex_bad_error_encountered 'at least one forbidden error found, gdscode=@1';
commit;
create or alter procedure p_tmx_hadle_doc as begin end;
create or alter procedure p_tmx_add_doc as begin end;
create or alter function fn_halt_test returns int as begin end;
set term ^;
execute block as
begin
  begin execute statement 'drop trigger tdx_bi'; when any do begin end end
  begin execute statement 'drop trigger tdx_bud'; when any do begin end end
  begin execute statement 'drop trigger tmx_bud'; when any do begin end end
end
^set term ;^
commit;

recreate table tq1(
    id int primary key using index tq1_pk,
    pid int,
    x int default 1,
    att_id int default current_connection,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tq1_pid on tq1(pid);
commit;

recreate table tq2(
    id int primary key using index tq2_pk,
    pid int,
    x int default 1,
    att_id int default current_connection,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tq2_pid on tq2(pid);
commit;


recreate table tdx(
    id int primary key using index tdx_pk,
    pid int,
    op int not null check(op in(1,2)),
    qty int not null check(qty>0),
    att_id int default current_connection,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
recreate table tlog(
    sel_id int,
    unit varchar(31),
    handle_mode varchar(50),
    fb_gdscode int,
    att_id int default current_connection,
    trn_id int default current_transaction,
    dts timestamp default 'now'
);
create index tlog_fb_gdscode on tlog(fb_gdscode);
commit;

recreate table tmx(
    id int primary key using index tmx_pk,
    op int not null check(op in(1,2)),
    att_id int default current_connection,
    trn_id int default current_transaction,
    dts timestamp default 'now',
    unique(id, op) using index tmx_unq_for_tdx_fk
);
create descending index tmx_id_desc on tmx(id);
commit;

recreate global temporary table gtt_trn_log(sel_id int primary key)
on commit delete rows;
commit;

recreate sequence g;
commit;

alter table tdx add constraint tdx_fk
    foreign key (pid, op) references tmx(id, op)
    on delete cascade
    on update cascade;
commit;

set term ^;
create or alter trigger tdx_bi for tdx active before insert as
    declare v_qty int;
    declare v_stt varchar(128);
begin
    -- Multiply rows in either in TQ1 or TQ2 - depending on new.op value
    -- (which is generated randomly and can be 1 or 2).
    -- Number of created rows = int values of new.qty,
    --  which is being inserted in tdx.qty field:
    v_stt = 'insert into ' || iif( new.op=1, 'tq1', 'tq2' ) || '(id, pid) values(:a_id, :a_pid)';
    v_qty = new.qty;
    while (v_qty>0) do
    begin
        execute statement (v_stt) ( a_id := gen_id(g,1), a_pid := new.pid );
        v_qty = v_qty - 1;
    end
end
^

create or alter trigger tdx_bud for tdx active before update or delete as
    declare v_sel_id int;
    declare v_curr_trn int;
    declare v_handle_mode varchar(50);
begin
    if (deleting) then  -- remove all rows in TQ1 and TQ2 which have pid = old.pid
        begin
            v_sel_id=old.pid;
            v_handle_mode='del in tq1, pid='||old.pid;
            rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
            delete from tq1 q where q.pid = old.pid;

            v_handle_mode='del in tq2, pid='||old.pid;
            rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
            delete from tq2 q where q.pid = old.pid;
        end
    else -- updating: MOVE all rows related to document, with DELETION in SOURCE
        begin
            v_sel_id=new.pid;
            if ( new.op = 1 ) then begin
              -- SOURCE = TQ2, TARGET = TQ1
              for select id, pid from tq2 where pid = new.pid as cursor c
              do begin
                  v_handle_mode='try: ins tq1, del in tq2, id='||c.id;
                  rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                  insert into tq1(id, pid) values (c.id, c.pid);
                  delete from tq2 where current of c;
              end
            end
            else begin
              -- SOURCE = TQ1, TARGET = TQ2
              for select id, pid from tq1 where pid = new.pid as cursor c
              do begin
                  v_handle_mode='try: ins tq2, del in tq1, id='||c.id;
                  rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                  insert into tq2(id, pid) values (c.id, c.pid);
                  delete from tq1 where current of c;
              end
            end
        end -- deleting or updating branch
when any do
    begin
      v_curr_trn = current_transaction;
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode, trn_id)
      values( :v_sel_id, 'tdx_bud', :v_handle_mode, gdscode, :v_curr_trn);

      exception;

    end
end

^

create or alter trigger tmx_bud for tmx active before update or delete as
  declare v_id int;
begin
    for
        select id from tdx d
        where d.pid = old.id
        for update with lock
        into v_id
    do begin
        -- nop! just ensure that we really lock all detail records --
    end
end
^
set term ;^
commit;
--------------------------------------------------------------------------------
set term ^;
create or alter procedure p_tmx_add_doc(m_rows int) returns(sel_id int)
as
    declare v_op int;
    declare i int;
    declare const_qty int = 100;
begin
    -- NB: trigger tdx_bi will "multiply" rows in either TQ1 or TQ2,
    -- number of added rows = new.qty:
    insert into tmx(id, op) values( gen_id(g,1), iif(rand()<0.5, 1, 2))
    returning id, op into sel_id, v_op;
    i=0;
    while (i < m_rows) do
    begin
        insert into tdx(id, pid, op, qty) values( gen_id(g,1), :sel_id, :v_op, 1+rand()*(:const_qty-1) );
        i=i+1;
    end
    suspend;
end
^

create or alter procedure  p_tmx_hadle_doc  returns(sel_id int, result char(3))
as
    declare v_curr_trn int;
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);

            select m.id
            from tmx m
            where m.id >= :v_rnd_id
                   and not exists(select * from gtt_trn_log g where g.sel_id = m.id) 
            order by id rows 1
            FOR UPDATE WITH LOCK
            into sel_id;

            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                     rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end
     if ( sel_id is NOT null ) then insert into gtt_trn_log(sel_id) values(:sel_id); 

    suspend;

when any do
    begin
      v_curr_trn = current_transaction;
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode, trn_id)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode, :v_curr_trn);

      exception;

    end
end

^

create or alter function fn_halt_test returns int
as
    declare result int;
begin
    -- Used in .sql to immediatelly stop test (via set bail on) if one of the
    -- following errors registered in tlog: PK / UQ / FK violation.
    select first 1 g.fb_gdscode
    from tlog g
                       where g.fb_gdscode
                       in (
                                 335544665 -- unique_key_violation
                                ,335544349 -- no_dup 
                                ,335544466 -- violation of FOREIGN KEY
                                ,335544838 -- Foreign key reference target does not exist 
                                ,335544839 -- Foreign key references are present for the record
                           )
    into result;
    return result;
end
^
set term ;^
commit;

------------------------------- initial data filling ---------------------------
set term ^;
execute block as
    declare n int = 500;
    declare const_qty int = 100;
    declare i int;
begin
    while (n>0) do
    begin
        execute procedure p_tmx_add_doc(1 + rand()*(:const_qty-1)) returning_values i;
        n = n-1;
    end
end
^set term ;^
commit;

set term ^;
execute block as
    declare n int = -1;
begin
    n=fn_halt_test();
    if ( n > 0 ) then
      exception ex_bad_error_encountered using (:n);
end
^ set term ;^
set bail off;

commit; set transaction snapshot no wait;
set term ^;
execute block returns(trn_id int, dts varchar(12), sel_id int, result varchar(3))
as
  declare n int = 10;
begin
  trn_id = current_transaction;
  while ( n>0 ) do begin
    select sel_id, result from  p_tmx_hadle_doc  into sel_id, result;
    dts=substring(cast(cast('now' as timestamp) as varchar(24)) from 12 for 12);
    suspend;
    n=n-1;
  end
end
^ set term ;^

setlocal enabledelayedexpansion enableextensions

@rem path to ISQL utility, without trailing backslash:
set fbc=C:\MIX\firebird\fb25\bin

@rem path where temp logs will be created:
set tmpdir=c:\temp\tq

md %tmpdir% 2>nul
del %tmpdir%\tq_run_*.log 2>nul
del %tmpdir%\tq_run_*.err 2>nul
if not .%1.==.. set /a winq=%1
if .%winq%.==.0. set winq=10
@echo off
for /l %%i in (1, 1, %winq%) do (
  set str=%fbc%\isql 192.168.0.220/3333:/var/db/fb30/tq.fdb -n -i tq_run.sql 1^>%tmpdir%\tq_run_%%i.log 2^>%tmpdir%\tq_run_%%i.err
  echo str = !str!
  start /min cmd /c !str!
)
goto end
:end

            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                    rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end

2014-08-12T 18:07:00.3910 (29313:0x7f7e2da16388) START_TRANSACTION 
       /var/db/fb30/tq.fdb (ATT_483, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
       C:\1INSTALL\FB25SNAP\bin\isql.exe:2232
               (TRA_16936, CONCURRENCY | NOWAIT | READ_WRITE)
. . .
2014-08-12T 18:07:31.1420  (29313:0x7f7e2da16388) SET_CONTEXT
        /var/db/fb30/tq.fdb (ATT_483, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2232
                (TRA_16936, CONCURRENCY | NOWAIT | READ_WRITE)
 -- этот "SET_CONTEXT" значит, что только что было успешно выполнено: select id from TMX where id = 351821 FOR UPDATE WITH LOCK: 
[USER_TRANSACTION]  DEBUG_TRACED_ACTION = "upd in tmx, id=351821" 

-- А начало before_update_delete-триггера значит, что вслед за ним будет уже делаться
-- вот это:  update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id 
2014-08-12T 18:07:31.1420  (29313:0x7f7e2da16388)  EXECUTE_TRIGGER_START 
        /var/db/fb30/tq.fdb (ATT_483, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2232
                (TRA_16936, CONCURRENCY | NOWAIT | READ_WRITE)
        TMX_BUD FOR TMX (BEFORE UPDATE)
-- Дальше идёт уже переброска данных из TQ1 в TQ2. В том числе там ниже будет строка с id=351866:
2014-08-12T18:07:31.1420 (29313:0x7f7e35281c18) SET_CONTEXT
        /var/db/fb30/tq.fdb (ATT_441, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:1756
                (TRA_16815, CONCURRENCY | NOWAIT | READ_WRITE)
[USER_TRANSACTION] DEBUG_TRACED_ACTION = "try: ins tq2, del in tq1, id=1863100"
. . . 

 -- триггер завершился УСПЕШНО (хоть и молотило всё 25 сек) 
2014-08-12T18:07:56.4590 (29313:0x7f7e2da16388) EXECUTE_TRIGGER_FINISH
        /var/db/fb30/tq.fdb (ATT_483, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2232
                (TRA_16936, CONCURRENCY | NOWAIT | READ_WRITE)
        TDX_BUD FOR TDX (BEFORE UPDATE)
  25314 ms, 28 write(s), 73735 fetch(es), 16582 mark(s)

 -- процедура, обновлявшая мастер-запись в TMX для id = 351821, также завершается УСПЕШНО: 
2014-08-12T18:07:56.9660 (29313:0x7f7e2da16388) EXECUTE_PROCEDURE_FINISH
        /var/db/fb30/tq.fdb (ATT_483, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2232
                (TRA_16936, CONCURRENCY | NOWAIT | READ_WRITE)

 -- закончился execute block, после выхода из которого в .sql-скрипте всегда делается COMMIT: 
2014-08-12T 18:07:58.4750 (29313:0x7f7e2da16388) COMMIT_TRANSACTION 
       /var/db/fb30/tq.fdb (ATT_483, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
       C:\1INSTALL\FB25SNAP\bin\isql.exe:2232
               (TRA_16936, CONCURRENCY | NOWAIT | READ_WRITE)
   1317 ms, 2489 write(s), 9 fetch(es), 6 mark(s)

2014-08-12T 18:07:48.7540 (29313:0x7f7e50784480) START_TRANSACTION 
       /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
       C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
               (TRA_17276, CONCURRENCY | NOWAIT | READ_WRITE)
. . .
 -- этот "SET_CONTEXT" значит, что только что было успешно выполнено: select id from TMX where id = 351821 FOR UPDATE WITH LOCK: 
 -- КАК ТАКОЕ МОГЛО ПРОИЗОЙТИ, ЕСЛИ ЭТА ЗАПИСЬ СЕЙЧАС ЗАЛОЧЕНА ТРАНЗАКЦИЕЙ 16936 
-- И ДЛЯ НЕЁ УСПЕШНО БЫЛА ВЫПОЛНЕНА ПРОЦЕДУРА ОБНОВЛЕНИЯ ДАННЫХ В ТАБЛИЦЕ `TMX` 
-- И В ПОДЧИНЁННЫХ ЕЙ TQ1 & TQ2 ? 
2014-08-12T 18:07:57.0020  (29313:0x7f7e50784480) SET_CONTEXT
        /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
                (TRA_17276, CONCURRENCY | NOWAIT | READ_WRITE)
[USER_TRANSACTION]  DEBUG_TRACED_ACTION = "upd in tmx, id=351821" 

 -- начинаем (почему-то!) переброску данных, хотя как мы могли вообще сюда попасть ?! 
2014-08-12T 18:07:57.0020  (29313:0x7f7e50784480) EXECUTE_TRIGGER_START
        /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
                (TRA_17276, CONCURRENCY | NOWAIT | READ_WRITE)
        TMX_BUD FOR TMX (BEFORE UPDATE)

2014-08-12T18:07:57.0020 (29313:0x7f7dce639db8) SET_CONTEXT
        /var/db/fb30/tq.fdb (ATT_510, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
        C:\MIX\firebird\fb25\bin\isql.exe:1904
                (TRA_17213, CONCURRENCY | NOWAIT | READ_WRITE)
[USER_TRANSACTION] DEBUG_TRACED_ACTION = "try: ins tq2, del in tq1, id=3908179"

 -- обломались в триггере (см ниже сообщение о PK violation): 
2014-08-12T18:07:57.0420 (29313:0x7f7e50784480) FAILED EXECUTE_TRIGGER_FINISH
        /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
                (TRA_17276, CONCURRENCY | NOWAIT | READ_WRITE)
        TDX_BUD FOR TDX (BEFORE UPDATE)
     36 ms, 1 write(s), 293 fetch(es), 8 mark(s)

 -- облом перешёл в ХП: 
2014-08-12T18:07:57.1000 (29313:0x7f7e50784480) FAILED EXECUTE_PROCEDURE_FINISH
        /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
                (TRA_17276, CONCURRENCY | NOWAIT | READ_WRITE)
Procedure P_TMX_HADLE_DOC:
    105 ms, 4 write(s), 2835 fetch(es), 412 mark(s)

 -- облом показался на клиенте: 
2014-08-12T18:07:57.1050 (29313:0x7f7e50784480) ERROR AT JStatement::fetch
        /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
        C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
335544665 : violation of PRIMARY or UNIQUE KEY constraint "INTEG_112" on table "TQ2"
335545072 : Problematic key value is ("ID" = 351866)
335544842 : At trigger 'TDX_BUD' line: 36, col: 19
At trigger 'CHECK_55'
At procedure 'P_TMX_HADLE_DOC' line: 42, col: 21
335544842 : At trigger 'TDX_BUD' line: 48, col: 7
At trigger 'CHECK_55'
At procedure 'P_TMX_HADLE_DOC' line: 42, col: 21
. . .

 -- закончился execute block, после выхода из которого в .sql-скрипте всегда делается COMMIT: 
2014-08-12T 18:07:57.1150  (29313:0x7f7e50784480) COMMIT_TRANSACTION
       /var/db/fb30/tq.fdb (ATT_470, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
       C:\1INSTALL\FB25SNAP\bin\isql.exe:2744
               (TRA_17276, CONCURRENCY | NOWAIT | READ_WRITE)
      7 ms, 54 write(s), 9 fetch(es), 6 mark(s)

create or alter procedure p_tmx_hadle_doc returns(sel_id int, result char(3))
as
    declare v_curr_trn int;
    declare v_min_id double precision;
    declare v_max_id double precision;
    declare v_rnd_id double precision;
    declare r double precision;
    declare v_sel_id int;
    declare const_qty int = 100;
    declare v_handle_mode varchar(50);
begin
    -- Performs action with ONE "doc", depending on random value (:r)
    -- if :r < 0.25 then add new "doc"
    -- else if :r < 0.75 then make update field tmx.op (this fires cascade update
    --                   in detail (tdx) and "exchange" operation between tq1 & tq2)
    -- else - remove "doc" (this fires cascade deletion in detail (tdx) and also
    --                      removing of all corresp. rows in tq1 & tq2)
    r = rand();
    if (r < 0.25) then
        begin
          select p.sel_id,'add' from p_tmx_add_doc( 1 + rand()*(:const_qty-1) ) p
          into sel_id, result;
        end
    else
        begin
            select id from tmx order by id rows 1 into v_min_id;
            select id from tmx order by id desc rows 1 into v_max_id;
            v_rnd_id = v_min_id - 0.5 + rand() * ( v_max_id - v_min_id + 1);

            select m.id
            from tmx m
            where m.id >=  :v_rnd_id 
                  and not exists(select * from gtt_trn_log g where g.sel_id = m.id)
            order by id rows 1
            FOR UPDATE WITH LOCK
            into sel_id;

            if ( r < 0.75 ) then
                begin
                    v_handle_mode='upd in tmx, id='||sel_id;
                    rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    update tmx m set m.op = iif(m.op=1, 2, 1) where id = :sel_id
                    returning 'upd' into result;
                end
            else
                begin
                    v_handle_mode='del in tmx, id='||sel_id;
                    rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', v_handle_mode);
                    delete from tmx m where id = :sel_id
                    returning 'del' into result;
                end
        end
    if ( sel_id is NOT null ) then insert into gtt_trn_log(sel_id) values(:sel_id);

    suspend;

when any do
    begin
      v_curr_trn = current_transaction;
      rdb$set_context('USER_TRANSACTION','DEBUG_TRACED_ACTION', 'gds='||gdscode||' for "'||:v_handle_mode||'"');
      in autonomous transaction do
      insert into tlog(sel_id, unit, handle_mode, fb_gdscode, trn_id)
      values( :sel_id, 'p_tmx_hadle_doc', :v_handle_mode, gdscode, :v_curr_trn);

      exception;

    end
end