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

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

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

Неявные блокировки при операциях DML

При поступлении из программы любой из команд INSERT, UPDATE или DELETE, СУБД сначала автоматически попытается наложить на объект определенные замки и только в случае успеха приступит к самому изменению данных. По меньшей мере, замков два:

типа TM в режиме ROW EXCLUSIVE;

типа TX в режиме EXCLUSIVE.

Наблюдать блокировки можно запросами SQL к таблицам словаря-справочника, но нагляднее их представляет Oracle Enterprize Manager (OEM — программа для администрирования Oracle). Возвращаясь к примеру выше, после первой выдачи UPDATE в рамках первой транзакции (она выполнялась сеансом 128) программа OEM показала следующие замки:

После попытки выполнить UPDATE для той же строки другой транзакцией (сеанс 139) эта транзакция "подвисла", а программа OEM показала следующие замки:

Заметьте, что замки типа TM в режиме наложения ROW EXCLUSIVE не помешали друг другу, а попытка второй транзакции наложить замок типа TX в режиме EXCLUSIVE привела к постановке этой транзакции в очередь ожидания. Мешающий этому действию замок будет снят только по концу первой транзакции.

Влияние внешних ключей

Наличие внешних ключей в схеме обычно приводит к дополнительным замкам на объектах БД и к дополнительным шансам блокирования работы транзакций.

Если таблицы связаны внешним ключом, выполнение INSERT, UPDATE внешнего ключа одной таблицы или ключа (первичного или уникального) другой и DELETE для подчиненной таблицы автоматически добавит третий и, возможно, четвертый замок:

  • типа TM в режиме ROW SHARE на партнерскую таблицу;
  • типа TX в режиме EXCLUSIVE на партнерскую таблицу.

В некоторых версиях Oracle в подобном поведении возможны непринципиальные варианты.

При выполнении команды SELECT … FOR UPDATE применительно к родительской таблице СУБД автоматически добавит второй замок:

  • типа TM в режиме ROW SHARE на эту таблицу.

Вот как OEM показывает замки, возникшие после выдачи UPDATE на изменение значения DEPTNO сотрудника из таблицы EMP:

Обратите внимание на наложение двух "лишних" замков, уже на таблицу DEPT. Пример показывает, что полезные с точки зрения моделирования предметной области внешние ключи приводят к увеличению количества замков на объектах, а значит — повышают вероятность блокирования одними транзакциями других.

Явное наложение замка типа TM на таблицу командой LOCK TABLE

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

Предложение LOCK TABLE в Oracle позволяет наложить замок в требуемом режиме на таблицу:

LOCK TABLE имя_таблицы 
IN режим_блокировки MODE 
[NOWAIT | WAIT [ n ]]

Само по себе это предложение не полностью решает задачу сохранения контроля над работой программы, так как сама команда LOCK TABLE может столкнуться с несовместимым замком, повешенным ранее другой транзакцией. Для окончательного закрытия проблемы применяется указание NOWAIT/WAIT.

Указание NOWAIT заменит в случае несовместимости замков ожидание на немедленный возврат в программу сообщения об ошибке. Теперь уже дело программиста обработать такую ошибку (исключительную ситуацию, exception) и довести ее в понятном виде до конечного пользователя.

Указание WAIT соответствует умолчательному поведению, когда при занятости хотя бы одной строки таблицы другими транзакциями выдающая LOCK TABLE будет ждать их завершения. Указание же WAIT n тоже приведет к ожиданию, но не более n секунд. Если за это время мешающий замок будет с таблицы снят, претендующая транзакция повесит на нее свой и продолжит работу. Если же по истечении n секунд таблица не освободится, программа получит сообщение об ошибке. Указание WAIT 0 равносильно NOWAIT.

Вариант WAIT n разрешен с версии 11.

Примеры:

LOCK TABLE dept IN SHARE MODE;
LOCK TABLE emp IN EXCLUSIVE MODE NOWAIT; 

Блокировку таблицы в режимах SHARE и EXCLUSIVE можно специально запретить или же наоборот, разрешить.

Пример:

ALTER TABLE emp DISABLE TABLE LOCK;
< Лекция 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