Конкурентный доступ к ресурсам и DBMS_LOCK

Сеансы работы с СУБД Oracle есть параллельно выполняющиеся процессы, работающие как с собственными, так и с общими ресурсами. Объекты БД, такие как таблицы, индексы, являются общими ресурсами. CУБД Oracle делает все возможное для того, чтобы конкурентный доступ к табличным данным был эффективным и максимально незаметным - прозрачным - для сеансов. Однако, в ряде случаев от программиста требуется явная блокировка ресурса на время работы с ним, и освобождение ресурса по окончании работы.

Таким ресурсом может быть, например, экземпляр некоторой сущности предметной области (сотрудник, элемент орг. структуры, и т.д.) Строки одной таблицы сеанс может зарезервировать для исключительного использования при помощи SELECT ... FOR UPDATE. Но в случае, когда необходимо заблокировать данные, распределенные по нескольким таблицам, лучшим решением будет специальное соглашение между конкурирующими процессами о доступе к ресурсу. А реализовать такое соглашение поможет пакет DBMS_LOCK.

Ниже я продемонстрирую секцию кода, которая может выполняться одновременно не более, чем в одном сеансе. Вход в такую секцию охраняет исключительная блокировка - мьютекс (mutex, от mutual exclusive), и код в этой секции гарантированно защищен от параллельного выполнения в разных сеансах. Если работать с некоторым ресурсом только внутри таких секций, то одновременный доступ к ресурсу будет исключен.

Пакет DBMS_LOCK предоставляет программисту API для работы с пользовательскими блокировками, которые обладают всеми возможностями системных блокировок СУБД Oracle. Так же, как и системные блокировки, пользовательские блокировки отображаются в динамическом вью v$lock.

Следующий блок PL/SQL создает исключительную блокировку при помощи пакета DBMS_LOCK и выводит ее идентификатор:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      l_lock VARCHAR2(30);
  3      l_status NUMBER;
  4  BEGIN
  5      dbms_lock.allocate_unique('myexclusivelock', l_lock);
  6      l_status := dbms_lock.request(l_lock, dbms_lock.x_mode);
  7      dbms_output.put_line(l_status || ' : ' || l_lock);
  8  END;
  9  /
  
0 : 10737491871073749187101

PL/SQL procedure successfully completed

SQL> /
 
4 : 10737491871073749187101
 
PL/SQL procedure successfully completed

Процедура DBMS_LOCK.ALLOCATE_UNIQUE в строке 5 связывает придуманное программистом имя блокировки (первый агрумент) с числовым идентификатором (второй аргумент), назначенным системой. Далее для работы с блокировкой используется полученный числовой идентификатор.

Функция DBMS_LOCK.REQUEST в строке 6 запрашивает у СУБД блокировку с данным идентификатором в исключительном режиме и получает ее, о чем говорит возвращаемый код 0. Как видим, повторная попытка получить ту же самую блокировку, выполнив PL/SQL блок еще раз, не увенчалась успехом: код возврата 4 означает, что у блокировки уже есть владелец. (Мьютексы в DBMS_LOCK нереентерабельны.)

См. описание функций и процедур пакета DBMS_LOCK в официальной документации по СУБД Oracle.

Полученную нами блокировку можно увидеть во вью v$lock, где пользовательские блокировки имеют тип 'UL':

SQL> select * from v$lock where sid = userenv('sid');
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000C10FA8E80 0000000C10FA8ED8        158 AE          100          0          4          0        903          2
0000000C10FB08F8 0000000C10FB0950        158 UL   1073749187          0          6          0        271          2

Снимем блокировку и убедимся, что она больше не отражается в v$lock:

SQL> DECLARE
  2      l_lock VARCHAR2(30);
  3      l_status NUMBER;
  4  BEGIN
  5      dbms_lock.allocate_unique('myexclusivelock', l_lock);
  6      l_status := dbms_lock.release(l_lock);
  7      dbms_output.put_line(l_status || ' : ' || l_lock);
  8  END;
  9  /

0 : 10737491871073749187101

PL/SQL procedure successfully completed

SQL> select * from v$lock where sid = userenv('sid');
ADDR             KADDR                   SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0000000C10FA8E80 0000000C10FA8ED8        158 AE          100          0          4          0        928          2

Процедура DBMS_LOCK.ALLOCATE_UNIQUE в строке 5 возвращает числовой идентификатор уже существующей блокировки по ее имени. Далее, функция DBMS_LOCK.RELEASE в строке 6 снимает данную блокировку.

Теперь вернемся к сценарию, в котором некоторый код может одновременно выполняться только в одном сеансе. Этот сценарий реализует процедура critical:

SQL> CREATE OR REPLACE PROCEDURE critical
  2  IS
  3      l_lock VARCHAR2(30);
  4      l_status NUMBER;
  5  BEGIN
  6      dbms_lock.allocate_unique('myspeciallock', l_lock);
  7      l_status := dbms_lock.request(l_lock, dbms_lock.x_mode);
  8      IF l_status = 0 THEN
  9         -- теперь никто не помешает поспать :)
 10         dbms_lock.sleep(5);
 11      END IF;
 12      l_status := dbms_lock.release(l_lock);
 13  END;
 14  /

Procedure created

Чтобы создать процедуру critical, текущему пользователю должна быть явно (не через роль) предоставлена привилегия EXECUTE на пакет SYS.DBMS_LOCK.

Для запуска процедуры critical в нескольких сеансах параллельно воспользуюсь возможностями пакета DBMS_SCHEDULER:

SQL> BEGIN
  2      FOR i IN 1..3 LOOP
  3          dbms_scheduler.create_job(
  4              job_name => 'critical_' || i,
  5              job_type => 'STORED_PROCEDURE',
  6              job_action => 'critical',
  7              ENABLED => TRUE
  8          );
  9      END LOOP;
 10  END;
 11  /

PL/SQL procedure successfully completed

Дав процедуре время выполниться, проверю результат выполнения:

SQL> SELECT job_name, status, actual_start_date, run_duration
  2  FROM all_scheduler_job_run_details
  3  WHERE job_name LIKE 'CRITICAL%';

JOB_NAME        STATUS          ACTUAL_START_DATE                                RUN_DURATION
--------------- --------------- ------------------------------------------------ ---------------
CRITICAL_1      SUCCEEDED       22-MAY-14 07.46.08.287061 PM ASIA/VLADIVOSTOK    +000 00:00:05
CRITICAL_2      SUCCEEDED       22-MAY-14 07.46.08.397432 PM ASIA/VLADIVOSTOK    +000 00:00:10
CRITICAL_3      SUCCEEDED       22-MAY-14 07.46.08.419444 PM ASIA/VLADIVOSTOK    +000 00:00:15

Как видим, три задачи отработали, причем каждой следующей понадобилось на 5 секунд больше, чтобы выполниться. Это - время, в течение которого функция DBMS_LOCK.REQUEST ожидала получения блокировки во втором и третьем сеансах.

Мы рассмотрели работу с исключительной (exclusive) блокировкой, но не менее важны разделяемые (shared) блокировк. Они позволяют параллельным процессам более эффективно работать с общим ресурсом. Исключительной блокировкой может владеть только один процесс, и, пока он ею владеет, ни один другой процесс не может получить доступ к заблокированному ресурсу. Разделяемой блокировкой могут владеть многие процессы, и, пока хотя бы один процесс ею владеет, ни один другой процесс не может получить исключительную блокировку данного ресурса.

Как это используется? Например, процессы, которым нужно только читать (но не изменять) некоторый ресрус, блокируют его в разделяемом режиме, что позволяет другим процессам также получать доступ к нему в разделяемом режиме, не дожидаясь его полного освобождения. Однако, процесс, которому необходимо изменить ресурс, для получения исключительной блокировки должен дождаться, когда с ресурса будут сняты все блокировки. И пока этот процесс будет изменять ресурс, ни один другой процесс не сможет получить доступ к ресурсу ни для чтения, ни для изменения.

Проиллюстрирую совместную работу разделяемой и исключительной блокировок. Для этого создам процедуру sharedmode, подобную critical, но, в отличие от нее, sharedmode запрашивает блокировку в разделяемом режиме:

SQL> CREATE OR REPLACE PROCEDURE sharedmode
  2  IS
  3      l_lock VARCHAR2(30);
  4      l_status NUMBER;
  5  BEGIN
  6      dbms_lock.allocate_unique('myspeciallock', l_lock);
  7      l_status := dbms_lock.request(l_lock, dbms_lock.s_mode);
  8      IF l_status = 0 THEN
  9         -- теперь никто не помешает поспать :)
 10         dbms_lock.sleep(5);
 11      END IF;
 12      l_status := dbms_lock.release(l_lock);
 13  END;
 14  /
 
Procedure created

Теперь в трех различных сеансах параллельно запущу процедуры sharedmode, а в четвертом сеансе - процедуру critical. Три процесса, выполняющие sharedmode, получат разделяемые блокировки сразу, как только за ними обратятся, и выполнятся приблизительно за 5 секунд, перед завершением сняв блокировки. Процесс, выполняющий процедуру critical, получит исключительную блокировку только после того, как будут сняты разделяемые блокировки, и будет выполняться еще в течение 5 секунд, итого, около 10 секунд.

SQL> BEGIN
  2      FOR i IN 1..3 LOOP
  3          dbms_scheduler.create_job(
  4              job_name => 'special_' || i,
  5              job_type => 'STORED_PROCEDURE',
  6              job_action => 'sharedmode',
  7              ENABLED => TRUE
  8          );
  9      END LOOP;
 10      dbms_scheduler.create_job(
 11          job_name => 'special_x',
 12          job_type => 'STORED_PROCEDURE',
 13          job_action => 'critical',
 14          ENABLED => TRUE
 15      );
 16  END;
 17  /
 
PL/SQL procedure successfully completed
 

SQL> SELECT job_name, status, actual_start_date, run_duration
  2  FROM all_scheduler_job_run_details
  3  WHERE job_name LIKE 'SPECIAL%'
  4  ORDER BY job_name;
 
JOB_NAME        STATUS          ACTUAL_START_DATE                                RUN_DURATION
--------------- --------------- ------------------------------------------------ ---------------
SPECIAL_1       SUCCEEDED       25-MAY-14 09.52.16.574000 PM +11:00              +000 00:00:05
SPECIAL_2       SUCCEEDED       25-MAY-14 09.52.16.734000 PM +11:00              +000 00:00:05
SPECIAL_3       SUCCEEDED       25-MAY-14 09.52.16.829000 PM +11:00              +000 00:00:05
SPECIAL_X       SUCCEEDED       25-MAY-14 09.52.16.893000 PM +11:00              +000 00:00:10
 

Последний запрос полностью подтвердил ожидания.

На этом закончу эксперименты с DBMS_LOCK:

SQL> DROP PROCEDURE critical;
Procedure dropped

SQL> DROP PROCEDURE sharedmode;
Procedure dropped

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

• отправку и получение сигналов параллельно выполняющимися процессами при помощи пакета DBMS_ALERT,
• передачу данных между параллельно пыполняющимися процессами при помощи пакета DBMS_PIPE.