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

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

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

Пример блокирования действий в транзакции

Oracle не запрещает разным транзакциям одновременно править разные строки одной и той же таблицы. Однако попытка транзакции изменять строку, уже изменяемую другой незавершенной транзакцией, приведет к блокировке претендующей транзакции. Ниже на двух экранах показана работа двух транзакций, сначала с разными строками EMP, а затем с общей строкой (текущее время отображается подсказкой для ввода строки в SQL*Plus):



Обратите внимание на "долгое" выполнение последнего оператора UPDATE на нижнем экране (42,51 секунды). В данном случае оно означает не то, что уменьшение зарплаты Миллеру на 100 единиц происходило так медленно, а то, что транзакция на нижнем экране после обращения к СУБД с заявкой на UPDATE была переведена в состояние ожидания (сеанс "завис"). Ее работу "заблокировала" транзакция на верхнем экране. Как только блокирующая транзакция завершилась, блокированная продолжила работу и на деле выполнила UPDATE. Об этой синхронизованности событий указывает (в подсказке SQL*Plus) окончательно одно и то же текущее время двух сеансов — для одной транзакции после ее последней команды ROLLBACK, а для другой — после ее последней команды UPDATE.

Упражнение. Проверьте возможность правки разными транзакциями (сеансами) одной строки таблицы, но разных полей.

Легко убедиться, что приведенная схема блокировок не препятствует появлению взаимных блокировок двух разных транзакций (deadlocks). Хорошая новость в том, что Oracle автоматически распознает появление взаимных блокировок и отменяет действие операции, виновной в этом. Одна из транзакций сможет продолжать работу и по своему окончанию освободит вторую.

Упражнение. Спровоцируйте взаимную блокировку двух транзакций и проверьте реакцию на это Oracle.

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

Замки, их типы, режимы наложения и правила совместимости

Замки (locks, иначе — "блокировки") в Oracle являются средством предотвращения нежелательного одновременного доступа к данным и внутренним структурам СУБД путем либо выстраивания процессов СУБД в очередь, либо прерывания операции с возвращением в программу ошибки доступа.

Замки имеют тип (type) и режим наложения (mode).

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

  • TM: блокировка транзакцией целой таблицы ("DML-блокировка");
  • TX: блокировка транзакцией отдельных строк таблицы ("блокировка транзакции").

Режимов наложения замка на объект шесть:

Режим блокировки Код — краткое название (Иногда) иное название Старое название
NULL 1 — NL
ROW SHARE 2 — RS SUB SHARED (SS) SHARE UPDATE
ROW EXCLUSIVE 3 — RX SUB EXCLUSIVE (SX)
SHARE 4 — S
SHARE ROW EXCLUSIVE 5 — SRX SHARED SUB EXCLUSIVE (SSX)
EXCLUSIVE 6 — X

Режимы с характеристикой EXCLUSIVE предполагают монопольное овладевание объектом, а режимы с характеристикой SHARE — долевое, допускающее одновременное нахождение на объекте нескольких однотипных замков (для режима SRX первенствует поведение EXCLUSIVE). Эти две характеристики ("виды блокировок") существуют в общем подходе к устройству транзакций. Слово ROW в названиях сообщает о действии замка на группу строк, а отсутствие этого слова — о действии на всю таблицу.

Замки типа TX могут налагаться СУБД в режимах RS и RX; типа TM — во всех.

Если транзакция пытается наложить на объект замок в режиме, несовместимом с режимом ранее наложенного на тот же объект замка, она либо (а) будет поставлена в очередь, либо (б) получит от СУБД сообщение об ошибке. Правила совместимости режимов наложения замков:

Режим претендующей блокировки
Режим имеющейся блокировки NL RS RX S SRX X
NL OK[1] OK OK OK OK OK
RS OK (OK)[2] (OK) (OK) (OK) Несовм.[3]
RX OK (OK) (OK) Несовм. Несовм. Несовм.
S OK OK Несовм. OK Несовм. Несовм.
SRX OK OK Несовм. Несовм. Несовм. Несовм.
X OK Несовм. Несовм. Несовм. Несовм. Несовм.

[1] режим нового замка совместим с режимом ранее наложенного, и замок будет применен

[2] режим нового замка совместим с режимом ранее наложенного, если замок устанавливается командой LOCK TABLE, и "условно совместим", если замок устанавливается командами UPDATE, DELETE и SELECT … FOR UPDATE; в последнем случае транзакция встанет в очередь ожидания, если другие транзакции не заблокировали требуемые строки (TX)

[3] режим нового замка несовместим с режимом ранее наложенного и попытка его наложить на объект приведет к ошибке

Замки могут накладываться СУБД автоматически (неявно) и программой пользователя явочным порядком. Все замки, наложенные в течение транзакции как явно, так и неявно, автоматически снимаются по ее завершению. При возврате к точке сохранения (ROLLBACK TO SAVEPOINT …) автоматически снимаются замки, наложенные в течение транзакции с момента выдачи команды создания точки сохранения.

< Лекция 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'));

И сколько строк он все таки вернет