Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 7046 / 737 | Оценка: 3.81 / 3.53 | Длительность: 19:30:00
ISBN: 978-5-9963-0488-2
Лекция 13:

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

< Лекция 12 || Лекция 13: 12345 || Лекция 14 >

Транзакции в 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;
< Лекция 12 || Лекция 13: 12345 || Лекция 14 >
Ярослав Прозоров
Ярослав Прозоров

В лекции № 7 "Введение в Oracle SQL" в подразделе "Несамостоятельность группировки с обобщениями ROLLUP, CUBE и GROUPING SETS"  представленная таблица сравнения содержит ошибки - окончания запросов пропущены. Видимо, ошибки вызваны некорректным переносом материала лекции.

Володимир Миколайчук
Володимир Миколайчук
Помогите разобраться поетапно с логикой запроса
-------TOOLS
NAME PRICE TYPE
drill 155 A
sawzall 192 N
mitre saw 292 M
router 86 I
RAD 145 M
jigsaw 128 I
screwdriver 77 P
------TOOL_TYPES
TYPE USAGE
A Always
I Often
M Sometimes
N Rarely
P Never

Запрос SQL:
SELECT t.type, SUM(t.price)
FROM tools t
GROUP BY t.type
HAVING SUM(t.price) >= (SELECT AVG(price)
FROM tools
WHERE type IN (SELECT type
FROM tool_types
WHERE usage = 'Often'));

И сколько строк он все таки вернет
Анатолий Федоров
Анатолий Федоров
Россия, Москва, Московский государственный университет им. М. В. Ломоносова, 1989
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001