Опубликован: 28.12.2011 | Доступ: свободный | Студентов: 6789 / 674 | Оценка: 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'));

И сколько строк он все таки вернет
Дмитрий Квашнёв
Дмитрий Квашнёв
Россия, Коломна, Московский государственный открытый университет, 2001
Павел Счетчиков
Павел Счетчиков
Россия, Казань