Точки отката и субтранзакции

В идеале транзакции должны быть атомарными – такими же неделимыми, как представлялись атомы древним грекам. Однако в действительности даже атомы неделимыми не являются. С появлением реализации SQL: 1999 транзакции в базах данных также перестали быть атомарными. Транзакция теперь может состоять из множества субтранзакций. Субтранзакции отделяются друг от друга точками отката, задаваемыми с помощью оператора SAVEPOINT. Он может использоваться в сочетании с оператором ROLLBACK. До появления точек отката оператор ROLLBACK применялся только для отмены всей транзакции, теперь его можно использовать для отката транзакции до имеющейся в ней точки отката. Вы можете спросить: а для чего это нужно?

В основном оператор ROLLBACK применяется для восстановления исходных данных после того, как транзакция была прервана из-за ошибки. Понятно, что если во время транзакции произошла ошибка, то бессмысленно делать откат только к ближайшей точке отката. Ведь для того, чтобы вернуть базу данных в состояние, в котором она была перед началом транзакции, нужен откат всей транзакции. Но это еще не вся правда. Возникают ситуации, в которых нужен откат именно части транзакции.

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

Чтобы поместить в код SQL точку отката, используйте следующий синтаксис:

SAVEPOINT имя_точки_сохранения;

Откат транзакции к этой точке можно выполнить с помощью следующего кода:

ROLLBACK TO SAVEPOINT имя_точки_сохранения;

Некоторые реализации SQL не поддерживают оператор SAVEPOINT. Если в вашей реализации его нет, то использовать этот оператор вы не сможете.

Ограничения в транзакциях

Проверка достоверности данных заключается не только в проверке правильности типа данных. Кроме этого, возможно, потребуется следить, чтобы в некоторых столбцах не было неопределенных значений, а в других – значения не выходили за границы определенного диапазона. Об этих ограничениях см. в главе 5.

Говорить о связи ограничений с транзакциями имеет смысл потому, что первые влияют на работу последних. Предположим, например, что нужно добавить данные в таблицу, в которой имеется столбец с ограничением NOT NULL. Как правило, в начале создают пустую строку и затем заполняют ее значениями. Однако ограничение NOT NULL не позволит воспользоваться данным способом, поскольку SQL не даст ввести строку с неопределенным значением, находящимся в столбце с ограничением NOT NULL, даже если данные в этот столбец предполагается ввести еще до конца транзакции. Для решения этой проблемы в SQL:2003 существует возможность определять ограничения как DEFERRABLE (задерживаемые) или NOT DEFERRABLE (незадерживаемые).

Ограничения, определенные как NOT DEFERRABLE, применяются немедленно. А те, что определены как DEFERRABLE, первоначально могут быть заданы как DEFERRED (задержанные) или IMMEDIATE (немедленные). Если ограничение типа DEFERRABLE задано как IMMEDIATE, то оно действует так, как и ограничение типа NOT DEFERRABLE, – немедленно. Если же ограничение типа DEFERRABLE задано как DEFERRED, то его действие может быть отсрочено.

Чтобы добавлять пустые записи или выполнять другие операции, которые могут нарушить ограничения типа DEFERRABLE, можно использовать следующий оператор:

SET CONSTRAINTS ALL DEFERRED;

Он определяет все ограничения типа DEFERRABLE как DEFERRED. На ограничения типа NOT DEFERRABLE этот оператор не действует. После того как выполнены все операции, которые могут нарушить ваши ограничения, и таблица достигла того состояния, когда их нарушать уже нельзя, тогда эти ограничения можно применить заново. Соответствующий оператор выглядит следующим образом:

SET CONSTRAINTS ALL IMMEDIATE;

Если вы сделали ошибку и данные не соответствуют каким-либо ограничениям, вы сразу же это увидите после выполнения данного оператора.

Если ограничения, ранее заданные как DEFERRED, явно не задаются вами как IMMEDIATE, то, когда вы попытаетесь завершить свою транзакцию с помощью оператора COMMIT, SQL активизирует все задержанные ограничения. Если к этому моменту не все ограничения выполняются, транзакция будет отменена и SQL выдаст сообщение об ошибке.

Ограничения в SQL защищают от ввода неправильных данных (или, что так же важно, от недопустимого отсутствия данных), причем у вас есть возможность на время действия транзакции временно отменить имеющиеся ограничения.

Чтобы увидеть, насколько важна отсрочка применения ограничений, проанализируйте пример с платежными ведомостями.

Предположим, что в таблице EMPLOYEE (сотрудник) имеются столбцы EmpNo (номер сотрудника), EmpName (фамилия сотрудника), DeptNo (номер отдела) и Salary (оклад). DeptNo является внешним ключом, который ссылается на таблицу DEPT (отдел). Предположим также, что в таблице DEPT имеются столбцы DeptNo и DeptName (название отдела), причем DeptNo – это первичный ключ.

Представим, что, кроме этого, вы хотите иметь такую же таблицу, как и DEPT, но в которой еще есть столбец Payroll (платежная ведомость), и в нем для каждого отдела имеется сумма значений Salary сотрудников этого отдела.

Эквивалент этой таблицы можно создать с помощью следующего представления:

CREATE VIEW DEPT2 ASSELECT D.*, SUM(E.Salary) AS PayrollFROM DEPT D, EMPLOYEE EWHERE D.DeptNo = E.DeptNoGROUP BY D.DeptNo;

Точно такое же представление можно определить еще и другим способом:

CREATE VIEW DEPT3 ASSELECT D.*,(SELECT SUM(E.Salary)FROM EMPLOYEE EWHERE D.DeptNo = E.DeptNo) AS PayrollFROM DEPT D;

Но предположим, что для большей эффективности вы не собираетесь вычислять значение SUM каждый раз, когда ссылаетесь на столбец DEPT.Payroll. Вместо этого вы хотите, чтобы в таблице DEPT в действительности находился столбец Payroll. Значения в этом столбце вы будете обновлять каждый раз, когда будете вносить изменения в столбце Salary.

И, чтобы обеспечить правильность значений в столбце Salary, в определение таблицы можно вставить ограничение:

CREATE TABLE DEPT(DeptNo CHAR(5),DeptName CHAR(20),Payroll DECIMAL(15.2),CHECK (Payroll = (SELECT SUM(Salary)FROM EMPLOYEE E WHERE E.DeptNo = DEPT.DeptNo)));

Теперь предположим, что вам надо увеличить на 100 значение Salary сотрудника под номером 123. Это можно сделать с помощью следующего обновления:

UPDATE EMPLOYEESET Salary = Salary + 100WHERE EmpNo = '123';

Кроме того, следует обновить таблицу DEPT:

UPDATE DEPT DSET Payroll = Payroll + 100WHERE D.DeptNo = (SELECT E.DeptNoFROM EMPLOYEE EWHERE E.EmpNo 423');

(Подзапрос используется для того, чтобы получить ссылку на значение DeptNo сотрудника с идентификационным номером 123.)

Но здесь имеется трудность: после каждого оператора ограничения проверяются. Теоретически должны проверяться все ограничения. Но на практике реализациями проверяются только те из них, которые относятся к значениям, измененным в ходе работы оператора.

После первого из двух последних операторов UPDATE реализация проверяет все ограничения, которые ссылаются на измененные им значения. В число этих ограничений входит то, которое определено в таблице DEPT, потому что оно относится к столбцу Salary таблицы EMPLOYEE, а значения в этом столбце изменяются оператором UPDATE. После выполнения первого оператора UPDATE это ограничение оказалось нарушенным. Допустим, что перед выполнением этого оператора база данных находится в полном порядке и каждое значение Payroll в таблице DEPT равно сумме значений Salary из соответствующих строк таблицы EMPLOYEE. Тогда, как только первый оператор UPDATE увеличит значение Salary, это равенство выполняться не будет. Такая ситуация исправляется вторым оператором UPDATE, после выполнения которого значения базы данных соответствуют имеющимся ограничениям. Но в промежутке между этими обновлениями ограничения не выполняются.

Оператор SET CONSTRAINTS DEFERRED дает возможность временно отключить все или только указанные вами ограничения. Действие этих ограничений будет задержано до тех пор, пока выполнится или оператор SET CONSTRAINTS IMMEDIATE, или один из двух операторов: COMMIT и ROLLBACK. Следовательно, в нашем случае перед оператором UPDATE и после него необходимо поместить операторы SET CONSTRAINTS:

SET CONSTRAINTS DEFERRED;UPDATE EMPLOYEESET Salary = Salary + 100WHERE EmpNo = '123';UPDATE DEPT DSET Payroll = Payroll + 100WHERE D.DeptNo = (SELECT E.DeptNoFROM EMPLOYEE EWHERE E.EmpNo = '123');SET CONSTRAINTS IMMEDIATE;

Эта процедура откладывает действие всех ограничений. Например, при вставке в DEPT новых строк первичные ключи проверяться не будут; т.е. вы удалили и ту защиту, которая, возможно, вам нужна. Поэтому следует явно указывать ограничения, которые надо задержать. Для этого при создании ограничений им следует давать имена:

CREATE TABLE DEPT(DeptNo CHAR(5),DeptName CHAR(20),Payroll DECIMAL(15.2),CONSTRAINT PayEqSumsalCHECK (Payroll = SELECT SUM(Salary)FROM EMPLOYEE EWHERE E.DeptNo = DEPT.DeptNo));

На ограничения с именами можно ссылаться индивидуально:

SET CONSTRAINTS PayEqSumsal DEFERRED;UPDATE EMPLOYEESET Salary = Salary + 100WHERE EmpNo = '12 3';UPDATE DEPT DSET Payroll = Payroll + 100WHERE D.DeptNo = (SELECT E.DeptNoFROM EMPLOYEE EWHERE E.EmpNo = 423');SET CONSTRAINTS PayEqSumsal IMMEDIATE;

Если для ограничения в операторе CREATE не указано имя, то SQL создает это имя неявно. Оно находится в специальных таблицах – таблицах каталога. Но все-таки лучше явно задавать имена ограничений.

Теперь предположим, что во втором операторе UPDATE в качестве значения возрастания вы по ошибке указали 1000. Это значение в операторе UPDATE является разрешенным, потому что имеющееся ограничение было отсрочено. Но при выполнении оператора SET CONSTRAINTS…IMMEDIATE будут проверяться указанные в нем ограничения. Если они не соблюдаются, то будет сгенерировано исключение. Но если вместо оператора SET CONSTRAINTS…IMMEDIATE выполняется оператор COMMIT, а ограничения не соблюдаются, то вместо оператора COMMIT выполняется оператор ROLLBACK.

Подведем итог. Временно отменять действие ограничений можно только внутри транзакции. Как только транзакция завершается, ограничения сразу же вступают в силу. Если использовать эту возможность правильно, то транзакция не создаст никаких данных, нарушающих какие-либо ограничения.