Transcript of Bruce Momjian's 2020 report "Unlocking the Postgres Lock Manager".
(Note: You can get all SQL queries from slides at this link: http://momjian.us/main/writings/pgsql/locking.sql )
! . , , . , , . . , . , .
. EnterpriseDB Postgres 23 . , . 90 . 40 . , , . . 30 . , 500. . , .
, , Postgres. , , . . 110 . , , .
. – . , - . .
- , . . . . .
- . , .
- . .
- . .
.
. , ? . , « ». 80- , . , , . .
, Oracle. .
, . , SHARE UPDATE ECXLUSIVE. SHARE RAW ECXLUSIVE. , . . «share», – . «exclusive» — . . , .
«» — access. «row» — . . . , .
, Postgres, , , , MVCC. -. , , MVCC – , , . , . .
, – . . , . Postgres . , .
, , , , .
http://momjian.us/main/writings/pgsql/locking.sql
. . SELECT pg_back. ID .
, , , , SQL . PSQL . , .
ID . , . ID Postgres, ID
. , Postgres.
ID – ID , . , SELECT, , , , . , SELECT, ID. ID.
Postgres, , ID . – ID . .
, , ID – 2.
, , , . , 2/10, 2/11, 2/12 . .
, . ID – 2/12. ID . . . ID .
( (ANALYZE)), ID . , . ID, .
, , . – 2/13. ID , , , .
, . ID ID . , Postgres.
. Postgres. . . , , .
, . , , , , , , Postgres.
, Postgres, system view. pg_lock. Pg_lock – , , Postgres.
pg_lock , . view, pg_locks. , . . . , . . SQL , .
, view , – lockview2.
. , . , .
, , Lockdemo. . . , .
, , . ACCESS SHARE. . , .
, «lock table». , . . ACCESS SHARE lock table. PSQL , . . . ? « lockview ». AccessShareLock . , . , . .
, , . .
«SELECT», () AccessShareLock. , . AccessShareLock. SELECT AccessShareLock . , .
SELECT ? , : pg_class, pg_namespace pg_attribute.
, , 9 AccessShareLocks . ? : pg_attribute, pg_class, pg_namespace. , , , AccessShareLock.
, . , , , . . . . , , , AccessShare – , " , ". , , .
ROW SHARE – .
. SELECT ROW SHARE . , .
, , SHARE LOCK? , ID 681 SELECT’. . ? «Lock». ID , , . , , , , - . , . .
, .
, () , . , , .
SHARE EXCLUSIVE – .
(ANALYZE) , .
SHARE LOCK – share.
. SHARE LOCK, . SHARE LOCK.
SHARE LOCK , , . , .
concurrently , , , , concurrently . , , . concurrently , .
SHARE ROW EXCLUSIVE – ().
, . . - , .
EXCLUSIVE , .
.
ACCESS EXCLUSIVE, , . , CLUSTER table, , . , .
ACCESS EXCLUSIVE, , . , .
, . , ID , ID , ID .
. . . – , Postgres , . , .
.
. -, ExclusiveLock, ID ExclusiveLock .
, ? . . , ? , , . , , Postgres.
, , 100 , 100 . 1 000 , 1 000 . . , . , , , . , , Postgres .
, , , MVCC, . , , Postgres . .
? , , . , . , . , , .
, ? , . , .
, Postgres , MVCC, , Postgres , . Postgres , , , .
- ? , , , , .
, , 1 000 , , 1 000 , , , . . MVCC . , , , , .
?
«», . « », .
. , . .
, shared, 30 .
, , .
, Postgres, – , update select. . select update . , , , , .
. . INSERT. – 694. ID , . , .
ID, – 695.
, 695 .
, . 695 – , update , , .
, – ShareLock, – ExclusiveLock. .
MVCC, , . , , . . SELECT UPDATE.
.
update , . , , , , . . . , . , .
, Lockdemo . . 698.
2-. 699 – . , .
– 2/51 – , . 3/112 – , 3. , , 699. 3/112 . Lock_mode , . 699. , 699, . ? ID. , Postgres . ID. , - , , . .
. ID. ID , lock_type. ? , , ID. , relation. . relation. transactionid, , , , transationid, , 699 .
, . . ID , . , .
6- , , . 699 . 700 . , , 699 .
lock_type, tuple .
, 0/10. , offset .
, 0/11, .
– 0/10, . , , , .
commit, , , . 700 – , , . , . 699 , . 700 , , , , .
, view, . , . , .
, . . .
, , . 3 4.
4. ID 702.
4 5. 5 6, 6 7. , , .
. ? 702. ID, . Granted? f. , (5, 6, 7) , , ID 702 . ID. 5 ID.
704, 705, , , . , , . , , - , .
, . , 12- .
, . 0/12.
, , , . . . .
, . 702 . 703 , 704 , 703 . 705 . , . , . , . , . , . .
, deadlocks. , . . deadlocks – , , - . , -.
, , : « -», : «, , - ». : «, , ». . , , , - , , . .
, , . , . , . , , .
deadlocks. 50 80. 50 50. 710.
80 81, 50 51.
, . 710 , 711 . , . 710 – . 711 , 710 .
deadlocks. .
80 80.
, deadlocks. 710 711, 711 710. . . .
. .
Postgres , . , . , - SHARE LOCK’ , . . 711 . , SHARE LOCK - ID - . .
deadlocks? ? .
. 40 40, .
60 61, 80 81.
80, – !
714 715. 716- 715- . .
, . - , - , - . , , , .
Postgres . , , , . . , 20 .
– serializable.
serializable .
719. .
, serializable.
, SA – serializable.
, SARieadLock, .
.
.
, 2, 2. 2. , 721- . 722 , 721 , 2 , , 721.
subtransaction.
723.
, ID. , . , ID . 724 . 725.
? , : serializable SAVEPOINT – , .
() , pg_advisory_lock.
, advisory. «advisory». pg_advisory_unlock.
. . pg_locks pg_stat_activity. ? , . , .
pg_stat_view.
. 724. . ? , . . , . , . , – . .
, – pg_blocking_pids. , , . ? , 11740, ID- . , 11740 724. 724 . 11306 ID-. , . , , . , , ID, , . , ID, , . pg_blocking_pids.
. 9.6, 5 , . . , .
, . , , . . , . , , !
:
, , . , - intent lock’. Postgres?
. , , , , SELECT, AccessShareLock. . , , , - , AccessShareLock . , . - , .
. . , lock .
lock exclusive, ?
, . , . , , SELECT, ShareExclusive, Row Exclusive, ? . , , , lock, . , , . , . . . , , , .
, . , , . , . . , !
, deadlock’, , ?
Postgres deadlock’. . , , . , , deadlocks… , . 1, 2, 1. deadlocks – , . , , 80 % , . , deadlock’.
! vacuum full , , vacuum full , . vacuum full ?
. , vacuum full . , , . . . , , , , , , . . , . , , , , lock exclusive.
, , , , . . . . lock exclusive.
locking timeout Postgres? Oracle , , « » 50 . . Postgres , - .
, - , locks. no way, …, . lock timeout, , . . , . .
75 ?
.
. 703?
. , , Postgres . 703 , 702. 704 705 , , , , . Postgres : , « ?», -. , . ? 702 703 , . , , . . , .
, Postgres . , f…. , . , 20 , . . . , 703. , . , , , 707, .
, , , 702 703, , , . , , , . , , , , .
, . , , . , , , lock, , . . , , , . . !
, , 705 704.
. , . . ? , 703 ID. , Postgres . 703 ID, , - , 703. , , 703 . - . , . . , . , , , . . , .
And the problem is that we have CP-infinity. And so it is quite possible that we can wake up the later one. And if, for example, we wake up the later one, we will expect the one who just got the lock, so we do not determine who exactly will be awakened first. We create just such a situation, and the system will wake them up in no particular order.
There are articles about locks by Yegor Rogov . Look, they are interesting and useful too. The topic, of course, is terribly complex. Thanks a lot Bruce!