hi all
Имеется след. схема:
tmx - таблица заголовков "документов"; имеет поле OP, принимающее одно из двух значений: 1 или 2;
tdx - подчиненная ей деталь, связана по FK с tmx *двумя* каскадами (см ниже); имеет такое же поле OP, как и tmx, а также int-поле QTY;
tq1 и tq2 - две таблицы одинаковой структуры, в которых хранятся строки, логически подчинённые tdx, но НЕ связанные с этой таблицей FK.
Когда в tdx добавляется строка с каким-то целочисленным QTY, то если new.op = 1, будет добавлено N записей в таблицу TQ1, а если new.op=2 - то N записей в таблицу TQ2. При этом N всегда = new.qty (напомню, QTY - целочисленное).
Записи в таблице tmx могут, помимо добавления и удаления, также обновляться, но при этом меняется только содержимое поля OP: если оно = 1, то новое значение будет равно 2, и наоборот. Каскадный триггер на update приведёт к обновлению этого же поля (OP) в таблице tdx.
На таблице tdx определен триггер before update or delete.
Этот триггер:
1) при удалении - грохает все записи в TQ1 & TQ2, относящиеся к tdx.pid;
2) при обновлении (а это м.б. только поле OP) - выполняет перенос строк:
2.1) если new.op = 1, то из TQ2 в TQ1;
2.2) если new.op = 2, то из TQ1 в TQ2.
Перенос строк TQ1<-->TQ2 делается БЕЗ "искажения" записей, т.е. с сохранением первичных ключей, которые были им присвоены при вставке (через gen_id). Выполняется этот перенос так (пример для SOURCE = TQ1, TARGET = TQ2):
1.
2.
3.
4.
5.
for select id, pid from tq1 where pid = new.pid as cursor c
do begin
insert into tq2(id, pid) values (c.id, c.pid);
delete from tq1 where current of c;
end
Кроме того, есть еще таблица TLOG для логирования возникающих исключений в автономной транзакции.
Эта схема, при работе транзакций с TIL = SNAPSHOT, будет допускать исключения вида lock_conflict, однако в ней не должны создаваться исключения вида PK/UK violation, либо FK violation. Потому что сначала лочатся родительские записи, и только потом начинается перенос строк TQ1<-->TQ2 (либо их удаление).
Однако, при интенсивной борьбе 50 молотилок можно быстро получить по лбу исключением unique_key_violation (gds=335544665).
Причину этого я понять не могу, поможыте разобраться, плз.
Вот
DDL :
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
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;
(он отрабатывает на достаточно серьёзном серваке примерно за 1 минуту, т.к. добавляет много начальных данных)
Вот запрос, который надо "размножить" 100500 млн раз, чтобы натравливание на него isql'ей не приводило к частым переконнектам:
file = `tq_run.sql`; требуется размножить его текст многократно, чтобы получить большой скрипт
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
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 ;^
--------------------
И вот батник:
file = `tq_run.bat`, запускать с параметром, равным числу запускаемых окон-молотилок
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
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
Батник надо подправить под свои переменные окружения.
Если запустить этот батник вот так: tq_run.bat 50 - то будет открыто 50 isql'ей, которые начнут молотьбу.
В аттаче - сжатый скрипт tq_run.sql, который есть результат 100500 размножений вышеприеденного .sql.
Если в базе будет зарегистрировано одно из след. исключений:
1.
2.
3.
4.
5.
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)
- то тест автоматически остановится.
В трейсе при этом будет:
1.
2.
3.
4.
5.
2014-08-11T23:59:14.2470 (5617:0x7f4f0e46f838) ERROR AT JStatement::execute
/var/db/fb30/tq.fdb (ATT_304, SYSDBA:NONE, NONE, TCPv4:192.168.43.62)
C:\1INSTALL\FB25SNAP\bin\isql.exe:2136
335544517 : exception 25
335544382 : EX_BAD_ERROR_ENCOUNTERED
335545016 : at least one forbidden error found, gdscode=335544665
Ну так вот.
При запуске 50 окон достаточно быстро (через 5-10 минут) тест самоостановится и в таблице TLOG будет вот это:
1.
select * from tlog where fb_gdscode=335544665
SEL_IDUNITHANDLE_MODEFB_GDSCODETRN_IDDTS1165462tdx_budtry: ins tq2, del in tq1, id=11663973355446651842812.08.2014 00:06:06.8621165462p_tmx_hadle_docupd in tmx, id=11654623355446651843012.08.2014 00:06:06.8922476196tdx_budtry: ins tq1, del in tq2, id=25775263355446651867912.08.2014 00:07:06.4623147023tdx_budtry: ins tq2, del in tq1, id=31792543355446651868012.08.2014 00:07:06.5273147023p_tmx_hadle_docupd in tmx, id=31470233355446651868112.08.2014 00:07:06.5542476196p_tmx_hadle_docupd in tmx, id=24761963355446651868412.08.2014 00:07:06.6262534478tdx_budtry: ins tq1, del in tq2, id=25881503355446651868612.08.2014 00:07:07.5462534478p_tmx_hadle_docupd in tmx, id=25344783355446651868712.08.2014 00:07:07.5701831720tdx_budtry: ins tq1, del in tq2, id=19156083355446651869812.08.2014 00:07:13.6041831720p_tmx_hadle_docupd in tmx, id=18317203355446651869912.08.2014 00:07:13.642
Как такое может происходить ?
PS.
В ddl-коде, приведенном выше, на самом деле есть одна "ошибка проектирования": таблицы TQ1 & TQ2 должны ссылаться полем PID не на TDX.
P ID, а на TDX.
ID . Из-за этой ошибки получается, что при удалении уже первой из строки документа в TDX сразу грохаются (или же переносятся между TQ1 и TQ2) не только строки, относящиеся к "первой из деталей", но и строки ко всем остальным "деталям" данного документа. То есть, TQ1.PID & TQ2.PID ссылаются на самом деле не на строку-деталь, а на строку-заголовок документа.
Однако, сиё не может (как мну кажется) влиять на появление PK/UK-violation'ов.
