Блокировки при нескольких обновлениях строки

Для простоты создадим таблицу без первичного ключа.

=> 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, amount numeric);

CREATE TABLE

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

INSERT 0 3

Создадим представление над 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

Первая транзакция обновляет и, соответственно, блокирует строку:

=> BEGIN;

BEGIN

=> SELECT txid_current(), pg_backend_pid();

 txid_current | pg_backend_pid 
--------------+----------------
       215338 |          29324
(1 row)

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

UPDATE 1

Вторая транзакция делает то же самое:

=> \c locks_rows

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

=> BEGIN;

BEGIN

=> SELECT txid_current(), pg_backend_pid();

 txid_current | pg_backend_pid 
--------------+----------------
       215339 |          29457
(1 row)

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

И третья:

=> \c locks_rows

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

=> BEGIN;

BEGIN

=> SELECT txid_current(), pg_backend_pid();

 txid_current | pg_backend_pid 
--------------+----------------
       215340 |          29531
(1 row)

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

Блокировки для первой транзакции:

=> SELECT * FROM locks WHERE pid = 29324;

  pid  |   locktype    |  lockid  |       mode       | granted 
-------+---------------+----------+------------------+---------
 29324 | relation      | pg_locks | AccessShareLock  | t
 29324 | relation      | locks    | AccessShareLock  | t
 29324 | relation      | accounts | RowExclusiveLock | t
 29324 | virtualxid    | 6/5      | ExclusiveLock    | t
 29324 | transactionid | 215338   | ExclusiveLock    | t
(5 rows)

Тип relation для pg_locks и locks в режиме AccessShareLock - устанавливаются на читаемые отношения.
Тип relation для accounts в режиме RowExclusiveLock - устанавливается на изменяемое отношение.
Типы virtualxid и transactionid в режиме ExclusiveLock - удерживаются каждой транзакцией для самой себя.

Блокировки для второй транзакции:

=> SELECT * FROM locks WHERE pid = 29457;

  pid  |   locktype    |   lockid   |       mode       | granted 
-------+---------------+------------+------------------+---------
 29457 | relation      | accounts   | RowExclusiveLock | t
 29457 | virtualxid    | 2/84       | ExclusiveLock    | t
 29457 | transactionid | 215339     | ExclusiveLock    | t
 29457 | transactionid | 215338     | ShareLock        | f
 29457 | tuple         | accounts:1 | ExclusiveLock    | t
(5 rows)

По сравнению с первой транзакцией:

Блокировки для pg_locks и locks отсутствуют, так как вторая транзакция не обращалась к этим отношениям.
Транзакция ожидает получение блокировки типа transactionid в режиме ShareLock для первой транзакции.
Удерживается блокировка типа tuple для обновляемой строки.

Блокировки для третьей транзакции:

=> SELECT * FROM locks WHERE pid = 29531;

  pid  |   locktype    |   lockid   |       mode       | granted 
-------+---------------+------------+------------------+---------
 29531 | relation      | accounts   | RowExclusiveLock | t
 29531 | virtualxid    | 4/100415   | ExclusiveLock    | t
 29531 | transactionid | 215340     | ExclusiveLock    | t
 29531 | tuple         | accounts:1 | ExclusiveLock    | f
(4 rows)

Транзакция ожидает получение блокировки типа tuple для обновляемой строки.

Общую картину текущих ожиданий можно увидеть в представлении pg_stat_activity. Для удобства можно добавить и информацию о блокирующих процессах:

=> SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid) 
FROM pg_stat_activity 
WHERE backend_type = 'client backend';

  pid  | wait_event_type |  wait_event   | pg_blocking_pids 
-------+-----------------+---------------+------------------
 29457 | Lock            | transactionid | {29324}
 29531 | Lock            | tuple         | {29457}
 29324 |                 |               | {}
(3 rows)

=> ROLLBACK;

ROLLBACK

=> ROLLBACK;

UPDATE 1
ROLLBACK

=> ROLLBACK;

UPDATE 1
ROLLBACK

Взаимоблокировка трех транзакций

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

=> BEGIN;

BEGIN

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

UPDATE 1

=> BEGIN;

BEGIN

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

UPDATE 1

=> BEGIN;

BEGIN

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

UPDATE 1

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

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

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

=> COMMIT;

=> COMMIT;

=> COMMIT;

ERROR:  deadlock detected
DETAIL:  Process 29324 waits for ShareLock on transaction 215342; blocked by process 29457.
Process 29457 waits for ShareLock on transaction 215343; blocked by process 29531.
Process 29531 waits for ShareLock on transaction 215341; blocked by process 29324.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"
ROLLBACK

UPDATE 1
COMMIT

UPDATE 1
COMMIT

Вот какую информацию о взаимоблокировке можно получить из журнала:

postgres$ tail -n 10 /home/postgres/logfile

2019-08-12 17:25:55.279 MSK [29324] ERROR:  deadlock detected
2019-08-12 17:25:55.279 MSK [29324] DETAIL:  Process 29324 waits for ShareLock on transaction 215342; blocked by process 29457.
	Process 29457 waits for ShareLock on transaction 215343; blocked by process 29531.
	Process 29531 waits for ShareLock on transaction 215341; blocked by process 29324.
	Process 29324: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
	Process 29457: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 3;
	Process 29531: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
2019-08-12 17:25:55.279 MSK [29324] HINT:  See server log for query details.
2019-08-12 17:25:55.279 MSK [29324] CONTEXT:  while updating tuple (0,2) in relation "accounts"
2019-08-12 17:25:55.279 MSK [29324] STATEMENT:  UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;

Взаимоблокировка двух операций UPDATE

Команда UPDATE блокирует строки по мере их обновления. Это происходит не одномоментно.

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

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

=> BEGIN;

BEGIN

=> DECLARE c1 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE;

DECLARE CURSOR

=> BEGIN;

BEGIN

=> DECLARE c2 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no DESC -- в обратную сторону
FOR UPDATE;

DECLARE CURSOR

=> FETCH c1;

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

=> FETCH c2;

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

=> FETCH c1;

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

=> FETCH c2;

Вторая команда ожидает блокировку...

=> FETCH c1;

Произошла взаимоблокировка. И через некоторое время:

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

ERROR:  deadlock detected
DETAIL:  Process 29531 waits for ShareLock on transaction 215344; blocked by process 29457.
Process 29457 waits for ShareLock on transaction 215345; blocked by process 29531.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,8) in relation "accounts"

=> COMMIT;

=> COMMIT;