α=> CREATE DATABASE admin_monitoring;
CREATE DATABASE
α=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
α=> 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 "student".
α=> SELECT * FROM pg_stat_all_tables WHERE relid='t'::regclass \gx
-[ RECORD 1 ]-------+------- relid | 17729 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
We have just inserted 1000 rows (n_tup_ins = 1000) and deleted 1000 rows (n_tup_del = 1000).
After that there is no active row versions left (n_live_tup = 0), all of 1000 row versions are dead at the moment (n_dead_tup = 1000).
α=> VACUUM;
VACUUM
α=> SELECT * FROM pg_stat_all_tables WHERE relid='t'::regclass \gx
-[ RECORD 1 ]-------+------------------------------ relid | 17729 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-02-27 15:39:00.990653+03 last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | 1 autovacuum_count | 0 analyze_count | 0 autoanalyze_count | 0
Dead tuples are cleaned up by the vacuum (n_dead_tup = 0), vacuum has processed the table once (vacuum_count = 1).
α=> INSERT INTO t VALUES (1),(2);
INSERT 0 2
The first transaction locks the first row...
student$ psql
α=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
α=> BEGIN;
BEGIN
α=> UPDATE t SET n=10 WHERE n=1;
UPDATE 1
The second transaction locks the second row...
student$ psql
α=> \c admin_monitoring
You are now connected to database "admin_monitoring" as user "student".
α=> BEGIN;
BEGIN
α=> UPDATE t SET n=200 WHERE n=2;
UPDATE 1
Now the first transaction tries to change the second row and waits for the lock to release...
α=> UPDATE t SET n=20 WHERE n=2;
And the second transaction tries to change the first row...
α=> UPDATE t SET n=100 WHERE n=1;
...which leads to a deadlock.
UPDATE 1
ERROR: deadlock detected DETAIL: Process 21646 waits for ShareLock on transaction 45310; blocked by process 21721. Process 21721 waits for ShareLock on transaction 45309; blocked by process 21646. HINT: See server log for query details. CONTEXT: while updating tuple (0,2) in relation "t"
student$ tail -n 8 /var/log/postgresql/postgresql-11-alpha.log
2019-02-27 15:39:05.608 MSK [21646] student@admin_monitoring ERROR: deadlock detected 2019-02-27 15:39:05.608 MSK [21646] student@admin_monitoring DETAIL: Process 21646 waits for ShareLock on transaction 45310; blocked by process 21721. Process 21721 waits for ShareLock on transaction 45309; blocked by process 21646. Process 21646: UPDATE t SET n=20 WHERE n=2; Process 21721: UPDATE t SET n=100 WHERE n=1; 2019-02-27 15:39:05.608 MSK [21646] student@admin_monitoring HINT: See server log for query details. 2019-02-27 15:39:05.608 MSK [21646] student@admin_monitoring CONTEXT: while updating tuple (0,2) in relation "t" 2019-02-27 15:39:05.608 MSK [21646] student@admin_monitoring STATEMENT: UPDATE t SET n=20 WHERE n=2;