Вопросы оптимизации. Транзакции и блокировки

Транзакции в Oracle

Для работы с транзакциями Oracle поддерживает следующие операторы SQL:

COMMIT [ WORK ]
ROLLBACK [ WORK ] [ TO SAVEPOINT имя_точки_сохранения ]
SAVEPOINT имя_точки_сохранения
SET TRANSACTION тип_транзакции

Слово WORK в COMMIT и ROLLBACK носит косметический характер и употребляется по желанию.

Команды COMMIT и ROLLBACK

В Oracle отсутствует команда для создания новой транзакции (в отличие от стандарта SQL), но есть две команды завершения: фиксацией результатов выполнявшихся в последней транзакции команд DML (COMMIT) и отказом от них (ROLLBACK). Соединение с СУБД автоматически приводит к началу новой транзакции, и то же случается по завершению отработки любой команды COMMIT или ROLLBACK. Таким образом, все операции с данными (таблиц, индексов, внутренних объектов LOB) волей-неволей всегда выполняются в Oracle в рамках какой-нибудь транзакции, а сеанс связи программы с СУБД выглядит последовательностью сменяющих друг друга транзакций. Команды завершения транзакции затрагивают только операции DML, но в некоторых случаях СУБД порождает такие команды самостоятельно. Так, всякая команда DDL завершается неявной (скрытой) выдачей COMMIT; аварийный разрыв сеанса или возникновение исключительной ситуации на уровне программы сопровождается неявной выдачей ROLLBACK.

Пример:

CONNECT scott/tiger
-- открыта новая транзакция
INSERT INTO emp ( empno, ename ) VALUES ( 1111, 'BUSH' );
UPDATE emp SET ename = 'LADEN' WHERE empno = 1111;
ROLLBACK;
-- старая транзакция завершена отменой UPDATE и INSERT; открыта новая транзакция

Иногда приводят два методических правила по употреблению COMMIT:

1. выдавать COMMIT, как только представится возможным, и
2. не выдавать COMMIT раньше необходимого.

Операция COMMIT затратна для СУБД и при особо частой выдаче может заметно тормозить работу СУБД. В версии 10 введена возможность ускоренного выполнения COMMIT. Для этого в команде можно указать ключевые слова BATCH ("групповая фиксация": запись о выдаче COMMIT заносится в буфер журнала в СУБД, но не провоцирует перенос журнальных записей в файл) или NOWAIT (СУБД начинает обрабатывать следующую команду сеанса, не дожидаясь фактического завершения отработки COMMIT):

COMMIT WRITE [BATCH | IMMEDIATE] [WAIT | NOWAIT]

Пример такого поведения можно наблюдать, прогнав в SQL*Plus с помощью сценарного файла следующий текст:

CONNECT / AS SYSDBA
SELECT dname, ora_rowscn FROM scott.dscn;
UPDATE scott.dscn SET dname = dname WHERE ROWNUM <= 2;
COMMIT;
STARTUP FORCE
SELECT dname, ora_rowscn FROM scott.dscn;
UPDATE scott.dscn SET dname = dname WHERE ROWNUM <= 2;
COMMIT WRITE BATCH NOWAIT;
STARTUP FORCE
SELECT dname, ora_rowscn FROM scott.dscn;

Здесь разрыв транзакции достигается форсированной перезагрузкой СУБД: STARTUP FORCE.

Любое из указаний BATCH или NOWAIT в команде COMMIT WRITE отменяет гарантию со стороны СУБД попадания последних изменений в БД в случае сбоя, невзирая на выдачу программой пользователя COMMIT.

Умолчательный способ отработки COMMIT при наличии указанных вариантов можно установить для всей СУБД (ALTER SYSTEM …) и для отдельных сеансов (ALTER SESSION …) параметрами СУБД:

COMMIT_WRITE[10]
COMMIT_LOGGING[11-)
COMMIT_WAIT[11-)

[10] В версии 10.

[11-)] С версии 11.

Примеры:

ALTER SESSION SET COMMIT_WRITE = 'batch, nowait';

С версии 11:

ALTER SESSION SET COMMIT_LOGGING = 'batch';
ALTER SESSION SET COMMIT_WAIT = 'force_wait';

Команды ROLLBACK и ROLLBACK TO SAVEPOINT

Команда SAVEPOINT позволяет поставить в последовательности команд DML поименованную "точку сохранения", к которой можно вернуться в рамках текущей транзакции с тем, чтобы дать ей с этого места новое продолжение:

CONNECT scott/tiger
-- новая транзакция ...
INSERT INTO emp ( empno, ename ) VALUES ( 1111, 'BUSH' );
UPDATE emp SET job = 'PRESIDENT' WHERE ename = 'BUSH';
SAVEPOINT try_new_employee;
-- поставили точку сохранения TRY_NEW_EMPLOYEE 
UPDATE emp SET ename = 'LADEN' WHERE ename = 'BUSH';
DELETE FROM emp WHERE ename = 'LADEN';
SELECT ename FROM emp WHERE empno = 1111;
-- сотрудник 'LADEN'
ROLLBACK TO SAVEPOINT try_new_employee;
-- вернулись к точке сохранения, отказавшись от последних DELELE и UPDATE
SELECT ename FROM emp WHERE empno = 1111;
-- сотрудник 'BUSH'
UPDATE emp SET job = 'CLERK' WHERE ename = 'BUSH';
ALTER TABLE emp ADD UNIQUE ( job );
-- хотя ошибка, но операции DDL, а потому неявная выдача COMMIT и новая транзакция ...
ROLLBACK;
-- новая транзакция, и далее без ошибок
SELECT ename FROM emp WHERE empno = 1111;
DELETE FROM emp WHERE ename = 'BUSH';
COMMIT;

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

В некоторых типах СУБД нет точек сохранения, зато есть более развитый аппарат вложенных транзакций.

Команда SET TRANSACTION

Команда SET TRANSACTION позволяет в начале транзакции (точнее, до выдачи первого изменяющего данные оператора DML) назначить тип транзакции.

Транзакция любого типа в Oracle не сможет увидеть изменения незавершенных других транзакций (нет так называемых "грязных" транзакций).

Тип транзакции READ WRITE умолчательный и не требует явного указания командой SET TRANSACTION. Транзакция этого типа позволяет программе выдавать команды DML изменения данных и наблюдать их результат, как будто бы он непосредственно совершается в БД.

Задание типа READ ONLY дает начало "читающей" транзакции, в течение которой программа изолируется от изменений в БД (выполняемых другими транзакциями) и видит состояние БД на момент выдачи SET TRANSACTION READ ONLY.

"Читающие" транзакции полезны при составлении отчетов, когда программе нужны согласованные данные из нескольких таблиц базы. Однако попытка выполнить в них INSERT, UPDATE или DELETE приведет к ошибке.

Тип транзакции ISOLATION LEVEL SERIALIZABLE аналогичен READ ONLY, но не запрещает выполнять собственные операции INSERT, UPDATE или DELETE. Последнее дает программисту свободу действия по сравнению с READ ONLY, однако чревато риском для транзакции оказаться заблокированной.

Пример последовательности выдачи команд в SQL*Plus:

CONNECT scott/tiger
-- новая транзакция, по умолчанию — READ WRITE ...
INSERT INTO emp ( empno, ename ) VALUES ( 1111, 'BUSH' );
HOST sqlplus scott/tiger
SELECT ename FROM emp WHERE empno = 1111;
-- сотрудник не виден  
EXIT
COMMIT;
-- новая транзакция ...
SELECT ename FROM emp WHERE empno = 1111;
-- сотрудник 1111 находится в БД и виден  
SET TRANSACTION READ ONLY;
-- установили тип READ ONLY ...
DELETE FROM emp WHERE empno = 1111;
-- ошибка: транзакция READ ONLY !  
HOST sqlplus scott/tiger
DELETE FROM emp WHERE empno = 1111;
COMMIT;
EXIT
SELECT ename FROM emp WHERE empno = 1111;
-- сотрудник все еще виден  
ROLLBACK;
SELECT ename FROM emp WHERE empno = 1111;
-- ... а по завершению транзакции уже нет
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- установили тип ISOLATION LEVEL SERIALIZABLE ...
INSERT INTO emp ( empno, ename ) VALUES ( 1111, 'OBAMA' );
HOST sqlplus scott/tiger
INSERT INTO emp ( empno, ename ) VALUES ( 2222, 'LADEN' );
COMMIT;
EXIT
SELECT ename FROM emp WHERE empno IN ( 1111, 2222 );
-- виден только 'OBAMA'  
ROLLBACK;
SELECT ename FROM emp WHERE empno IN ( 1111, 2222 );
-- виден только 'LADEN'  
DELETE FROM emp WHERE empno = 2222;
COMMIT;
-- "почистили" данные

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

ALTER SESSION SET ISOLATION LEVEL SERIALIZABLE;