Рассылка закрыта
При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.
Вы можете найти рассылки сходной тематики в Каталоге рассылок.
Открыто о СУБД Oracle на русском : Согласованность данных при изменении - продолжение темы
| Информационный Канал Subscribe.Ru |
Выпуск 71
Уважаемые подписчики рассылки!
Этот выпуск продолжает тему согласованности данных при изменениях в СУБД Oracle, которая уже
рассматривалась почти год назад
в рассылке. Соответствующее обсуждение на сайте Тома Кайта,
начатое в августе 2003 года, многими считается одним из лучших...
Как обычно, я не переводил проявления восторга и вопросы постителей сайта, не разобравшихся в сути обсуждаемой проблемы.
Непосредственным поводом вернуться к этой теме для меня стало недавно "оформившееся" отдельное обсуждение на форуме по Oracle сайта SQL.ru.
Согласованность данных при изменении. Часть II
Тем, кто не в курсе, рекомендую сначала почитать Часть I...
Комментарий читателя от 20 августа 2003 года
Прав ли я, предполагая, что:
при изменении Oracle создает согласованное по чтению представление только для чтения и изменяет
исходный блок, а не создает согласованное по чтению представление созданного блока
Ответ Тома Кайта
Нам надо изменять последнюю версию, да.
Рассмотрим пример:
update emp set sal = sal + 1;
Если два пользователя примерно одновременно будут выполнять этот оператор и мы не будем изменять ПОСЛЕДНЮЮ версию блока, мы потеряем изменение.
Правильное утверждение...
Правильно ли следующее утверждение:
Оператор update/delete будет видеть значения на момент, когда оператор
мог быть выполнен без блокирования предыдущими операторами update/delete
из других сеансов, а не на момент начала выполнения оператора.
Ответ Тома Кайта
Нет. Хотя и похоже не правду.
Пусть оператор update начинается в момент времени T1. По ходу работы он встречает заблокированную строку и блокируется. Теперь время - T1+x. Он разблокируется и видит измененную строку.
Возможны два случая:
- Строка фактически не изменилась с момента времени T1 (можно сравнить текущий образ
и согласованный по чтению образ). Тогда мы продолжаем, как обычно.
Под "строка фактически не изменилась" я подразумеваю, что набор интересующих нас столбцов не изменился: тех, что используются в условии, а не изменяются в конструкции set - Строка ОТЛИЧАЕТСЯ. Оператор откатывается и перезапускается. Время опять T1. Мы перенесли стойки ворот!
Так что, оператор update всегда видит строки на время начала выполнения оператора, - мы просто изменили понятие "время начала выполнения оператора".
Другие результаты
После выполнения твоего сценария я получил другие результаты. Похоже, Oracle в моем случае не "смещает время начала выполнения". Мы используем Oracle 9.2.0. Не мог бы ты объяснить, что происходит?
Результат был таким:
rob@DEV> update t set y = y where id = 3; 1 rij is bijgewerkt. rob@DEV> set echo offв другом сеансе выполняем:
update t set y = 1 where y = -1 or id = 2 or id = 3;
затем возвращаемся и нажимаем клавишу enter
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 25494 3 0 3 0
NOT VISIBLE 2 25508 2 0 2 1
в другом сеансе выполняем:
update t set y = -1 where id in ( 1,5 );
commit;
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 25494 3 0 3 0
NOT VISIBLE 2 25508 2 0 2 1
3 25510 1 0 1 -1
4 25510 5 0 5 -1
Commit is voltooid.
DECODE(MSG1 SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
----------- ---------- ---------- ---------- ---------- ---------- ----------
1 25494 3 0 3 0
NOT VISIBLE 2 25508 2 0 2 1
3 25510 1 0 1 -1
4 25510 5 0 5 -1
NOT VISIBLE 5 25508 3 0 3 1
фиксация в ранее заблокированном сеансе
ID Y
---------- ----------
1 -1
2 1
3 1
4 0
5 -1
DECODE SEQ SID OLD_ID OLD_Y NEW_ID NEW_Y
------ ---------- ---------- ---------- ---------- ---------- ----------
1 25494 3 0 3 0
2 25508 2 0 2 1
3 25510 1 0 1 -1
4 25510 5 0 5 -1
5 25508 3 0 3 1
Ответ Тома Кайта
update t set y = y where id = 3;
вы выполнили тест для y=y, а не для y=-1. Вот почему так произошло.
Измененное утверждение
Сложнее, чем я думал:
"Оператор update/delete будет видеть значения на момент, когда оператор
мог быть выполнен без блокирования предыдущими операторами update/delete
из других сеансов, которые меняют значение, используемое в запросе,
а не на момент начала выполнения оператора."
create table test
( field1 number(10,0),
field2 number(10,0),
field3 number(10,0)
)
/
insert into test
values(5,5,1);
insert into test
values(4,5,1);
commit;
FIELD1 FIELD2 FIELD3
---------- ---------- ----------
5 5 1
4 5 1
(перед каждым тестом таблица будет возвращаться в это состояние)
ТЕСТ 1
------
сеанс 1:
update test
set field1 = field1 * 1
where field1 = 5;
сеанс 2:
-- этот сеанс будет блокирован
update test
set field2 = field2 * 2
where field1 = 5;
сеанс 3:
update test
set field1 = 5
where field1 = 4;
commit;
сеанс 1: commit; -- этот оператор разблокирует сеанс 2
сеанс 2: commit;
результат:
FIELD1 FIELD2 FIELD3
---------- ---------- ----------
5 10 1
5 5 1
ТЕСТ 2:
-------
сеанс 1:
update test
set field2 = field2 * 1
where field1 = 5;
сеанс 2:
-- этот сеанс будет блокирован
update test
set field2 = field2 * 2
where field1 = 5;
сеанс 3:
update test
set field1 = 5
where field1 = 4;
commit;
сеанс 1: commit; -- этот оператор разблокирует сеанс 2
сеанс 2: commit;
результат:
FIELD1 FIELD2 FIELD3
---------- ---------- ----------
5 10 1
5 5 1
ТЕСТ 3
------
сеанс 1:
update test
set field3 = field3 * 2
where field1 = 5;
сеанс 2:
-- этот сеанс будет блокирован
update test
set field2 = field2 * 2
where field1 = 5;
сеанс 3:
update test
set field1 = 5
where field1 = 4;
commit;
сеанс 1: commit; -- этот оператор разблокирует сеанс 2
сеанс 2: commit;
результат:
FIELD1 FIELD2 FIELD3
---------- ---------- ----------
5 10 2
5 5 1
ТЕСТ 4
------
сеанс 1:
update test
set field2 = field2 * 2
where field1 = 5;
сеанс 2:
-- этот сеанс будет блокирован
update test
set field2 = field2 * 2
where field1 = 5;
сеанс 3:
update test
set field1 = 5
where field1 = 4;
commit;
сеанс 1: commit; -- этот оператор разблокирует сеанс 2
сеанс 2: commit;
результат:
FIELD1 FIELD2 FIELD3
---------- ---------- ----------
5 20 1
5 5 1
Но, если я правильно понял ответ Тома, я должен получить:
FIELD1 FIELD2 FIELD3
---------- ---------- ----------
5 20 1
5 10 1
?
freek@dev01> select banner from v$version; BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production CORE 9.2.0.1.0 Production TNS for 32-bit Windows: Version 9.2.0.1.0 - Production NLSRTL Version 9.2.0.1.0 - Production
Ответ! :)
Тест 4 не делает то, что вы предполагали, поскольку сеанс 1 не изменил поле, входящее в условие оператора в сеансе 2.
Поменяйте
сеанс 2: -- этот сеанс будет блокирован update test set field2 = field2 * 2 where field1 = 5;
на
session 2: -- этот сеанс будет блокирован update test set field2 = field2 * 2 where field1 = 5 and field2 > 0;
и вы получите ожидаемый результат.
Спасибо
Спасибо за то, что указали на ошибку в моих рассуждениях.
"Перезапуск" зависит не от изменения значения, которое мы хотим изменить, а от изменения значения, использованного в условии поиска.
Я теперь перечитал ответ Тома на мой первый вопрос, и все стало ясно.
Комментарий читателя отљ20 июня 2004 года
Обсуждение в дискуссионной группе см. Google тоже было интересным. Но оно оставило без ответа вопрос:
> 1. Как я уже упоминал ранее, зачем вообще заниматься перезапуском, если требования уровня изоляции > READ COMMITTED выполнены, хоть с перезапуском, хоть без. В чем причина этих перезапусков? > > > Потому что в противном случае мы будем обрабатывать строки, более не удовлетворяющие условиям.
И что? Требования уровня изоляции READ COMMITTED не нарушаются - повторяемости чтения никто не обещал. Зачем заниматься этой аномалией, игнорируя другие, например, игнорируя новые строки, удовлетворяющие условиям? Они тоже не будут обработаны.
У вас есть ответ на это? Другими словами, в каких конкретно случаях update будет перезапускаться? Упомянутое обсуждение касалось изменения столбцов, упоминаемых в конструкции WHERE оператора UPDATE, но вновь вставленные строки ВСЕГДА игнорируются при изменении, независимо от условий.
В любом случае, не могли бы вы прокомментировать, как другие производители СУБД решают эту проблему?
Ответ Тома Кайта
> Потому что в противном случае мы будем обрабатывать строки, более не удовлетворяющие условиям.
Это, по моему, ясно как день.
Новые строки, которые были вставлены, не всегда игнорируются оператором update. Если update по условию "x=5" начался в полдень, а в 12:01 вы вставили новую строку с x=5 и зафиксировали это изменение в 12:02, а в 12:03 оператор update пришлось перезапустить - этот update, фактически, увидит вставленную строку. В основе всего лежит согласованность чтения. Все, что вы видите, согласовано на определенный момент времени - и вы не обрабатываете строки, не удовлетворяющие заданным условиях. Представьте свое удивление, если есть требование "y может быть null ТОЛЬКО и ЕСЛИ ТОЛЬКО x=5", и вы делаете "update t set y = null where x=5", а другой сеанс потом поменял x=5 на x=6 - если не перезапустить оператор, что будет (будет обработка строк, более не удовлетворяющих условиям). Когда мы видим, что x БЫЛ 5, а ТЕПЕРЬ x равен 6, мы перезапускаем оператор update, и он увидит все зафиксированные на момент перезапуска строки.
Другие производители используют блокировки так, что чтение блокирует запись, а записьблокирует чтение, и за счет этого запросы блокируют изменения и наоборот. Их изменения являются/могут быть согласованными на момент ЗАВЕРШЕНИЯ запроса, а нкаши - на момент его начала. В большинстве случаев, однако, другие СУБД используются в режиме read committed, а это означает, что запросы возвращают вообще не согласованные результаты. Если вас интересуют детали, я писал об этом подробно в книге "Expert One on One Oracle" ("Oracle для профессионалов" в моем переводе, если кто не знает :) - прим. В.К.) и сравнивал модели согласованного чтения и блокирования при чтении.
Комментарий читателя от 21 июня 2004 года
"Новые строки, которые были вставлены, не всегда игнорируются оператором update. Если update по условию "x=5" начался в полдень, а в 12:01 вы вставили новую строку с x=5 и зафиксировали это изменение в 12:02, а в 12:03 оператор update пришлось перезапустить - этот update, фактически, увидит вставленную строку."
Да, если update перезапускается в 12:03, он учтет новую строку, но меня интересовало, что вызовет его перезапуск в 12:03? Откуда сервер "знает", что новая вставленная строка удовлетворяет условию WHERE для выполняемого изменения?
Ответ Тома Кайта
Вставка строки не вызывает перезапуска. Изменение каким-то другим сеансом строки, в которой было "x=5" в полдень, на "x=<нечто другое>" вызовет перезапуск.
Комментарий читателя от 22 июня 2004 года
"Вставка строки не вызывает перезапуска. Изменение каким-то другим сеансом строки, в которой было "x=5" в полдень, на "x=<нечто другое>" вызовет перезапуск."
Тем не менее, мой вопрос остается.
Пусть update себе работает, блокируя каждую строку, соответствующую его конструкции WHERE и изменяя ее. Пусть он уже "прошел" определенную строку, т.е. уже изменил ее, и тут другой сеанс появляется и меняет "x=5" на "x=6", - как сеанс, выполняющий UPDATE, узнает, что это произошло?
Другими словами, пусть на момент начала выполнения UPDATE конструкции WHERE удовлетворяло 10 строк, оператор начал со строки 1 и продолжает работать. После изменения строки 9 другой сеанс изменяет в строке 1 x=5 на x=6. Что заставляет сервер Oracle снова вернуться к строке 1?
Ответ Тома Кайта
Строка заблокирована, и другой сеанс не может изменить x=5 на x=6
Строка заблокирована...
- "Вставка строки не вызывает перезапуска. Изменение каким-то другим сеансом строки, в которой было "x=5" в полдень, на "x=<нечто другое>" вызовет перезапуск."
- "Строка заблокирована, и другой сеанс не может изменить x=5 на x=6"
Хм, а не противоречат ли эти 2 утверждения друг другу? Если строка заблокирована, как другой сеанс, который вы упоминаете в (1), сможет ее изменить?
Ответ Тома Кайта
В полдень вы выполняете "update t set y = 0 where x = 5"
Вы начинаете выполнять согласованные чтения таблицы t в поисках строк с "x=5"
Каждая найденная с помощью согласованного чтения (по состоянию на ПОЛДЕНЬ) строка, должна быть получена в текущем состоянии (current mode, как она есть "сейчас", скажем, в 12:01).
Если текущее состояние строки отличается от полученного при согласованном чтении, мы должны решить, можем мы изменять эту строку или нет. Если в 12:00 для данной строки x было 5, а в 12:01, когда мы до нее добрались, x равно 6, - мы не можем изменять эту строку. Мы перезапускаем update.
Речь шла об измененной другим сеансом строке, до которой мы ЕЩЕ НЕ ДОБРАЛИСЬ. Все строки, которые мы изменили, заблокированы, - перезапуски вызывают еще не обработанные нами строки.
Прекрасное объяснение!
Это, несомненно, мое самое любимое обсуждение на сайте asktom (bиз того немногого, что я уже успел прочитать). Я ближайших несколько лет буду с удовольствием изучать этот сайт!
Том, хотел узнать... Судя по товему ответу, ты знал об этом "феномене" мини-отката до того, как получил исходный вопрос. Когда и где ты узнал об этом важном процессе впервые? В руководстве Concepts об этом явно не написано!
Ответ Тома Кайта
Я знал, что нечто пободное должно происходить (а как иначе, нельзя же изменять строку с x=6, если было сказано менять с x=5).
До августа 2003 года я не исследовал эту проблему настолько серьезно.
ТОгда же я хлопнул себя по лбу и сказал "Ага, это объясняет, что произошло в июне 2000 года". Почему? Я использовал внешние процедуры в версии 8i в моей собственной реализации пакета "utl_tcp" для посылки сообщений электронной почты по протоколу smtp. Я посылал сообщения из триггера. Пользователи иногда говорили мне: "эй, ты мне это сообщение послал дважды". Я отвечал: "нет, это невозможно". Я заметил, что это всегда были одни и те же пользователи (упомянутые "в начале" таблицы), - проблема должна быть с их стороны. Полтом мы начали использовать пакет dbms_job для посылки сообщений в фоновом режиме после фиксации (в результате изменения выполнялись намного быстрее, меньше блокирования). Проблема исчезла, а я решил, что они починили свою систему электронной почты.
Позже в августе я сказал себе: "Ага, это объясняет и происходившее летом 1999 года". Мы работали над самой ранней реализацией портала. У нас был триггер для создания последовательностей с помощью автономных транзакций. Эти последовательности не могли существовать, но постоянно кто-то жаловался "получил ошибку, последовательность существует". Это опять было вызвано перезапуском - триггер срабатывал, происходил откат (но не DDL-оператора, он фиксировался), триггер срабатывал и возникала ошибка. Это казалось "невозможным", но было...
Так что я смутно подозревал, что нечто подобное происходит, просто никогда еще не выяснял подробно. Заданный вопрос дал повод углубиться в исследование проблемы.
Спасибо!
Спасибо, что раскрыли нам происходящее "за кадром".
> Заданный вопрос дал повод углубиться в исследование проблемы.
И сколько времени потребовалось, чтобы все это выяснить с нуля, когда вы этим занялись?
(Понять суть, постоить тест и крикнуть "Эврика"?). Заняло ли это час, или больше?
Просто хочу оценить ваши впечатляющие аналитические способности... :).
Ответ Тома Кайта
Насколько я помню, времени понадобилось не так уж много - в присланном примере было более чем достаточно информации для создания теста. Так что, создание начального примера заняло пару минут. Анализ результатов - пару часов; понадобились еще эксперименты (другие случаи) и ряд консультаций, просто чтобы убедиться, что я говорю "правильные вещи".
Дальнейший анализ
Дополнительный сценарий: Почему при перезапуске изменяется только одна из двух вновь вставленных (двумя отдельными сеансами) строк, удовлетворяющих условиям оператора, а не обе?
Пример:
T1 (Сеанс 1) - UPDATE x SET col2 = -1 WHERE col2 = 1;
Этот update затрагивает 5000 строк.
T2 (Сеанс 2) - UPDATE x SET col2 = 0 WHERE col1 = 'A' AND col2 = 1;
-- 1 row updated.
(Сеанс 2) - INSERT INTO x (col1, col2) VALUES ('B',1);
(Сеанс 2) - COMMIT;
T3 (Сеанс 3) - UPDATE x SET col2 = 1 WHERE col1 = 'A' AND col2 = 0; /* Восстанавливаем в
строке исходное значение */
(Сеанс 3) - INSERT INTO x (col1, col2) VALUES ('C',1);
(Сеанс 3) - COMMIT;
T4 (Сеанс 1) - /* Измненение,наконец, завершается */ COMMIT;
SELECT * FROM x WHERE col1 = 'A';
col1 col2
---- ----
A -1
SELECT * FROM x WHERE col1 IN ('B','C');
col1 col2
---- ----
B -1
C 1
Если после перезапуска надо начать с текущего момента после выявленния измененной строки, то почему при втором проходе update выявляются не все втсавленные строки? Я ожидал, что в обеих строках будет значение -1, поскольку перезапуск должен был быть вызван после сеанса 3 (мы знаем это потому, что строка, которой было возвращено значение 1, теперь имеет значение -1).
Спасибо за любой комментарий. Прошу прощения, что не предоставляю тестовый сценарий; эта проблема была выявлена на таблице из 500000, где легко было изменить строку внизу до того, как первый сеанс ее изменит (и избежать блокирования).
Ответ Тома Кайта
Сеанс 1 начался при SCN "1"
Я предполагаю, что сеанс 2 заблокировал сеанс 1. Когда в сеансе 2 произошла фиксация, сеанс 1 был перезапущен при SCN "2". Сеанс 1 перешел в режим select for update "на момент scn=2"
Сеанс 3, видимо, не блокировал сеанс 1, поскольку согласованное чтение дало "col2=0" для этой строки - на момент SCN=2, col2=0, и эта строка не должна обрабатываться сеансом 1.
Транзакции T3 и T4 могли выполняться в другом порядке - ничего, кроме везения, не требует, чтобы T4 выпаолнялась после T3, поскольку сеанс 3 не изменял никаких данных, которые интересовали сеанс 1.
Дальнейший анализ - продолжение
Дополнительное уточнение сценария:
"Я предполагаю, что сеанс 2 заблокировал сеанс 1."
Блокирование не происходит, поскольку сеанс 2 может изменить строку и зафиксировать изменение до того, как сеанс 1 доберется до нее в таблице.
"Сеанс 3, видимо, не блокировал сеанс 1, поскольку согласованное чтение дало "col2=0" для этой строки - на момент SCN=2, col2=0, и эта строка не должна обрабатываться сеансом 1."
Правильно. Поскольку сеанс 3 читает строку по состоянию на момент SCN=2, блокирования нет, но сеанс 3 устанваливает значение col2 (0) обратно в 1, что соответствует условию оператора update в сеансе 1.
"Транзакции T3 и T4 могли выполняться в другом порядке - ничего, кроме везения, не требует, чтобы T4 выпаолнялась после T3, поскольку сеанс 3 не изменял никаких данных, которые интересовали сеанс 1."
Везение тут не при чем, - тест контролировался (оценка времени выполнения в производственной среде может быть очень сложна, но возможна).
Но, транзакция T3 действительно произошла до завершения T1, и T3 зафиксирована.
Причина уверенности в том, что транзакция T3 зафиксирована до T1 состоит в том, что T1 затронула измененную транзакцией T3 строку (поминте установку значения col2 обратно в 1). Вот что мы видим после фиксации транзакции в сеансе 1:
SELECT * FROM x WHERE col1 = 'A'; col1 col2 ---- ---- A -1
Итак, по какой-то причине сеанс 1 после перезапуска выявляет вставку в сеансе 2 (устанавливает col2 значение -1 для строк, где col1 = 'B'), и выявляет изменение сеанса 3, где col1 = 'A', но не изменяет вставленную сеансом 3 строку, где col1 = 'C'.
Из соображений согласованности можно ожидать, что вставленная сеансом 3 строка тоже будет изменена после перезапуска (col2 = -1 где col1 = 'C').
Есть о чем подумать.
Ответ Тома Кайта
Предположение о блокировании не обязательно - сеанс мог быть заблокирован, а мог и не быть.
Сеанс 1 будет выполнять согласованное чтение соответствующих данных, убеждаться, что "предполагалось изменение этой строки", а затем будет выполнять чтение текущего состояния (если он был заблокирован - после разблокирования) и увидит, что "теперь эту строку менять не надо". Это вызывает перезапуск.
На самом деле, я передумал - я не вижу никакого сценария, при котором A, в конечном итоге, будет равно -1. Нам нужно увидеть тестовый пример (я потратил некторое время на попытки это сымитировать, и не смог получить A, равное -1, как вы пишете).
Оригинал обсуждения этого вопроса можно найти здесь.
Copyright © 2003 Oracle Corporation
Продолжение, похоже, следует...
Прежние выпуски
Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)
В следующих выпусках
Вообще-то, я собирался заняться статьей Джонатана Льюиса, посвященной пересозданию индексов... Да и человека три таки выразили желание почитать в рассылке фрагменты новой книги "Mastering Oracle PL/SQL: Practical Solutions" в моем переводе. Думаю, что-то из этого появится еще до августа.
С наилучшими пожеланиями,
В.К.
| http://subscribe.ru/
E-mail: ask@subscribe.ru |
Адрес подписки |
Отписаться |
| В избранное | ||
