Наиболее частый случай блокировок - блокировки, возникающие на уровне строк.

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

=> CREATE DATABASE locks_rows;

CREATE DATABASE

=> \c locks_rows

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

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

CREATE TABLE

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

INSERT 0 3

Поскольку информация о блокировке строк хранится только в самих версиях строк, воспользуемся знакомым расширением pageinspect.

=> CREATE EXTENSION pageinspect;

CREATE EXTENSION

Для удобства создадим представление, расшифровывающее интересующие нас информационные биты в первых трех версиях строк.

=> CREATE VIEW accounts_v AS
SELECT '(0,'||lp||')' AS ctid,
       t_xmax as xmax,
       CASE WHEN (t_infomask & 1024) > 0  THEN 't' END AS commited,
       CASE WHEN (t_infomask & 2048) > 0  THEN 't' END AS aborted,
       CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,
       CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,
       CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd
FROM heap_page_items(get_raw_page('accounts',0))
WHERE lp <= 3
ORDER BY lp;

CREATE VIEW

Обновляем сумму первого счета (ключ не меняется) и номер второго счета (ключ меняется):

=> \c locks_rows

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

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

=> UPDATE accounts SET acc_no = 20 WHERE acc_no = 2;

Заглянем в представление. Напомним, что оно показывает только первые три (то есть исходные) версии строк.

=> SELECT * FROM accounts_v;

 ctid  | xmax  | commited | aborted | lock_only | is_multi | keys_upd 
-------+-------+----------+---------+-----------+----------+----------
 (0,1) | 26993 |          |         |           |          | 
 (0,2) | 26993 |          |         |           |          | t
 (0,3) |     0 |          | t       |           |          | 
(3 rows)

По столбцу keys_upd видно, что строки заблокированы в разных режимах.

Теперь в другом сеансе запросим разделяемые блокировки первого и третьего счетов:

=> \c locks_rows

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

=> SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE;

 acc_no | amount  
--------+---------
      1 | 1000.00
(1 row)

=> SELECT * FROM accounts WHERE acc_no = 3 FOR SHARE;

 acc_no | amount  
--------+---------
      3 | 3000.00
(1 row)

Все запрошенные блокировки совместимы друг с другом. В версиях строк видим:

=> SELECT * FROM accounts_v;

 ctid  | xmax  | commited | aborted | lock_only | is_multi | keys_upd 
-------+-------+----------+---------+-----------+----------+----------
 (0,1) |     1 |          |         |           | t        | 
 (0,2) | 26993 |          |         |           |          | t
 (0,3) | 26994 |          |         | t         |          | 
(3 rows)

Столбец lock_only позволяет отличить просто блокировку от обновления или удаления. В первой строке видим, что обычный номер заменен на номер мультитранзакции - об этом говорит столбец is_multi.

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

=> CREATE EXTENSION pgrowlocks;

CREATE EXTENSION

=> SELECT * FROM pgrowlocks('accounts') \gx

-[ RECORD 1 ]-----------------------------
locked_row | (0,1)
locker     | 1
multi      | t
xids       | {26993,26994}
modes      | {"No Key Update","Key Share"}
pids       | {11973,12082}
-[ RECORD 2 ]-----------------------------
locked_row | (0,2)
locker     | 26993
multi      | f
xids       | {26993}
modes      | {Update}
pids       | {11973}
-[ RECORD 3 ]-----------------------------
locked_row | (0,3)
locker     | 26994
multi      | f
xids       | {26994}
modes      | {"For Share"}
pids       | {12082}

Расширение дает информацию о режимах всех блокировок.

=> ROLLBACK;

=> ROLLBACK;

"Очередь" ожидания

Для удобства создадим представление над pg_locks, "свернув" в одно поле идентификаторы разных типов блокировок:

=> CREATE VIEW locks AS
SELECT pid,
       locktype,
       CASE locktype
         WHEN 'relation' THEN relation::REGCLASS::text
         WHEN 'virtualxid' THEN virtualxid::text
         WHEN 'transactionid' THEN transactionid::text
         WHEN 'tuple' THEN relation::REGCLASS::text||':'||tuple::text
       END AS lockid,
       mode,
       granted
FROM pg_locks;

CREATE VIEW

Пусть одна транзакция заблокирует строку в разделяемом режиме...

=> SELECT txid_current(), pg_backend_pid();

 txid_current | pg_backend_pid 
--------------+----------------
        26997 |          11882
(1 row)

=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;

 acc_no | amount  
--------+---------
      1 | 1000.00
(1 row)

=> SELECT txid_current(), pg_backend_pid();

 txid_current | pg_backend_pid 
--------------+----------------
        26998 |          11973
(1 row)

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

В представлении pg_locks можно увидеть, что вторая транзакция ожидает завершения первой (granted = f):

=> SELECT * FROM locks WHERE pid = 11973;

  pid  |   locktype    |    lockid     |       mode       | granted 
-------+---------------+---------------+------------------+---------
 11973 | relation      | accounts_pkey | RowExclusiveLock | t
 11973 | relation      | accounts      | RowExclusiveLock | t
 11973 | virtualxid    | 3/26          | ExclusiveLock    | t
 11973 | transactionid | 26997         | ShareLock        | f
 11973 | transactionid | 26998         | ExclusiveLock    | t
 11973 | tuple         | accounts:1    | ExclusiveLock    | t
(6 rows)

При этом она удерживает блокировку версии строки (locktype = tuple).

Чтобы не разбираться, кто кого блокирует, по представлению pg_locks, можно узнать номер (или номера) процесса блокирующего сеанса с помощью функции:

=> SELECT pg_blocking_pids(11973);

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

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

=> SELECT txid_current(), pg_backend_pid();

 txid_current | pg_backend_pid 
--------------+----------------
        26999 |          12082
(1 row)

=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;

 acc_no | amount  
--------+---------
      1 | 1000.00
(1 row)

=> SELECT * FROM pgrowlocks('accounts') \gx

-[ RECORD 1 ]-------------
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {26997,26999}
modes      | {Share,Share}
pids       | {11882,12082}

После того, как первая транзакция завершится, вторая не сможет обновить строку потому, что остается третья транзакция.

=> COMMIT;

Теперь вторая транзакция ожидает завершения третьей:

=> SELECT * FROM locks WHERE pid = 11973;

  pid  |   locktype    |    lockid     |       mode       | granted 
-------+---------------+---------------+------------------+---------
 11973 | relation      | accounts_pkey | RowExclusiveLock | t
 11973 | relation      | accounts      | RowExclusiveLock | t
 11973 | virtualxid    | 3/26          | ExclusiveLock    | t
 11973 | transactionid | 26998         | ExclusiveLock    | t
 11973 | transactionid | 26999         | ShareLock        | f
 11973 | tuple         | accounts:1    | ExclusiveLock    | t
(6 rows)

=> COMMIT;

=> ROLLBACK;

Как избежать ожидания

Обычно команды SQL ожидают освобождения блокировки. Но иногда хочется отказаться от выполнения, если блокировку не удалось получить сразу же. Для этого есть два варианта команды SELECT FOR.

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

=> SELECT * FROM accounts FOR UPDATE NOWAIT;

ERROR:  could not obtain lock on row in relation "accounts"

Второй вариант - использовать фразу SKIP LOCKED. При этом команда будет пропускать заблокированные строки, но обрабатывать оставшиеся.

=> DECLARE c CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no FOR UPDATE SKIP LOCKED;

DECLARE CURSOR

=> FETCH c;

 acc_no | amount  
--------+---------
      2 | 2000.00
(1 row)

Практическая применимость такого подхода состоит в многопоточной обработке очередей.

=> ROLLBACK;

=> ROLLBACK;

Взаимоблокировка

Обычная причина возникновения взаимоблокировок - разный порядок блокирования строк таблиц.

Первая транзакция намерена перенести 100 рублей с первого счета на второй. Для этого она сначала уменьшает первый счет:

=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;

В это же время вторая транзакция намерена перенести 10 рублей со второго счета на первый. Она начинает с того, что уменьшает второй счет:

=> UPDATE accounts SET amount = amount - 10.00 WHERE acc_no = 2;

Теперь первая транзакция пытается увеличить второй счет, но обнаруживает, что строка заблокирована.

=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;

Затем вторая транзакция пытается увеличить первый счет, но тоже блокируется.

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

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

ERROR:  deadlock detected
DETAIL:  Process 11973 waits for ShareLock on transaction 27003; blocked by process 12082.
Process 12082 waits for ShareLock on transaction 27002; blocked by process 11973.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"

=> COMMIT;

=> COMMIT;

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

Блокировки строк

"Очередь" ожидания

Как избежать ожидания

Взаимоблокировка