(по мотивам рассказов об
удивительных фокусах с нарушениями ПК там, где они "ну никак и никогда" не могли происходить).
Товарищи! Прошу поднять бокалы, ибо "зарегистрировано открытие"!
Допустим, Tх-1 делает в достаточно длительном цикле:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
execute block as
. . .
begin
while (1=1) do
begin
fetch <cursor on table t_source> into <vars>;
if (row_count) = 0 then leave;
delete from t_source where current of <cursor on table t_source>;
insert into t_target(...) values ( <vars> );
when any do
exception;
end
Если курсорный цикл споткнётся на 100500-й итерации обо что-то (да хоть бы на лок-конфликт), то управление прыгнет в when-блок.
Ввиду наличия в этом блоке команды 'exception;' начнётся выполнение откатов всех 100499 изменений, а именно:
* отмена вставок ключей в индексы таблицы t_target;
* отмена вставок собственно данных в таблицу t_target;
* отмена удалений в таблице t_source.
На обычной windows-тачке (рабочей станции) эти действия будут выполняться для 200 тыс строк около 5 сек.
Ключевой ВОПРОС: в какой последовательности будут выполнены откаты, то есть будут ли они идти в порядке, обратном хронологии этих изменений ?
Если подробно, то интересовало:
1) на уровне отдельной записи таблицы -- будет ли соблюден "реверс" для вставок ключей в индексы и собственно данных (т.е. если при insert'e сначала корректировали страницу с данными, а затем полезли в индексы, то что при отмене будет первым и что вторым ?);
2) на уровне всех изменявшихся строк некоторой таблицы -- будет ли соблюден "реверс" для того порядка, в котором обрабатывали строки ? например, если обработали строки в следующем порядке id: 752, 284, 911 -- то будет ли откат в обратном порядке: 911 ==> 284 ==> 752 ?
3) на уровне отдельных таблиц: если сначала поменяли в T1, затем в T2, а после стартовали изменения в T3, но они обломались - будут ли откаты идти в порядке Т3 ==> T2 ==> T1 ?
4) на уровне стейтментов -- будет ли соблюден "реверс" порядка выполненных операторов (т.е. если делали так: {delete, insert}, то откатываем так: {insert, delete} - да или нет ?)
Эмпирически была получена догадка, подтверждённая затем в интенсивной переписке "Э & K" и и теперь ставшая ОТВЕТОМ:
1) при откате изменений уровня записи -- реверса никакого
нет , но порядок таки
есть : сначала бекаут записи, потом сборка мусора в индексах -- т.е.
порядок точно такой же "прямой" , как и при выполнении самого insert/update-стейтмента;
2) при откате изменений уровня строк -- изменения,
скорее всего , будут по возрастанию rdb$db_key, но для приложения это всё равно, что "непредсказуемо";
3) при откате уровня таблиц -- изменения,
скорее всего , будут по возрастанию rdb$relation_id. Для приложения это тоже самое, что и в предыдущем пункте - "непредсказемо";
4) при откате уровня стейтментов -- никакого порядка нет
Всё вышесказанное означает, что вот этот код внутри цикла (см выше):
1.
2.
delete from t_source where current of <cursor on table t_source>;
insert into t_target(...) values ( <vars> );
--
может привести при конкурентной работе даже двух транзакций к тому, что Тх-2 получит исключение
PK-violation на операторе insert into t_target(...).
И произойдёт это в том случае, когда обломавшаяся конкурентка Тх-1:
1) начала делать откаты своих 100500 изменений;
2) уже отменила удаление некоторой записи ("delete from t_source ...");
3) еще НЕ успела отменить наличие ключей в таблице t_target, которые были всунуты туда командой "insert into t_target(...)".
Постулат о том, что НЕЛЬЗЯ рассчитывать на какой-либо осмысленный порядок отмен изменений, на удивление легко подтвердился.
DDL:
1) две таблицы (`ts` & `tt`) с одинаковой структурой и индексами, в одну заливаем "много строк", вторая пуста.
2) процедурка p_handle, переносящая все строки из `ts` в `tt`, и гарантированно обламывающаяся на последней итерации курсора
3) when-блок в p_handle, содержащий exception. Именно этот exception будет выполнять откаты длительностью в несколько секунд, что позволит спокойно переключиться в другое окно и попробовать там сделать "доброе дело"
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.
create or alter procedure p_handle as begin end;
recreate sequence g;
recreate table ts(id int not null, doc_id int, sgn smallint default 0);
commit;
recreate table tt(id int primary key using index tt_pk, doc_id int, sgn smallint default 0);
create index tt_doc_id_asc on tt(doc_id, sgn);
create descending index tt_doc_id_dec on ts(doc_id);
commit;
set term ^;
execute block as
declare n int = 1;
declare m int = 200000;
declare i int = 0;
declare k int;
declare d int = 0;
begin
while (i < n) do begin
k = 0;
while (k < m) do begin
insert into ts(id, doc_id, sgn) values( gen_id(g,1), :d, iif(:k < :m-1, 0, 1) );
k = k + 1;
end
i = i+1;
d = d + 1;
end
end
^
set term ;^
commit;
alter table ts add constraint ts_pk primary key (id) using index ts_pk;
create index ts_doc_id_asc on ts(doc_id, sgn);
create descending index ts_doc_id_dec on ts(doc_id, sgn);
commit;
set term ^;
create or alter procedure p_handle returns(selected_doc int, processed_rows int)
as
declare c_move cursor for( select s.id, s.sgn from ts s where s.doc_id = :selected_doc order by s.doc_id, s.sgn );
declare v_id int;
declare v_sgn smallint;
declare v_min int;
declare v_max int;
declare v_rnd double precision;
declare v_id1 int;
declare v_cnt int;
begin
-- select s.doc_id from ts s order by s.doc_id rows 1 into v_min;
-- select s.doc_id from ts s order by s.doc_id desc rows 1 into v_max;
-- v_rnd = rand()* ( v_max+0.5 - (v_min-0.5) );
--
-- select s.doc_id from ts s where s.doc_id >= :v_rnd order by s.doc_id rows 1 into selected_doc;
selected_doc = 0;
open c_move;
processed_rows = 0;
while (1=1) do begin
fetch c_move into v_id, v_sgn; if (row_count=0) then leave;
if ( processed_rows = 0 ) then v_id1 = v_id;
delete from ts where current of c_move;
if ( v_sgn = 0 ) then
insert into tt(id, doc_id, sgn) values( :v_id, :selected_doc, :v_sgn);
else
begin
rdb$set_context('USER_SESSION', 'DBG_FAULT_EXPR', cast('now' as timestamp) );
insert into tt(id, doc_id, sgn) values( :v_id1, :selected_doc, :v_sgn);
end
processed_rows = processed_rows + 1;
end
close c_move;
suspend;
when any do
begin
rdb$set_context('USER_SESSION', 'DBG_WHEN_BEGIN', cast('now' as timestamp) );
exception;
end
end
^
set term ;^
commit;
Теперь запускаем три окна.
session #0 : трейс с бублированием вывода в лог, конфиг трейса:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
database #
{
enabled = true
log_sweep = true
log_errors = true
time_threshold = 0
log_connections = true
log_transactions = true
log_context = true
log_statement_start = true
log_procedure_start = true
log_statement_finish = true
log_procedure_finish = true
print_perf = true
max_sql_length = 8192
max_log_size = 5000000
}
: ISQL и в нём всё готово для выполнения команды
SQL> in ts-tt-del1.sql; -- т.е. ввели, но Ентер пока не жмякаем
-- где "ts-tt-del1.sql" есть:
1.
2.
3.
4.
5.
6.
7.
rollback;
set transaction no wait;
delete from ts where id = 1
returning current_transaction, cast('now' as timestamp) as dts_of_deletion, id as deleted_id;
insert into tt(id) values( 1 )
returning current_transaction, cast('now' as timestamp) as dts_of_insertion, id as inserted_id;
rollback;
: ISQL и в нём всё готово для выполнения скрипта "ts-tt-test.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.
--- begin of script ts-tt-test.sql ---
rollback;
set transaction no wait;
-- эта ХП, как я уже говорил, будет удалять 199999 строк из `ts` и добавлять их содержимое в `tt`,
-- а на итерации номер 200000 она намеренно выполнит попытку нарушения ПК в таблице `tt`.
-- После чего управление переедет в when-блок этой ХП, там будет сфоткан момент времени,
-- но главное - после этого там будет вызов 'exception;'
select * from p_handle;
set term ^;
execute block as
begin
rdb$set_context('USER_SESSION', 'DBG_AFTER_PROC', cast('now' as timestamp) );
end
^ set term ;^
set list on;
select
current_transaction
,rdb$get_context('USER_SESSION', 'DBG_FAULT_EXPR' ) dts_fault_expr
,rdb$get_context('USER_SESSION', 'DBG_WHEN_BEGIN' ) dts_when_block
,rdb$get_context('USER_SESSION', 'DBG_AFTER_PROC' ) dts_after_proc
from rdb$database;
set list off;
rollback;
--- end of script ts-tt-test.sql ---
Теперь - поехали!
session #1:
session #0: (окно трейса) ждём, пока в нём не появится вот это:
1.
2.
3.
t0 (ATT_44, SYSDBA:NONE, NONE, TCPv4:127.0.0.1)
C:\MIX\firebird\fb30\isql.exe:4040
(TRA_1303, CONCURRENCY | NOWAIT | READ_WRITE)
[USER_SESSION] DBG_FAULT_EXPR = "2015-02-12 16:36:50.3400"
-- после чего
быстро переключаемся в session #2 и жмякаем там Ентер.
Если всё сделать быстро, то в
session #2 получим сначала по лбу:
1.
2.
3.
4.
5.
6.
7.
8.
9.
SQL> in ts-tt-del1.sql;
Statement failed, SQLSTATE = 40001
deadlock
-update conflicts with concurrent update
-concurrent transaction number is 1289
After line 2 in file ts-tt-del1.sql
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_75" on table "TT"
-Problematic key value is ("ID" = 1)
After line 3 in file ts-tt-del1.sql
#####################################################################################
А теперь, почтеннейшая публика, ВНИМАНИЕ! БЫСТРО повторяем ввод предыдущей команды в
session #2 , несколько раз.
И получаем там:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SQL> in ts-tt-del1.sql;
Statement failed, SQLSTATE = 40001
deadlock
-update conflicts with concurrent update
-concurrent transaction number is 1289
After line 2 in file ts-tt-del1.sql
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_75" on table "TT"
-Problematic key value is ("ID" = 1)
After line 3 in file ts-tt-del1.sql
/* это потому, что запись в таблице `ts` с ID=1 пока еще сдержит бек-версию */
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
SQL> in ts-tt-del1.sql;
CURRENT_TRANSACTION DTS_OF_DELETION DELETED_ID
=================== ========================= ============
1296 2015-02-12 16:32:35.8560 1
Statement failed, SQLSTATE = 23000
violation of PRIMARY or UNIQUE KEY constraint "INTEG_75" on table "TT"
-Problematic key value is ("ID" = 1)
After line 3 in file ts-tt-del1.sql
/* вот оно!!! отмена удаления записи в таблице `ts` с id=1 уже выполнено,
однако ключ в индексе таблицы `tt`, куда была добавлена эта запись скриптом session #1 ("in ts-tt-test.sql"),
еще НЕ вычищен. */
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SQL> in ts-tt-del1.sql;
CURRENT_TRANSACTION DTS_OF_DELETION DELETED_ID
=================== ========================= ============
1298 2015-02-12 16:32:38.3560 1
CURRENT_TRANSACTION DTS_OF_INSERTION INSERTED_ID
=================== ========================= ============
1298 2015-02-12 16:32:38.4030 1
/* ну, а здесь всё уже вычищено в session #1 и мы смогли как удалить эту запись из `ts`, так и добавить её в `tt` */
ЗЫ. Я искал из-за этого PK-violation ошибку в своём коде несколько недель. Затем стал терзаться сомнениями, что это в ФБ глюкобаг, т.к. исключение лезло только на 3.0 SC и никогда не встречалось в 3.0 SS. Но теперь спокойно воспроизвожу и там, и там - надо было просто дровишек побольше для SS подкинуть.
ЗЗЫ. Пойду напьюсь, что ле... Гора с плеч же, блин! :-)
