=> CREATE DATABASE admin_monitoring;
CREATE DATABASE
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
=> CREATE TABLE t(n numeric);
CREATE TABLE
=> INSERT INTO t SELECT 1 FROM generate_series(1,1000);
INSERT 0 1000
=> DELETE FROM t;
DELETE 1000
=> \c
You are now connected to database "admin_monitoring" as user "postgres".
=> \x
Expanded display is on.
=> SELECT * FROM pg_stat_all_tables WHERE relid='t'::regclass;
-[ RECORD 1 ]-------+------- relid | 16436 schemaname | public relname | t seq_scan | 1 seq_tup_read | 1000 idx_scan | idx_tup_fetch | n_tup_ins | 1000 n_tup_upd | 0 n_tup_del | 1000 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 1000 n_mod_since_analyze | 2000 last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 0 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0
Мы вставили 1000 строк (n_tup_ins = 1000), удалили 1000 строк (n_tup_del = 1000).
После этого не осталось активных версий строк (n_live_tup = 0), все 1000 строк не актуальны на текущий момент (n_dead_tup = 1000).
=> VACUUM;
VACUUM
=> SELECT * FROM pg_stat_all_tables WHERE relid='t'::regclass;
-[ RECORD 1 ]-------+------------------------------ relid | 16436 schemaname | public relname | t seq_scan | 1 seq_tup_read | 1000 idx_scan | idx_tup_fetch | n_tup_ins | 1000 n_tup_upd | 0 n_tup_del | 1000 n_tup_hot_upd | 0 n_live_tup | 0 n_dead_tup | 0 n_mod_since_analyze | 2000 last_vacuum | 2019-03-31 15:24:20.714225+03 last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 1 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0
Неактуальные версии строк убраны при очистке (n_dead_tup = 0), очистка обрабатывала таблицу один раз (vacuum_count = 1).
=> INSERT INTO t VALUES (1),(2);
INSERT 0 2
Одна транзакция блокирует первую строку таблицы...
postgres$ psql
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
=> BEGIN;
BEGIN
=> UPDATE t SET n=10 WHERE n=1;
UPDATE 1
Затем другая транзакция блокирует вторую строку...
postgres$ psql
=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "postgres".
=> BEGIN;
BEGIN
=> UPDATE t SET n=200 WHERE n=2;
UPDATE 1
Теперь первая транзакция пытается изменить вторую строку и ждет ее освобождения...
=> UPDATE t SET n=20 WHERE n=2;
А вторая транзакция пытается изменить первую строку...
=> UPDATE t SET n=100 WHERE n=1;
...и происходит взаимоблокировка.
UPDATE 1
ERROR: deadlock detected DETAIL: Process 12389 waits for ShareLock on transaction 598; blocked by process 12457. Process 12457 waits for ShareLock on transaction 597; blocked by process 12389. HINT: See server log for query details. CONTEXT: while updating tuple (0,2) in relation "t"
postgres$ tail -n 8 /home/postgres/logfile
2019-03-31 15:24:25.248 MSK [12389] ERROR: deadlock detected 2019-03-31 15:24:25.248 MSK [12389] DETAIL: Process 12389 waits for ShareLock on transaction 598; blocked by process 12457. Process 12457 waits for ShareLock on transaction 597; blocked by process 12389. Process 12389: UPDATE t SET n=20 WHERE n=2; Process 12457: UPDATE t SET n=100 WHERE n=1; 2019-03-31 15:24:25.248 MSK [12389] HINT: See server log for query details. 2019-03-31 15:24:25.248 MSK [12389] CONTEXT: while updating tuple (0,2) in relation "t" 2019-03-31 15:24:25.248 MSK [12389] STATEMENT: UPDATE t SET n=20 WHERE n=2;