Блокировки отношений и других объектов

Создадим таблицу "банковских" счетов. В ней храним номер счета и сумму. 

=> CREATE DATABASE locks_objects;

CREATE DATABASE

=> \c locks_objects

You are now connected to database "locks_objects" as user "postgres".

=> CREATE TABLE accounts(acc_no integer, amount numeric);

CREATE TABLE

=> INSERT INTO accounts VALUES (1,1000.00), (2,2000.00), (3,3000.00);

INSERT 0 3

Во втором сеансе начнем транзакцию. Нам понадобится номер обслуживающего процесса. 

=> \c locks_objects

You are now connected to database "locks_objects" as user "postgres".

=> SELECT pg_backend_pid();

 pg_backend_pid 
----------------
          11123
(1 row)


=> BEGIN;

BEGIN

Какие блокировки удерживает только что начавшаяся транзакция? 

=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, granted
FROM pg_locks WHERE pid = 11123;

  locktype  | relation | virtxid | xid |     mode      | granted 
------------+----------+---------+-----+---------------+---------
 virtualxid |          | 3/16    |     | ExclusiveLock | t
(1 row)

Только блокировку собственного виртуального номера.

Теперь обновим строку таблицы. Как изменится ситуация? 

=> UPDATE accounts SET amount = amount + 100 WHERE acc_no = 1;

UPDATE 1

=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, granted
FROM pg_locks WHERE pid = 11123;

   locktype    | relation | virtxid |  xid  |       mode       | granted 
---------------+----------+---------+-------+------------------+---------
 relation      | accounts |         |       | RowExclusiveLock | t
 virtualxid    |          | 3/16    |       | ExclusiveLock    | t
 transactionid |          |         | 26986 | ExclusiveLock    | t
(3 rows)

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

Теперь попробуем в еще одном сеансе создать индекс по таблице. 

=> \c locks_objects

You are now connected to database "locks_objects" as user "postgres".

=> SELECT pg_backend_pid();

 pg_backend_pid 
----------------
          11279
(1 row)


=> CREATE INDEX ON accounts(acc_no);

Команда не выполняется - ждет освобождения блокировки. Какой?

Проверим: 

=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, granted 
FROM pg_locks WHERE pid = 11279;

  locktype  | relation | virtxid | xid |     mode      | granted 
------------+----------+---------+-----+---------------+---------
 virtualxid |          | 4/54    |     | ExclusiveLock | t
 relation   | accounts |         |     | ShareLock     | f
(2 rows)

Видим, что транзакция пыталась получить блокировку таблицы в режиме ShareLock, но не смогла (granted = f).

Мы можем найти номер блокирующего процесса (в общем виде - несколько номеров)... 

=> SELECT pg_blocking_pids(11279);

 pg_blocking_pids 
------------------
 {11123}
(1 row)

...и посмотреть информацию о сеансах, к которым они относятся: 

=> SELECT * FROM pg_stat_activity
WHERE pid = ANY(pg_blocking_pids(11279)) \gx

-[ RECORD 1 ]----+------------------------------------------------------------
datid            | 24842
datname          | locks_objects
pid              | 11123
usesysid         | 10
usename          | postgres
application_name | psql
client_addr      | 
client_hostname  | 
client_port      | -1
backend_start    | 2019-08-12 17:16:12.66099+03
xact_start       | 2019-08-12 17:16:12.74424+03
query_start      | 2019-08-12 17:16:12.826119+03
state_change     | 2019-08-12 17:16:12.827085+03
wait_event_type  | Client
wait_event       | ClientRead
state            | idle in transaction
backend_xid      | 26986
backend_xmin     | 
query            | UPDATE accounts SET amount = amount + 100 WHERE acc_no = 1;
backend_type     | client backend

После завершения транзакции блокировки снимаются и индекс создается. 

=> COMMIT;

COMMIT


CREATE INDEX

Рекомендательные блокировки

Рассмотрим еще один тип блокировок: рекомендательные. В отличие от других блокировок (таких, как блокировки отношений), рекомендательные блокировки устанавливаются только пользователем, но не автоматически. Их используют, если приложению требуется логика блокирования, которую неудобно реализовывать с помощью других, "обычных" блокировок.

Получим блокировку некого условного ресурса. Начнем транзакцию. 

=> BEGIN;

BEGIN

В качестве идентификатора используется число; если ресурс имеет имя, удобно получить число с помощью функции хеширования: 

=> SELECT hashtext('ресурс1');

 hashtext  
-----------
 243773337
(1 row)


=> SELECT pg_advisory_lock(hashtext('ресурс1'));

 pg_advisory_lock 
------------------
 
(1 row)

Информация о рекомендательных блокировках доступна в pg_locks: 

=> SELECT locktype, objid, virtualxid AS virtxid, mode, granted 
FROM pg_locks WHERE pid = 11123;

  locktype  |   objid   | virtxid |     mode      | granted 
------------+-----------+---------+---------------+---------
 virtualxid |           | 3/17    | ExclusiveLock | t
 advisory   | 243773337 |         | ExclusiveLock | t
(2 rows)

Если другой сеанс попробует захватить ту же блокировку, он будет ждать ее освобождения: 

=> SELECT pg_advisory_lock(hashtext('ресурс1'));

В приведенном примере блокировка действует до конца сеанса, а не транзакции, как обычно. 

=> COMMIT;

COMMIT

=> SELECT locktype, objid, virtualxid AS virtxid, mode, granted 
FROM pg_locks WHERE pid = 11123;

 locktype |   objid   | virtxid |     mode      | granted 
----------+-----------+---------+---------------+---------
 advisory | 243773337 |         | ExclusiveLock | t
(1 row)

Ее можно явно освободить: 

=> SELECT pg_advisory_unlock(hashtext('ресурс1'));

 pg_advisory_unlock 
--------------------
 t
(1 row)



 pg_advisory_lock 
------------------
 
(1 row)

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

Конец демонстрации.