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

Неявные блокировки при операциях 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;