Unlocking the Postgres Lock Manager

1 . Unlocking the Postgres Lock Manager BRUCE MOMJIAN This talk explores all aspects of locking in Postgres by showing queries and their locks; covered lock types include row, table, shared, exclusive, and advisory lock types. Creative Commons Attribution License http://momjian.us/presentations Last updated: July, 2018 1 / 110 

2 .No One Likes Locking But it Is Necessary for Proper Database Operation https://www.flickr.com/photos/mplemmon/ 2 / 110 

3 . Outline 1. Locking Introduction 2. Transaction Identifiers 3. Lock Types 4. Lock Examples 3 / 110 

4 .1. Locking Introduction https://www.flickr.com/photos/54409200@N04/ 4 / 110 

5 . What an Adventure! Xyzzy Little maze of twisting passages Little maze of twisty passages Little twisty maze of passages Maze of little twisting passages Maze of little twisty passages Maze of twisting little passages Maze of twisty little passages Twisting little maze of passages Twisting maze of little passages Twisty little maze of passages Twisty maze of little passages http://en.wikipedia.org/wiki/Colossal_Cave_Adventure#Maze_of_twisty_little_passages 5 / 110 

6 . The Real Postgres Lock Types ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE 6 / 110 

7 . Share/Exclusive Types ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE 7 / 110 

8 . Row/Access Types ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE 8 / 110 

9 . MVCC Multiversion Concurrency Control (MVCC) allows Postgres to offer high concurrency even during significant database read/write activity. MVCC specifically offers behavior where "readers never block writers, and writers never block readers". While Multiversion Concurrency Control (MVCC) reduces locking requirements, it does not eliminate locking. 9 / 110 

10 .2. Transaction Identifiers https://www.flickr.com/photos/grendelkhan/ 10 / 110 

11 .Keep Your Eye on the Red (Text) https://www.flickr.com/photos/alltheaces/ 11 / 110 

12 . What Is Our Process Identifier (PID)? SELECT pg_backend_pid(); pg_backend_pid ---------------- 11306 All queries used in this presentation are available at http://momjian. us/main/writings/pgsql/locking.sql. 12 / 110 

13 . What Is Our Virtual XID (VXID) SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/7 | 2 is the backend id, and 7 is the virtual transaction id for this backend, i.e., backend id/backend-local xid. 13 / 110 

14 . What Is Our Backend Id? SELECT * FROM pg_stat_get_backend_idset() AS t(id) WHERE pg_stat_get_backend_pid(id) = pg_backend_pid(); id ---- 2 The maximum backend id is set by max_connections. Query courtesy of Phil Sorber. 14 / 110 

15 . The VXID Increments SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/10 | SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/11 | 15 / 110 

16 . Getting a Real/External/Non-Virtual XID BEGIN WORK; SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/12 | ANALYZE pg_language; 16 / 110 

17 . Getting a Real/External/Non-Virtual XID SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/12 | 674 SELECT txid_current(); txid_current -------------- 674 COMMIT; Transaction identifiers range from 3 to 4 billion (2^32). Zero(0) is an invalid transaction id, and 1 and 2 are used for setting frozen xids (committed and aborted). 17 / 110 

18 . Requesting Your XID Assigns One BEGIN WORK; SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/13 | -- this will assign a non-virtual xid if not already assigned SELECT txid_current(); txid_current -------------- 675 18 / 110 

19 . Requesting Your XID Assigns One SELECT virtualtransaction AS vxid, transactionid::text FROM pg_locks WHERE pid = pg_backend_pid() ORDER BY 1, 2 LIMIT 1; vxid | transactionid ------+--------------- 2/13 | 675 COMMIT; 19 / 110 

20 .3. Lock Types https://www.flickr.com/photos/proimos/ 20 / 110 

21 . Setup: Create View lockview -- cannot be a temporary view because other sessions must see it CREATE VIEW lockview AS SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted, CASE WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL THEN virtualxid || ’ ’ || transactionid WHEN virtualxid::text IS NOT NULL THEN virtualxid ELSE transactionid::text END AS xid_lock, relname, page, tuple, classid, objid, objsubid FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid) WHERE -- do not show our view’s locks pid != pg_backend_pid() AND -- no need to show self-vxid locks virtualtransaction IS DISTINCT FROM virtualxid -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7; 21 / 110 

22 . Create View lockview1 CREATE VIEW lockview1 AS SELECT pid, vxid, lock_type, lock_mode, granted, xid_lock, relname FROM lockview -- granted is ordered earlier ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7; 22 / 110 

23 . Create View lockview2 CREATE VIEW lockview2 AS SELECT pid, vxid, lock_type, page, tuple, classid, objid, objsubid FROM lockview -- granted is first -- add non-display columns to match ordering of lockview ORDER BY 1, 2, granted DESC, vxid, xid_lock::text, 3, 4, 5, 6, 7, 8; 23 / 110 

24 . Create and Populate Table lockdemo CREATE TABLE lockdemo (col int); INSERT INTO lockdemo VALUES (1); 24 / 110 

25 . Explicit ACCESS SHARE Locking BEGIN WORK; LOCK TABLE lockdemo IN ACCESS SHARE MODE; \! psql -e -c ’SELECT * FROM lockview1;’ | sed ’s/^/\t/g’ SELECT * FROM lockview1; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+-----------+-----------------+---------+----------+---------- 11306 | 2/23 | relation | AccessShareLock | t | | lockdemo 25 / 110 

26 . Explicit ACCESS SHARE Locking \! psql -e -c ’SELECT * FROM lockview2;’ | sed ’s/^/\t/g’ SELECT * FROM lockview2; pid | vxid | lock_type | page | tuple | classid | objid | objsubid -------+------+-----------+------+-------+---------+-------+---------- 11306 | 2/23 | relation | | | | | COMMIT; Future slides will only show lockview2 if it contains useful information. 26 / 110 

27 . Implicit ACCESS SHARE Locking BEGIN WORK; SELECT * FROM lockdemo; col ----- 1 \! psql -e -c ’SELECT * FROM lockview1;’ | sed ’s/^/\t/g’ SELECT * FROM lockview1; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+-----------+-----------------+---------+----------+---------- 11306 | 2/24 | relation | AccessShareLock | t | | lockdemo COMMIT; 27 / 110 

28 . Multi-Table ACCESS SHARE Locking BEGIN WORK; SELECT pg_class.oid FROM pg_class JOIN pg_namespace ON (relnamespace = pg_namespace.oid) JOIN pg_attribute ON (pg_class.oid = pg_attribute.attrelid) LIMIT 1; oid ----- 112 28 / 110 

29 . Multi-Table ACCESS SHARE Locking \! psql -e -c ’SELECT * FROM lockview1;’ | sed ’s/^/\t/g’ SELECT * FROM lockview1; pid | vxid | lock_type | lock_mode | granted | xid_lock | relname -------+------+-----------+-----------------+---------+----------+--------------------------------- 11306 | 2/25 | relation | AccessShareLock | t | | pg_attribute 11306 | 2/25 | relation | AccessShareLock | t | | pg_attribute_relid_attnam_index 11306 | 2/25 | relation | AccessShareLock | t | | pg_attribute_relid_attnum_index 11306 | 2/25 | relation | AccessShareLock | t | | pg_class 11306 | 2/25 | relation | AccessShareLock | t | | pg_class_oid_index 11306 | 2/25 | relation | AccessShareLock | t | | pg_class_relname_nsp_index 11306 | 2/25 | relation | AccessShareLock | t | | pg_namespace 11306 | 2/25 | relation | AccessShareLock | t | | pg_namespace_nspname_index 11306 | 2/25 | relation | AccessShareLock | t | | pg_namespace_oid_index OMMIT; 29 / 110