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

Явная блокировка групп строк в таблицах

Недостатком блокирования данных таблицы командой LOCK TABLE может оказаться чересчур широкий охват строк, способный порождать по существу ненужные ожидания среди прочих транзакций. Зарезервировать для собственных нужд группы строк, а не все строки таблицы целиком, можно оператором SELECT, завершив его специальной фразой FOR UPDATE.

Предложение

SELECT ... 
FOR UPDATE [список_имен_столбцов] 
[NOWAIT | WAIT [ n ]]

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

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

Примеры:

SELECT * FROM emp WHERE deptno = 10 FOR UPDATE; 

Здесь программа получает сведения о сотрудниках 10-го отдела и тут же резервирует за собой право свободно их изменять до конца транзакции.

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

SELECT empno, sal, comm
FROM   emp INNER JOIN dept
       USING ( deptno )
WHERE  job = 'CLERK'
  AND  loc = 'NEW YORK' 
FOR UPDATE
; 

Здесь программа получает сведения о сотрудниках-клерках из Нью-Йорка и тут же блокирует строки об этих сотрудниках в таблице EMP и строкe об отделе этих сотрудников из таблицы DEPT. В подобных случаях снова может возникать проблема чересчур широкого блокирования. Сузить охват блокирования искусственно позволяет уточнение OF …:

SELECT empno, sal, comm
FROM   emp INNER JOIN dept
       USING ( deptno )
WHERE  job = 'CLERK'
  AND  loc = 'NEW YORK' 
FOR UPDATE OF emp.sal
;

Здесь программа получает те же сведения, что и в предыдущем запросе, но резервироваться до конца транзакции будут только строки из таблицы EMP с сотрудниками-клерками из Нью-Йорка. Строки из таблицы DEPT не блокируются.

Казалось бы, в уточнении OF … достаточно сослаться на имена таблиц, строки которых мы хотим зарезервировать, в то время как Oracle требует указывать здесь список столбцов таблиц. Логика такого синтаксического оформления следующая: сообщить после слова OF перечень тех полей строк используемых в запросе таблиц, которые мы намерены править до конца транзакции. Таким образом, фраза FOR UPDATE OF приобретает документирующий характер, способствующий лучшему восприятию текста программистом.

Указания NOWAIT и WAIT [ n ] даются и действуют аналогично таким же в команде LOCK TABLE, однако вариант WAIT n стал доступен много раньше, с версии 8.

Групповая блокировка исключительно свободных строк

С версии 8.1 существует разновидность групповой блокировки с обходом уже кем-то блокированных в данный момент строк. Запрос ниже выдаст в программу и одновременно пометит для текущей транзакции замками только те строки о сотрудниках отдела 10, которые свободны в настоящее время от несовместимых замков, принадлежащих другим транзакциям:

SELECT * FROM emp WHERE deptno = 10 FOR UPDATE SKIP LOCKED; 

Строки с существующими несовместимыми замками "наша" транзакция попросту проигнорирует, обойдет.

Такая возможность позволяет сократить и упростить программный код. Долгое время она была недокументирована, но с версии 11 стала официальной.

Замки доступа, используемые предложениями DDL

На время выполнения предложений DDL ALTER TABLE, DROP TABLE и LOCK TABLE … IN EXCLUSIVE MODE СУБД пытается "блокировать" таблицу замком типа TM в режиме EXCLUSIVE. Если там уже имеется замок от предшествовавшей операции DML, попытка выполнить операцию DDL может завершиться неудачей.

Упражнение. Проверьте работу блокировок при выполнении операций DDL. Выполните в одном сеансе (т. е. в одной транзакции):

UPDATE emp SET sal = sal WHERE ename = 'SCOTT';

Выполните в другом сеансе (т. е. в другой транзакции):

DROP TABLE emp;

Таблицы словаря-справочника

В основе "статических таблиц" словаря-справочника Oracle лежит относительно небольшое число исходных (основных) таблиц, таких как OBJ$, TAB$, COM$, IND$, AUD$, PROPS$ и им подобных. О наличии этих таблиц в схеме SYS документация по Oracle не сообщает. Но на их основе построено большое число выводимых (виртуальных) таблиц, "представлений" данных, доставляющих справочную информацию об объектах в более удобном виде, собственно и предназначенных разработчиками Oracle для обычных потребителей (технически при помощи вдобавок одноименных таблицам публичных синонимов), например, таблицы:

USER(ALL, DBA)_TABLES
USER(ALL, DBA)_TAB_COLUMNS
USER(ALL, DBA)_INDEXES
USER(ALL, DBA)_CONSTRAINTS
USER(ALL, DBA)_CONS_COLUMNS
USER(ALL, DBA)_IND_COLUMNS
USER(ALL, DBA)_SEQUENCES
USER(ALL, DBA)_TRIGGERS
USER(ALL, DBA)_TRIGGER_COLS
...

Префикс USER обозначает перечисление объектов пользователя, префикс ALL — объектов, доступных для работы из схемы, префикс DBA — всех объектов БД. Таблицы с префиксом DBA обычному пользователю, как правило, не видны. В то же время некоторое количество таблиц-представлений словаря-справочника не имеют префиксов USER, ALL или DBA и именованы по-своему.

Так, таблица DICTIONARY является "справочной по справочнику"; она содержит список названий практически всех (виртуальных) таблиц словаря-справочника (за редкими исключениями) с краткими пояснениями — в том числе название самой себя. Она предназначена для поиска нужной справочной таблицы путем составления запроса на SQL, например:

SELECT table_name FROM dictionary WHERE table_name LIKE 'USER%DICT%';

Для простоты употребления заведено несколько дополнительных публичных (PUBLIC) синонимов:

• TABS (синоним для USER_TABLES) — список всех таблиц схемы пользователя;
• COLS (синоним для USER_TAB_COLUMNS) — список всех столбцов всех таблиц схемы пользователя;
• SEQ (синоним для USER_SEQUENCES) — список всех генераторов последовательностей чисел в схеме пользователя;
• SYN (синоним для USER_SYNONYMS) — список всех синонимов схемы пользователя;
• CAT (синоним для USER_CATALOG) — список основных таблиц и представлений данных, синонимов и генераторов последовательностей чисел в схеме пользователя;
• OBJ (синоним для USER_OBJECTS) — список всех объектов схемы пользователя;
• IND (синоним для USER_INDEXES) — список всех индексов схемы пользователя;
• DICT (синоним для DICTIONARY).

Введя в словарь-справочник представления данных, разработчики Oracle достигли сразу две цели:

• дали программистам БД более удобный вид на данные словаря-справочника, чем это обеспечивают исходные таблицы (а при проектировании последних приоритет отдавался критерию скорости доступа);
• уменьшили риски случайной порчи справочной части БД из-за некорректных изменений операторами DML напрямую справочных таблиц.

Когда пользователь работает в графической среде разработки типа SQL Developer, он редко испытывает нужду в прямом обращении к таблицам словаря-справочника: за него это скрытно делает программа. Однако даже в этом случае запросы к справочным таблицам на SQL способны иногда дать ответ, недоступный вовсе или получаемый крайне неудобно с помощью графической среды; например: "в каких таблицах имеется столбец с таким-то именем?"

Встроеный SQL

Включающие языки для Oracle: С/C++, Ada, COBOL, PL/1, FORTRAN, Java (SQLJ), SQL*Plus.

Соответствующие компоненты программного обеспечения Oracle: Pro*C, Pro*Ada, Pro*PL/1, Pro*FORTRAN, SQLJ, ... .

Пример оформления запроса SQL для включающего языка C/C++ (переменные привязки name, salary, d_no):

exec sql     select ename, sal
        into :name, :salary
        from emp
        where deptno = :d_no;

Пример для включающего языка Java (переменная привязки cnt):

#sql { 
       SELECT COUNT ( * )
       INTO :cnt
       FROM emp 
     }

Пример из PL/SQL в окружении SQL*Plus (name, salary, d_no — переменные в SQL*Plus):

BEGIN
  SELECT ename, sal INTO :name, :salary
  FROM   emp
  WHERE  deptno = :d_no
  ;
END;

Порядок обработки исходного текста программы в общем случае: