2 (fk int constraint c fk

3 references p(pk)

4 deferrable

5 initially immediate

7 / Table created.

tkyte@TKYTE816> insert into p values (1) ; 1 row created.

tkyte@TKYTE816> insert into с values (1) ; 1 row created.

Итак, имеется родительская таблица Р и подчиненная таблица С. Таблица С ссылается на таблицу Р, а реализующее это правило требование названо C FK (child foreign key - внешний ключ подчиненной таблицы). Это требование создано как допускающее отложенную проверку - DEFERRABLE, но при создании, кроме того, указана конструкция INITIALLY IMMEDIATE. To есть проверку требования можно отложить до фиксации транзакции или любого другого момента. По умолчанию, однако, проверка будет выполняться на уровне оператора. Именно так чаще всего и используются требования с отложенной проверкой. Существующие приложения не будут проверять нарушение требования перед выполнением оператора COMMIT, поэтому лучше не делать им таких сюрпризов. В соответствии с определением, таблица С будет вести себя обычным образом, но позволит явно это поведение изменить. Теперь давайте попытаемся применить ряд операторов ЯМД к таблицам и посмотрим, что получится:

tkyte@TKYTE816> update p set pk = 2; update p set pk = 2

ERROR at line 1:

ORA-02292: integrity constraint (TKYTC FK) violated - child record found

Поскольку требование сейчас работает в режиме IMMEDIATE, этот изменение не будет выполнено. Изменив режим требования, попробуем снова:

tkyte@TKYTE816> set constraint c fk deferred; Constraint set.

tkyte@TKYTE816> update p set pk = 2; 1 row updated.

Теперь все получилось. В демонстрационных целях я собираюсь показать, как, процедурно проверив требование в режиме DEFERRED, убедиться, что сделанные изменения согласуются с установленными бизнес-правилами (другими словами, не нарушено ли требование ). Имеет смысл делать это перед фиксацией или передачей управления в другую часть программы (где ограничений в режиме DEFERRED может и не быть):

tkyte@TKYTE816> set constraint c fk immediate; set constraint с fk immediate

ERROR at line 1:

ORA-02291: integrity constraint (TKYTE.C FK) violated - parent key not found

Этот оператор не выполняется и сразу же возвращает сообщение об ошибке, чего и следовало ожидать: мы ведь знаем, что требование нарушено. Изменение таблицы Р при этом не отменяется (это нарушило бы неделимость на уровне оператора). Учтите также, что транзакция по-прежнему работает с требованием C FK в режиме DEFERRED, поскольку оператор SET CONSTRAINT не сработал. Продолжим, распространяя изменение на таблицу С:

tkyte@TKYTE816> update с set fk = 2; 1 row updated.

tkyte@TKYTE816> set constraint c fk immediate; Constraint set.

tkyte@TKYTE816> commit; Commit complete.

Именно так это и происходит.

Плохие привычки при работе с транзакциями

У многих разработчиков выработались плохие привычки в отношении транзакций. Особенно часто это наблюдается у разработчиков, которые имели дело с СУБД, поддерживающими транзакции, но не навязывающими их использование. Например, в СУБД Informix (по умолчанию), Sybase и SQL Server необходимо явно начинать транзакцию. В противном случае каждый оператор будет выполняться как отдельная транзакция. Как сервер Oracle неявно устанавливает вокруг отдельных операторов SAVEPOINT, в этих СУБД вокруг каждого оператора неявно устанавливаются операторы BEGIN WORK/COMMIT или ROLLBACK. Это объясняется тем, что в упомянутых СУБД блокировки - ценный ресурс, а сеансы, читающие данные, блокируют сеансы, изменяющие данные. Пытаясь повысить параллелизм, создатели этих СУБД заставляют разработчиков делать транзакции как можно короче (иногда в ущерб целостности данных).

В СУБД Oracle принят другой подход. Транзакции всегда начинаются неявно, и возможность автоматической фиксации отсутствует, если только она не реализована в приложении (см. описание функционального интерфейса JDBC в конце этого раздела). В Oracle транзакция фиксируется, когда это необходимо, и не раньше. Транзакции должны быть такого размера, как нужно для приложения. Проблемы нехватки блокировок, невозможности доступа к данным для других сеансов и т.п. учитывать не надо - размер транзакции определяется исключительно требованиями обеспечения целостности данных. Блокировки не являются особо ценным ресурсом, а одновременное чтение и запись данных не приводит к конфликтам. Это обеспечивает надежность транзакций в базе данных. Продолжительность транзакций не ограничивается, - она определяется требованиями приложения. Транзакции не должны быть ориентированы на удобство работы

компьютера и его программного обеспечения - они предназначены для защиты целостности данных пользователей.

Столкнувшись с задачей изменения большого количества строк, программисты обычно пытаются изобрести процедурный способ сделать это в цикле - так, чтобы можно бхло фиксировать изменения группы строк определенного размера. Я слышал два обоснования подобного решения:

быстрее и эффективнее фиксировать изменения часто с помощью множества небольших транзакций, чем обработать все строки и зафиксировать одну большую транзакцию;

недостаточно места в сегментах отката.

Оба эти утверждения неверны. Частые фиксации изменений не способствуют ускорению - почти всегда быстрее сделать все необходимое в одном SQL-операторе. Рассмотрим небольшой пример: имеется, например, таблица Т с множеством строк, и необходимо изменить значение в столбце для каждой строки этой таблицы. Это можно сделать одним простым оператором:

tkyte@TKYTE816> create table t as select * from all objects; Table created.

tkyte@TKYTE816> set timing on

tkyte@TKYTE816> update t set object name = lower(object name); 21946 rows updated. Elapsed: 00:00:01.12

Многие, однако, по разным причинам предпочитают делать это так:

tkyte@TKYTE816> begin

2 for x in (select rowid rid, object name, rovjnum r

3 from t)

4 loop

5 update t

6 set object name = lower(x.object name)

7 where rowid = x.rid;

8 if (mod(x.r,100) = 0) then

9 commit;

10 end if;

11 end loop;

12 commit;

13 end;

14 /

PL/SQL procedure successfully completed. Elapsed: 00:00:05.99

На этом простом примере показано, что частое фиксирование изменений в цикле в пять раз медленнее. Если что-то можно сделать одним SQL-оператором, делайте это именно так - наверняка получится быстрее.