Этот статья посвящена проблеме согласованности данных при изменениях в СУБД Oracle. По мотивам интересного обсуждения на сайте Тома Кайта.
Я всегда исходил из предположения, что оператор типа update t set ... where ... обрабатывается следующим образом:
- Берется согласованный моментальный снимок таблицы на момент начала выполнения оператора
- Для каждой строки этого моментального снимка:
подождать снятия блокировки, если строка заблокирована другой транзакцией,
а затем изменить строку, если она удовлетворяет конструкции where.
Другими словами, любые изменения, сделанные транзакциями, начавшимися и зафиксированными после начала выполнения оператора (определяется по значению SCN), невидимы для оператора, поскольку они не входят в "согласованный моментальный снимок".
Следующий эксперимент (построенный на основе реальной ситуации) заставил меня задуматься об этом еще раз.
Мы будем изменять большую таблицу с помощью оператора update (в транзакции, обозначенной LONG), но мы заблокируем ее по ходу работы - она будет ждать, пока транзакция BLOCKER не снимет блокировку. Пока транзакция LONG ждет, мы изменим первую и последнюю строку с помощью другой транзакции, FIRSTLAST, зафиксируем ее, а затем откатим транзакцию BLOCKER, чтобы LONG могла продолжить работу.
Затем мы повторим те же действия, но зафиксируем транзакцию BLOCKER, вместо того, чтобы откатить ее.
create table t (id int, y int, filler char(10) default ' ') initrans 2 nologging
storage (initial 1M next 1m pctincrease 0 maxextents unlimited);
insert /*+ APPEND */ into t select rownum, 0, ' '
from all_objects,all_objects,all_objects where rownum <=1000000;
commit;
BLOCKER> update t set y = -1 where id = 500000;
LONG> update t set y = rownum where y = -1 or id=500000; -- она блокируется на строке 500000
FIRSTLAST> update t set y = -1 where id in (1,1000000);
FIRSTLAST> commit;
BLOCKER> rollback;
LONG> select * from t where y <> 0;
ID Y FILLER
---------- ---------- ----------
1 -1
500000 1
1000000 -1
Этого я и ожидал; поскольку транзакция FIRSTLAST была начата и зафиксирована после транзакции LONG, ее изменения не входят в "моментальный снимок конструкции where" транзакции LONG, и поэтому первая и последняя строки не были изменены транзакцией LONG [т.е. LONG видит старое значение (0) столбца y первой и последней строки, хотя новое значение (-1) и удовлетворяет условию в конструкции where транзакции LONG]. Пока все понятно.
Теперь мы повторим тот же эксперимент (вернув прежде значение 0 в столбце y во всех строках таблицы), но зафиксируем транзакцию BLOCKER вместо отката:
update t set y = 0 where y <> 0;
commit;
BLOCKER> update t set y = -1 where id = 500000;
LONG> update t set y = rownum where y = -1 or id=500000; -- она блокируется на строке 500000
FIRSTLAST> update t set y = -1 where id in (1,1000000);
FIRSTLAST> commit;
BLOCKER> commit; -- это единственное отличие
LONG> select * from t where y <> 0;
ID Y FILLER
---------- ---------- ----------
1 1
500000 2
1000000 3
Теперь, хотя транзакция FIRSTLAST начата и зафиксирована после начала LONG, так что ее изменения не входят в "моментальный снимок конструкции where" транзакции LONG, LONG, фактически, увидела изменения, выполненные FIRSTLAST (а не только изменения, выполненные транзакцией BLOCKER, что естественно).
Этот эксперимент дает одинаковые результаты в версиях 8.1.7 и 9.0.1.
Если оператор update транзакции LONG откатить и выполнить заново после определения того, что транзакция BLOCKER изменила строку, мы получим такой же результат.
Первый вопрос. Выполняет ли Oracle оператор заново на самом деле? Точнее, могу ли я разрабатывать ПО с учетом перезапуска оператора как способа обеспечения согласованности при одновременных изменениях, даже если на самом деле сервер Oracle делает нечто, аналогичное по результатам такому перезапуску?
Второй вопрос. В чем причина такого поведения? Интуиция мне подсказывает, что это обеспечивает учет всех изменений транзакции BLOCKER или ни одного, т.е. обеспечивает неделимость (atomicity) транзакции BLOCKER... но я бы хотел получить подтверждение или опровержение этой догадки.
[Примечание: Транзакция LONG присваивает столбцу y значение rownum, и если вспомнить, что значение rownum присваивается строкам по мере их просмотра оператором, кажется, что транзакция LONG изменила строки, начиная с id=1, затем - 500000, затем - 1000000, даже если она была заблокирована на id=500000, и уже обращалась к id=1 и отвергла эту строку (как следует из моего предположения о поведении сервера Oracle в данном случае, - я знаю, что в общем случае порядок просмотра нельзя ни предсказать, ни воспроизвести). Просто пища для размышлений - из этого ничего не следует.]
Представленный выше эксперимент построен на основе реальной производственной ситуации, оператор update которой был похож на использованный в транзакции LONG, и применялся к большой таблице, в которой одновременно выполнялось (множество) изменений, аналогичных выполняемым в транзакциях BLOCKER и FIRSTLAST. Я могу легко представить и другие ситуации, когда надо выполнить пакетное обновление большой таблицы, принимающей транзакции, с большим количеством изменений (или вставок и удалений) в секунду, причем, круглосуточных. Например, "повысить зарплату примерно в полночь всем сотрудникам, которые...". "Примерно" означает "как можно ближе к полуночи", - изменить всю таблицу в 00:05 можно, но уж никак не в 13:00...
Если предположить, что ответ на первый вопрос - "да", я не могу просто запустить пакетное обновление около получночи, поскольку набор строк для изменения не определяется однозначно сразу, а уточняется при каждом измении строки любой последующей транзакцией.
Если рассмотреть крайний случай, может потребоваться перезапускать оператор бесконечно... и никакого согласованного изменения по состоянию "около полуночи" уж никак не получится.
Вероятно, лучше всего это делать примерно так (реальная реализация может оказаться более сложной, использовать временные таблицы и т.п.):
create table snap (row_id rowid) as select rowid row_id from t where ...
А затем update t на базе snap, содержащей набор строк, которые необходимо изменить.
Третий вопрос. Может, я зря переживаю или просто ошибаюсь, рассматривая такой способ изменения?
Ответ Тома Кайта
Я даже боюсь публиковать этот ответ, поскольку подозреваю, что он вызовет шквал комментариев.
Результирующее множество согласовано - но оно может оказаться согласованным и на момент перезапуска. Это можно продемонстрировать на куда более простом примере - я использую всего 5 строк специальным образом, аналогично вашему примеру.
Мы используем строчный триггер и автономную транзакцию, чтобы увидеть, что происходит за кадром. Вот как строится пример:
ops$tkyte@ORA920> create sequence s;
Sequence created.
ops$tkyte@ORA920> create table msg1
2 ( seq int primary key, sid int,
3 old_id int, old_y int,
4 new_id int, new_y int );
Table created.
ops$tkyte@ORA920> create table msg2
2 ( seq int primary key, sid int,
3 old_id int, old_y int,
4 new_id int, new_y int );
Table created.
ops$tkyte@ORA920> create table t
2 as
3 select rownum id, 0 y
4 from all_users
5 where rownum <= 5;
Table created.
ops$tkyte@ORA920> select count(*)
2 from t;
COUNT(*)
----------
5
Итак, t - наша таблица из 5 строк, в которой мы будем выполнять одновременные изменения. msg1 - таблица, в которую мы будем записывать предварительные и окончательные образы строк в той же транзакции. В таблицу msg2 мы будем записывать те же образы, но с помощью автономной транзакции:
ops$tkyte@ORA920> create or replace procedure log_msg2(p_seq in int,
2 p_sid in int,
3 p_old_id in int,
4 p_old_y in int,
5 p_new_id in int,
6 p_new_y in int)
7 as
8 pragma autonomous_transaction;
9 begin
10 insert into msg2
11 (seq, sid, old_id, old_y, new_id, new_y)
12 values
13 (p_seq, p_sid, p_old_id, p_old_y, p_new_id, p_new_y);
14 commit;
15 end;
16 /
Procedure created.
ops$tkyte@ORA920> create or replace trigger t_trigger before update on t for each row
2 declare
3 l_seq number;
4 begin
5 select s.nextval into l_seq from dual;
6
7 insert into msg1
8 (seq, sid, old_id, old_y, new_id, new_y )
9 values
10 (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
11
12 log_msg2
13 (l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
14 end;
15 /
Trigger created.
Этот триггер будет регистрировать изменения в обеих таблицах - мы сможем увидеть запись в msg2 сразу, а в таблице msg1 окажутся только зафиксированные записи.
ops$tkyte@ORA920> update t set y = -1 where id = 3; 1 row updated.
Устанавливаем "средней" строке значение -1, что, естественно, ее заблокирует.
ops$tkyte@ORA920> set echo off
В другом сеансе выполним:
update t set y = 1 where y = -1 or id = 2 or id = 3;
Затем вернемся в первый сеанс и нажмем Enter
ops$tkyte@ORA920> pause
Я сделал это, и транзакция, естественно, оказалась заблокированной. Она изменила единственную строку, с id=2, и оказалась заблокированной на id=3.
ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
Можно увидеть, что строка с id=2 изменена - триггер сработал. Транзакция ждет на строке id=3...
ops$tkyte@ORA920> set echo off
В другом сеансе выполним:
update t set y = -1 where id in ( 1,5 ); commit; ops$tkyte@ORA920> pause
Это и будет ваше "изменение первой и последней строки". Я сделал это, и транзакция прошла без проблем:
ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
Теперь в нашей журнальной таблице 4 строки. seq=1 - это первое выполненное нами изменение, и оно блокирует сеанс session2. seq=2 - это одна строка, которую уже изменил заблокированный сеанс. seq=3/4 - записи для зафиксированного изменения "первой/последней" строки.
ops$tkyte@ORA920> commit; Commit complete.
Вот теперь все становится по-настоящему интересным:
ops$tkyte@ORA920> select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
NOT VISIBLE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
NOT VISIBLE 5 12566 3 0 3 1
NOT VISIBLE 6 12566 1 -1 1 1
NOT VISIBLE 7 12566 2 0 2 1
NOT VISIBLE 8 12566 3 -1 3 1
NOT VISIBLE 9 12566 5 -1 5 1
9 rows selected.
Странно - когда мы зафиксировали транзакцию, прежде всего, была изменена строка с id=3, - мы это видели - seq=5 соответствует этому изменению. ОДНАКО, она фактически была отменена (как и изменение строки с id=2 - см. далее), и изменение было начато с начала. При этом "повторно" были изменены строки с id=2 и id=3, а также строки с id 1 и 5 (они были выбраны по условию y=-1).
ops$tkyte@ORA920> set echo off
Фиксируем транзакцию в ранее заблокированном сеансе:
ops$tkyte@ORA920> pause
ops$tkyte@ORA920> select * from t;
ID Y
---------- ----------
1 1
2 1
3 1
4 0
5 1
Итак, ясно видно, что изменены были 4 строки и:
ops$tkyte@ORA920> select decode(msg1.seq,null,'UNDONE'), msg2.*
2 from msg2, msg1
3 where msg2.seq = msg1.seq(+)
4 order by msg2.seq;
DECODE SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
------ ---------- ---------- ---------- ---------- ---------- ----------
1 12565 3 0 3 -1
UNDONE 2 12566 2 0 2 1
3 12567 1 0 1 -1
4 12567 5 0 5 -1
UNDONE 5 12566 3 0 3 1
6 12566 1 -1 1 1
7 12566 2 0 2 1
8 12566 3 -1 3 1
9 12566 5 -1 5 1
9 rows selected.
Теперь, когда все "зафиксировано", мы видим, что изменения seq=2 и seq=5 были "отменены" - rolled back. Сделанные изменения были "отменены", чтобы можно было их "выполнить повторно" (Не хотелось бы, чтобы объем таких изменений вырос в 2, 3 или N раз, не так ли?)
Итак, сервер Oracle просто изменил время выполнения затроебованного вами изменения - если хотите, немного сместил его.
Оператор не должен бы "перезапускаться" бесконечно, поскольку остальные будут заблокированы и будут ждать своей очереди - они (заблокированные операторы) останутся заблокированными до завершения этого продолжительного оператора. Мини-откаты не будут их разблокировать.
Если бы мне нужно было выполнять такое большое пакетное изменение, я бы предпочел заблокировать таблицу, чтобы предотвратить влияние мини-транзакций. Мы имеем дело с конфликтом методов - и, в конечном итоге, большинство ваших пользователей окажутся заблокированными после нескольких перезапусков продолжительного оператора (т.е. общее время простоя для них будет больше).
Подумайте об этом - оператор должен перезапуститься, чтобы "сработать правильно", ему действительно необходимо видеть данные по состоянию на "сейчас", чтобы безопасно изменять их (а что, если другие изменения УДАЛИЛИ строки, которые должен был затронуть оператор?)
Я бы назвал вашу ситуацию опасной - весьма вероятны проблемы, связанные с потерей изменений. Блокировка таблицы для такого множественного изменения кажется вполне обоснованной или эффективной. Не хотите ее устанавливать - пусть сервер делает то, что делает.
Интересное обсуждение... Комментарий читателя от 3 августа 2003 года
Спасибо за замечательный ответ... Но что ты имел ввиду, когда писал:
"пусть сервер делает то, что делает"
Ответ Тома Кайта
В своем ответе я продемонстрировал, что "делает" сервер.
Я писал:
- Либо заблокируйте таблицу перед выполнением пакетного изменения, либо...
- Пусть сервер делает то, что делает.
Комментарий читателя от 4 августа 2003 года
Хорошо, поскольку "шквала комментариев" не последовало ;-), можно я проверю, правильно ли я понял ответ? Если вы проигнорируете этот длинный комментарий, я пойму, и подожду, пока до меня дойдет очередь.
a) Если транзакция "long" остановится на заблокированной строке, она подождет, проверит новые значения, а потом:
if (новые значения удовлетворяют конструкции where) {
выполнить мини-откат изменений и повторно выполнить изменение (или удаление) [случай A]
} else {
продолжить обработку [случай B]
}
b) В ходе мини-отката, блокировка TX транзакции "long" не снимается, так что ждущие ее снятия транзакции остаются заблокированными, что помогает предотвратить "бесконечный перезапуск" транзакции "long";
c) Причина мини-отката и перезапуска оператора [случай A] - в том, что транзакция "long" увидела изменение (подверглась влиянию) другой транзакции "tx-other", но транзакция "tx-other" могла также изменить строку, уже просмотренную транзакцией "long", поэтому если транзакцию "long" не перезапустить, она увидит только часть изменений транзакции "tx-other", нарушая ее неделимость;
d) Причина продолжения обработки в [случае B] - в том, что транзакция "long" (еще) не видела изменений, выполненных транзакцией "tx-other", так что не важно, изменила транзакция "tx-other" строку, уже просмотренную транзакцией "long", или нет: неделимость означает "видеть все, или ничего", а в данном случае речь идет как раз о "ничего";
e) Утверждения c+d можно выразить так: "транзакция long должна выполняться строго после tx-other";
f) Я все равно думаю, что транзакция "long" может перезапускаться бесконечно, поскольку, например, может случиться следующее:
- Транзакция "long" заблокирована на строке;
- Транзакция "brand-new-tx" изменяет не заблокированную строку;
- Транзакция "long" перезапускается, снова блокируется на этой строке и снова перезапускается.
Но [это я так думаю], в ходе мини-отката не снимаются блокировки уже измененных транзакцией "long" строк, так что вероятность срабатывания транзакции "brand-new-tx" со временем уменьшается, так что в реальности бесконечный перезапуск практически невозможен.
g) Если вы не против, просто интересно: сколько времени потребовалось на то, чтобы понять мой вопрос и ответить на него?
Ответ Тома Кайта
a) да...
b) да...
c) Эта причина И: что, если строка была изменена так, что она больше не удовлетворяет условию выбора? Да, предварительный образ удовлетворял, а текущее значение - нет. Пусть имеется правило "если a = 5, B должно быть в пределах от 10 до 15, иначе B должно быть равно 20". Вы выполняете оператор "update t set b = 11 where a = 5". По ходу работы эта транзакция блокируется. Блокировка связана с тем, что другой сеанс изменяет единственную строку - "update t set a = 6 where id = 1234". Ваше изменение не сработает, ХОТЯ вы ясно сказали "изменять, только если a=5" - потому что теперь a = 6.
Так что, транзакция перезапускается и пропускает эту строку.
d) Кажется, я вас понял - да.
e) Можно сказать и так
f) Я думаю, это возможно, но весьмамаловероятно.
g) Чтение вопроса потребовало больше времени, чем ответ на него. Было сложно разобраться, что вы имеете ввиду, но интересно. Возможно, я сделаю этот вопрос темой отдельной презентации. Меня удивило отсутствие комментариев - я думал получить много сообщений типа "Вот оно как..." ;)
Почему Rollback и Commit обрабатываются по-разному?
В ходе изучения этого обсуждения меня удивило, почему сервер Oracle работает по-разному, когда блокирующий сеанс выполняет откат вместо фиксации. Конечно, данные заблокированной строки при откате не меняются, но, мне кажется, было бы более логично (для единообразия), если бы откат изменений заблокированной строки тоже вызывал бы мини-откат транзакции LONG. В чем я неправ?
Ответ Тома Кайта
Весь вопрос в том, изменились ли данные. Если в исходном примере заменить:
update t set y = -1 where id = 3;
на
update t set y = y where id = 3;
Блокировки будут теми же. Сеанс 1 заблокирует строку с id=3. Сеанс 2 изменит строку с id=2, а затем окажется заблокированным на id=3. Сеанс 3 затем изменит строку с id=1 и id=5, и зафиксирует изменения. Потом сеанс 1 фиксирует транзакцию - сеанс 2 окажется разблокированным и обнаружит "ага, значение y не изменилось - не о чем беспокоиться, можно продолжать работу...", и изменит в результате только две строки.
Вот сценарий, который я использовал, безо всяких приглашений SQL*Plus, и он показывает, что даже при фиксации, если значение не изменилось, работа продолжится как в случае отката.
Более или менее согласованным результат не будет - он окажется "настолько" же согласованным, но производительность сущесвенно повысится...
drop table msg1;
drop table msg2;
drop sequence s;
drop table t;
set echo on;
create sequence s;
create table msg1
( seq int primary key, sid int,
old_id int, old_y int,
new_id int, new_y int );
create table msg2
( seq int primary key, sid int,
old_id int, old_y int,
new_id int, new_y int );
create table t
as
select rownum id, 0 y
from all_users
where rownum <= 5;
select count(*)
from t;
create or replace procedure log_msg2( p_seq in int,
p_sid in int,
p_old_id in int,
p_old_y in int,
p_new_id in int,
p_new_y in int )
as
pragma autonomous_transaction;
begin
insert into msg2
(seq, sid, old_id, old_y, new_id, new_y )
values
(p_seq, p_sid, p_old_id, p_old_y, p_new_id, p_new_y );
commit;
end;
/
create or replace trigger t_trigger before update on t for each row
declare
l_seq number;
begin
select s.nextval into l_seq from dual;
insert into msg1
(seq, sid, old_id, old_y, new_id, new_y )
values
(l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
log_msg2
(l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
end;
/
update t set y = y where id = 3;
set echo off
prompt В другом сеансе выполните:
prompt update t set y = 1 where y = -1 or id = 2 or id = 3;;
prompt Затем вернитесь в этот и нажмите enter
pause
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
set echo off
prompt В другом сеансе выполните:
prompt update t set y = -1 where id in ( 1,5 );;
prompt commit;;
pause
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
commit;
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
set echo off
prompt Выполните commit в ранее заблокированном сеансе
pause
select * from t;
select decode(msg1.seq,null,'UNDONE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
Потребуется около 6 месяцев...
Мне кажется, потребуется не менее шести месяцев, чтобы я это понял. Я всегда говорил, что одной из сильных сторон сервера Oracle является возможность понять, что происходит "за кадром", и это - отличный пример.
Ответ Тома Кайта
Вот еще интересные варианты:
a) Если заменить update t set y = -1 where id =3; на update t set y = y where id = 3;
то вместо 4 будут изменены только 2 строки...
b) Добавьте в таблицу t столбец Z с нулевыми значениями во всех строках. Выполните первое изменение:
update t set y = y, z = 1 where id = 3;
Теперь посмотрите, сколько строк будет изменено (попробуйте догадаться)
c) Оставив столбец Z с нулями, добавьте следующий триггер:
create or replace trigger t after update on t for each row
begin
dbms_output.put_line(:new.z);
end;
/
и используйте следующий оператор update:
update t set y = y, z = 1 where id = 3;
Теперь посмотрите, сколько строк было изменено...
Интересная разновидность интересного варианта
a) Если заменить update t set y = -1 where id =3; на update t set y = y where id = 3;
то вместо 4 будут изменены только 2 строки...
Да! Потому что оператор "update t set y = 1 where y = -1 or id = 2 or id = 3" (который я пометил "long") не был перезапущен, поэтому он и не увидел изменения на -1, выполненного транзакцией "first/last". Т.е. он был согласован как образ до "first/last".
Вот вариант вашего сценария, в котором для регистрации используется client_info, и его окончательные результаты:
drop table msg1;
drop table msg2;
drop sequence s;
drop table t;
set echo on
set lines 123
col client_info format a15
set pages 9999
create sequence s;
create table msg1
( seq int primary key, sid int,
old_id int, old_y int,
new_id int, new_y int );
create table msg2
( seq int primary key, sid int,
old_id int, old_y int,
new_id int, new_y int,
client_info varchar2(64) );
create table t
as
select rownum id, 0 y
from all_users
where rownum <= 5;
select count(*)
from t;
create or replace procedure log_msg2( p_seq in int,
p_sid in int,
p_old_id in int,
p_old_y in int,
p_new_id in int,
p_new_y in int )
as
pragma autonomous_transaction;
client_info varchar2(64);
begin
dbms_application_info.read_client_info (client_info);
insert into msg2
(seq, sid, old_id, old_y, new_id, new_y, client_info )
values
(p_seq, p_sid, p_old_id, p_old_y, p_new_id, p_new_y, client_info);
commit;
end;
/
show errors;
create or replace trigger t_trigger before update on t for each row
declare
l_seq number;
begin
select s.nextval into l_seq from dual;
insert into msg1
(seq, sid, old_id, old_y, new_id, new_y )
values
(l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
log_msg2
(l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y);
end;
/
exec dbms_application_info.set_client_info ('blocker');
update t set y = y where id = 3;
-- update t set y = -1 where id = 3;
set echo off
prompt В другом сеансе выполните:
prompt exec dbms_application_info.set_client_info ('long');;
prompt update t set y = 1 where y = -1 or id = 2 or id = 3;;
prompt Затем вернитесь в этот и нажмите enter
pause
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
set echo off
prompt В другом сеансе выполните:
prompt exec dbms_application_info.set_client_info ('first/last');;
prompt update t set y = -1 where id in ( 1,5 );;
prompt commit;;
prompt Затем вернитесь в этот и нажмите enter
pause
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
commit;
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
set echo off
prompt Зафиксируйте ранее заблокированный сеанс (long)
prompt затем вернитесь в этот и нажмите enter
pause
select * from t;
select decode(msg1.seq,null,'UNDONE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
Окончательный результат:
ID Y
---------- ----------
1 -1
2 1
3 1
4 0
5 -1
DECODE(MSG1.SEQ,NU SEQ SID OLD_ID OLD_Y NEW_ID
NEW_Y CLIENT_INFO
------------------ ---------- ---------- ---------- ---------- ----------
---------- ---------------
1 3967 3 0 3
0 blocker
2 3968 2 0 2
1 long
3 3969 1 0 1
-1 first/last
4 3969 5 0 5
-1 first/last
5 3968 3 0 3
1 long
А теперь другая интересная разновидность интересного варианта: что если строка с id=5 удовлетворяет конструкции where с самого начала, а затем она изменяется транзакцией "first/last" так, что перестает удовлетворять условию (я выбрал значение 99)?
Давайте начнем с такого состояния таблицы:
ID Y
---------- ----------
1 0
2 0
3 0
4 0
5 -1
Вот что мы будем делать:
drop table msg1;
drop table msg2;
drop sequence s;
drop table t;
set echo on
set lines 123
col client_info format a15
set pages 9999
create sequence s;
create table msg1
( seq int primary key, sid int,
old_id int, old_y int,
new_id int, new_y int );
create table msg2
( seq int primary key, sid int,
old_id int, old_y int,
new_id int, new_y int,
client_info varchar2(64) );
create table t
as
select rownum id, 0 y
from all_users
where rownum <= 5;
update t set y = -1 where id = 5;
select * from t;
create or replace procedure log_msg2( p_seq in int,
p_sid in int,
p_old_id in int,
p_old_y in int,
p_new_id in int,
p_new_y in int )
as
pragma autonomous_transaction;
client_info varchar2(64);
begin
dbms_application_info.read_client_info (client_info);
insert into msg2
(seq, sid, old_id, old_y, new_id, new_y, client_info )
values
(p_seq, p_sid, p_old_id, p_old_y, p_new_id, p_new_y, client_info);
commit;
end;
/
create or replace trigger t_trigger before update on t for each row
declare
l_seq number;
begin
select s.nextval into l_seq from dual;
insert into msg1
(seq, sid, old_id, old_y, new_id, new_y )
values
(l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y );
log_msg2
(l_seq, userenv('sessionid'), :old.id, :old.y, :new.id, :new.y);
end;
/
exec dbms_application_info.set_client_info ('blocker');
-- update t set y = -1 where id = 3;
update t set y = y where id = 3;
set echo off
prompt В другом сеансе выполните:
prompt exec dbms_application_info.set_client_info ('long');;
prompt update t set y = 1 where y = -1 or id = 2 or id = 3;;
prompt Затем вернитесь в этот и нажмите enter
pause
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
set echo off
prompt В другом сеансе выполните:
prompt exec dbms_application_info.set_client_info ('first/last');;
prompt update t set y = 99 where id in ( 1,5 );;
prompt commit;;
prompt Затем вернитесь в этот и нажмите enter
pause
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
commit;
select decode(msg1.seq,null,'NOT VISIBLE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
set echo off
prompt Зафиксируйте транзакцию в ранее заблокированном сеансе (long)
prompt Затем вернитесь в этот и нажмите enter
pause
select * from t;
select decode(msg1.seq,null,'UNDONE'), msg2.*
from msg2, msg1
where msg2.seq = msg1.seq(+)
order by msg2.seq;
Окончательный результат:
ID Y
---------- ----------
1 99
2 1
3 1
4 0
5 99
DECODE(MSG1.SEQ,NU SEQ SID OLD_ID OLD_Y NEW_ID
NEW_Y CLIENT_INFO
------------------ ---------- ---------- ---------- ---------- ----------
---------- ---------------
1 3971 3 0 3
0 blocker
UNDONE 2 3968 2 0 2
1 long
3 3969 1 0 1
99 first/last
4 3969 5 -1 5
99 first/last
UNDONE 5 3968 3 0 3
1 long
UNDONE 6 3968 5 -1 5
1 long
7 3968 2 0 2
1 long
8 3968 3 0 3
1 long
Оператор seq=6 выполнил изменение (я думаю, old_y=-1 осталось от моментального снимка, который берется в начале выполнения оператора), но затем транзакция "long" увидела, что строка была изменена, поэтому оператор был перезапущен.
Вы согласны?
Сколько грязных буферов? Комментарий читателя от 19 августа 2003 года
Если при каждом изменении берется согласованный образ блока, то для каждого блока должно быть несколько грязных буферов. Как эти изменения объединяются - в конечном итоге, на диск ведь пишется один блок? (Если записывать на диск все грязные образы, на диске не окажутся все изменения.)
Ответ Тома Кайта
Есть только один грязный буфер, последний. Остальные - просто согласованные по чтению версии того же блока на разные моменты времени.
Единственно верной является последняя версия блока.