Создадим таблицу как в демонстрации; номер счета будет первичным ключом.
=> 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 PRIMARY KEY, 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
----------------
28663
(1 row)
=> BEGIN;
BEGIN
=> SELECT * FROM accounts WHERE acc_no = 1;
acc_no | amount
--------+---------
1 | 1000.00
(1 row)
Блокировки:
=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, granted FROM pg_locks WHERE pid = 28663;
locktype | relation | virtxid | xid | mode | granted ------------+---------------+---------+-----+-----------------+--------- relation | accounts_pkey | | | AccessShareLock | t relation | accounts | | | AccessShareLock | t virtualxid | | 2/75 | | ExclusiveLock | t (3 rows)
Здесь мы видим:
Если смотреть блокировки в самой транзакции, к ним добавится блокировка на таблицу pg_locks:
=> SELECT locktype, relation::REGCLASS, virtualxid AS virtxid, transactionid AS xid, mode, granted FROM pg_locks WHERE pid = 28663;
locktype | relation | virtxid | xid | mode | granted ------------+---------------+---------+-----+-----------------+--------- relation | pg_locks | | | AccessShareLock | t relation | accounts_pkey | | | AccessShareLock | t relation | accounts | | | AccessShareLock | t virtualxid | | 2/75 | | ExclusiveLock | t (4 rows)
=> COMMIT;
COMMIT
Начинаем транзакцию и читаем одну строку.
=> BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
=> SELECT * FROM accounts WHERE acc_no = 1;
acc_no | amount
--------+---------
1 | 1000.00
(1 row)
Блокировки:
=> SELECT locktype, relation::REGCLASS, page, tuple, virtualxid AS virtxid, transactionid AS xid, mode, granted FROM pg_locks WHERE pid = 28663;
locktype | relation | page | tuple | virtxid | xid | mode | granted ------------+---------------+------+-------+---------+-----+-----------------+--------- relation | accounts_pkey | | | | | AccessShareLock | t relation | accounts | | | | | AccessShareLock | t virtualxid | | | | 2/76 | | ExclusiveLock | t page | accounts_pkey | 1 | | | | SIReadLock | t tuple | accounts | 0 | 1 | | | SIReadLock | t (5 rows)
К предыдущим блокировкам добавились:
=> COMMIT;
COMMIT
Требуется изменить параметры:
=> ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM
=> ALTER SYSTEM SET deadlock_timeout = '100ms';
ALTER SYSTEM
=> SELECT pg_reload_conf();
pg_reload_conf ---------------- t (1 row)
Воспроизведем блокировку.
=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = 10.00 WHERE acc_no = 1;
UPDATE 1
=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = 100.00 WHERE acc_no = 1;
В первом сеансе выполним задержку и после этого завершим транзакцию.
=> SELECT pg_sleep(1);
pg_sleep ---------- (1 row)
=> COMMIT;
COMMIT
UPDATE 1
=> COMMIT;
COMMIT
Вот что попало в журнал:
postgres$ tail -n 7 /home/postgres/logfile
2019-08-12 17:25:51.399 MSK [28663] LOG: process 28663 still waiting for ShareLock on transaction 215332 after 100.319 ms 2019-08-12 17:25:51.399 MSK [28663] DETAIL: Process holding the lock: 28610. Wait queue: 28663. 2019-08-12 17:25:51.399 MSK [28663] CONTEXT: while updating tuple (0,1) in relation "accounts" 2019-08-12 17:25:51.399 MSK [28663] STATEMENT: UPDATE accounts SET amount = 100.00 WHERE acc_no = 1; 2019-08-12 17:25:52.369 MSK [28663] LOG: process 28663 acquired ShareLock on transaction 215332 after 1070.271 ms 2019-08-12 17:25:52.369 MSK [28663] CONTEXT: while updating tuple (0,1) in relation "accounts" 2019-08-12 17:25:52.369 MSK [28663] STATEMENT: UPDATE accounts SET amount = 100.00 WHERE acc_no = 1;