Unlocking the Postgres Lock Manager

锁定对于为任何无法充分利用硬件的数据库提供高并发性至关重要,如果锁定限制了其使用。本文通过显示查询及其锁来探索Postgres中锁的所有方面;覆盖的锁类型包括行、表、共享、独占和咨询锁类型。本文还讨论了多版本并发控制(MVCC)提供的高并发性。

展开查看详情

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