hvladТут весьма интересный эффект получается.После внедрёжа вот этого: http://svn.code.sf.net/p/firebird/code/firebird/trunk/ChangeLog 2014-08-12 10:21 hvlad
M src/jrd/Monitoring.cpp
Avoid info duplication when statements in call stack attached to different transactions (for example: monitoring snapshot is created in autonomous transaction)
- стало лучше. Но эффекты тоже стали еще более "интересными".
Дано:
1) master & detail таблицы, связаны по FK с on delete cascade; их имена: dbg_main & dbg_detl;
2) на detail-таблицу наверчен триггер before delete or update, который вызывает несколько процедур:
1.
2.
3.
4.
5.
create or alter trigger dbg_detl_bud for dbg_detl active before update or delete
as
begin
execute procedure p_01; -- а из неё вызываются ХП: P_02 ==> P_03 ==> P_04 ==> DBG_GET_STACK
end
3) в master-таблицу вводится одна запись, в detail - две подчинённые к ней.
4) процедуры P_01...P_04, DBG_GET_STACK, и вообще весь 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.
set term ^;
create or alter procedure dbg_get_stack as begin end^
create or alter procedure p_01 as begin end^
create or alter procedure p_02 as begin end^
create or alter procedure p_03 as begin end^
create or alter procedure p_04 as begin end^
create or alter procedure p_00 as begin end^
set term ;^
commit;
------------------------------------------------------------------------
recreate table dbg_detl(id int primary key, pid int);
recreate table dbg_main(id int primary key);
commit;
alter table dbg_detl add constraint dbg_detl_fk foreign key (pid)
references dbg_main(id)
on delete cascade;
commit;
------------------------------------------------------------------------
recreate table dbg_stack(
whoami varchar(31)
,call_level int
,statement_id int
,call_id int
,object_name char(31)
,object_type smallint
,source_line int
,source_column int
,trn_id int default current_transaction
);
commit;
set term ^;
create or alter procedure dbg_get_stack(
a_whoami varchar(31)
)
as
declare call_level int;
declare mon$statement_id type of column mon$call_stack.mon$statement_id;
declare mon$call_id type of column mon$call_stack.mon$call_id;
declare mon$object_name type of column mon$call_stack.mon$object_name;
declare mon$object_type type of column mon$call_stack.mon$object_type;
declare mon$source_line type of column mon$call_stack.mon$source_line;
declare mon$source_column type of column mon$call_stack.mon$source_column;
declare v_stt varchar(4096); -- use dyn SQL for watching in trace!
begin
v_stt = 'with recursive'
|| ' r as ('
|| ' select 1 call_level,'
|| ' c.mon$statement_id,'
|| ' c.mon$call_id,'
|| ' c.mon$object_name,'
|| ' c.mon$object_type,'
|| ' c.mon$source_line,'
|| ' c.mon$source_column'
|| ' from mon$call_stack c'
|| ' where c.mon$caller_id is null'
|| ' UNION ALL'
|| ' select h.call_level+1,'
|| ' c.mon$statement_id,'
|| ' c.mon$call_id,'
|| ' c.mon$object_name,'
|| ' c.mon$object_type,'
|| ' c.mon$source_line,'
|| ' c.mon$source_column'
|| ' from mon$call_stack c'
|| ' join r h'
|| ' on c.mon$caller_id = h.mon$call_id'
|| ')'
|| ' select'
|| ' r.call_level'
|| ' ,r.mon$statement_id'
|| ' ,r.mon$call_id'
|| ' ,r.mon$object_name'
|| ' ,r.mon$object_type'
|| ' ,r.mon$source_line'
|| ' ,r.mon$source_column'
|| ' from r'
;
for
execute statement (v_stt)
with autonomous transaction -- ::: NB :::
into
:call_level
,:mon$statement_id
,:mon$call_id
,:mon$object_name
,:mon$object_type
,:mon$source_line
,:mon$source_column
do begin
insert into dbg_stack(
whoami
,call_level
,statement_id
,call_id
,object_name
,object_type
,source_line
,source_column
--,trn_id
)
values(
:a_whoami
,:call_level
,:mon$statement_id
,:mon$call_id
,:mon$object_name
,:mon$object_type
,:mon$source_line
,:mon$source_column
--,current_transaction
);
end
end
^
set term ;^
commit;
---------------------------------
set term ^;
create or alter procedure p_04 as
declare n int;
begin
-- dummy row 1
-- dummy row 2
-- dummy row 3
-- dummy row 4
execute procedure dbg_get_stack( 'p_04' );
end
^
create or alter procedure p_03 as
declare n int;
begin
-- dummy row 1
-- dummy row 2
-- dummy row 3
execute procedure p_04;
end
^
create or alter procedure p_02 as
declare n int;
begin
-- dummy row 1
-- dummy row 2
execute procedure p_03;
end
^
create or alter procedure p_01 as
declare n int;
begin
-- dummy row 1
execute procedure p_02;
end
^
---------------------
create or alter trigger dbg_detl_bud for dbg_detl active before update or delete
as
begin
execute procedure p_01;
end
^
set term ;^
commit;
insert into dbg_main(id) values(1);
insert into dbg_detl(id, pid) values(1,1);
insert into dbg_detl(id, pid) values(2,1);
commit;
Далее делаем два варианта удаления из dbg_detl:
1) непосредственно удаляя из неё, т.е.: delete from dbg_detl;
2) опосредованно через delete from dbg_main, что вызовет каскадный триггер на удаление подчинённых строк в dbg_detl (и это удаление уже приведёт к срабатыванию вышепоказанного триггерка dbg_detl_bud).
Скрипт для варианта-1 :
1.
2.
3.
4.
5.
set width whoami 15; set width object_name 20;
select current_transaction from rdb$database;
delete from dbg_detl;
select whoami,call_level,object_name,source_line,source_column,trn_id from dbg_stack;
rollback;
Его результат: всё хорошо, кроме номера транзакции в dbg_stack - см в конце поста "вопрос-2" 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CURRENT_TRANSACTION
===================
62761
WHOAMI CALL_LEVEL OBJECT_NAME SOURCE_LINE SOURCE_COLUMN TRN_ID
=============== ============ ==================== ============ ============= ============
p_04 1 DBG_DETL_BUD 4 3 62761
p_04 2 P_01 5 5 62761
p_04 3 P_02 6 9 62761
p_04 4 P_03 7 13 62761
p_04 5 P_04 8 17 62761
p_04 6 DBG_GET_STACK 47 5 62761
p_04 1 DBG_DETL_BUD 4 3 62761
p_04 2 P_01 5 5 62761
p_04 3 P_02 6 9 62761
p_04 4 P_03 7 13 62761
p_04 5 P_04 8 17 62761
p_04 6 DBG_GET_STACK 47 5 62761
Скрипт для варианта-2 :
1.
2.
3.
4.
5.
6.
set width whoami 15; set width object_name 20;
select current_transaction from rdb$database;
delete from dbg_main;
select whoami,call_level,object_name,source_line,source_column,trn_id from dbg_stack;
rollback;
select * from dbg_stack;
Его результат: 1.
2.
3.
CURRENT_TRANSACTION
===================
62764
(да, это - весь результат: никаких данных в dbg_stack нету!)
Фрагмент трейса для варианта-1 : 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
2014-08-13T17:38:04.1130 (30975:0x7fb5e238a9d0) EXECUTE_STATEMENT_FINISH
oltp30 (ATT_319, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2916
(TRA_62763, CONCURRENCY | WAIT | READ_WRITE)
Statement 927:
-------------------------------------------------------------------------------
with recursive r as ( select 1 call_level, c.mon$statement_id, c.mon$call_id, c.mon$object_name, c.mon$object_type, c.mon$source_line, c.mon$source_column from mon$call_stack c where c.mon$caller_id is null UNION ALL select h.call_level+1, c.mon$statement_id, c.mon$call_id, c.mon$object_name, c.mon$object_type, c.mon$source_line, c.mon$source_column from mon$call_stack c join r h on c.mon$caller_id = h.mon$call_id) select r.call_level ,r.mon$statement_id ,r.mon$call_id ,r.mon$object_name ,r.mon$object_type ,r.mon$source_line ,r.mon$source_column from r
6 records fetched
0 ms, 1 fetch(es)
2014-08-13T17:38:04.1130 (30975:0x7fb5e238a9d0) EXECUTE_PROCEDURE_FINISH
oltp30 (ATT_319, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2916
(TRA_62761, CONCURRENCY | WAIT | READ_WRITE)
Procedure DBG_GET_STACK:
param0 = varchar(31), "p_04"
0 ms, 18 fetch(es), 6 mark(s)
Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
DBG_STACK 6
Фрагмент трейса для варианта-2 : 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
2014-08-13T17:40:57.8280 (30975:0x7fb5e238dcc0) EXECUTE_STATEMENT_FINISH
oltp30 (ATT_319, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2916
(TRA_62766, CONCURRENCY | WAIT | READ_WRITE)
Statement 933:
-------------------------------------------------------------------------------
with recursive r as ( select 1 call_level, c.mon$statement_id, c.mon$call_id, c.mon$object_name, c.mon$object_type, c.mon$source_line, c.mon$source_column from mon$call_stack c where c.mon$caller_id is null UNION ALL select h.call_level+1, c.mon$statement_id, c.mon$call_id, c.mon$object_name, c.mon$object_type, c.mon$source_line, c.mon$source_column from mon$call_stack c join r h on c.mon$caller_id = h.mon$call_id) select r.call_level ,r.mon$statement_id ,r.mon$call_id ,r.mon$object_name ,r.mon$object_type ,r.mon$source_line ,r.mon$source_column from r
0 records fetched
0 ms, 1 fetch(es)
2014-08-13T17:40:57.8280 (30975:0x7fb5e238dcc0) EXECUTE_PROCEDURE_FINISH
oltp30 (ATT_319, SYSDBA:NONE, NONE, TCPv4:192.168.43.96)
C:\MIX\firebird\fb25\bin\isql.exe:2916
(TRA_62764, CONCURRENCY | WAIT | READ_WRITE)
Procedure DBG_GET_STACK:
param0 = varchar(31), "p_04"
0 ms
при удалении, которое вызвано каскадным триггером (вариант-2), записи в mon$call_stack недоступны. По кр. мере, вот этот запрос ("якорь" рекурсии): 1.
2.
3.
4.
5.
6.
7.
8.
9.
select 1 call_level,
c.mon$statement_id,
c.mon$call_id,
c.mon$object_name,
c.mon$object_type,
c.mon$source_line,
c.mon$source_column
from mon$call_stack c
where c.mon$caller_id is null
- возвращает ноль строк и рекурсия, след-но, тоже ничего не вернёт.
ВОПРОС-1 : Как заполучить стек вызовов в таком случае, как вариант-2 ?
Кроме того, хотя и оффтоп, но скажу сюда и ВОПРОС-2 , чтоб не забылось.
Если я выполняю вот это вот (см ХП dbg_get_stack в вышеприведенном DDL):
1.
2.
3.
4.
5.
6.
for
execute statement (v_stt)
with autonomous transaction
into . . .
do begin
insert into ...
end
- то согласно доке: http://www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html#langrefupd25-psql-execstat-with-trans WITH AUTONOMOUS TRANSACTION causes a separate transaction to be started, with the same parameters as the current transaction- должна была стартовать НОВАЯ транзакция. Но по результатам варианта-1 видим, что этого не случилось. Это так и должно быть ? |
