1-- 2-- ADVISORY LOCKS 3-- 4 5BEGIN; 6 7SELECT 8 pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), 9 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); 10 11SELECT locktype, classid, objid, objsubid, mode, granted 12 FROM pg_locks WHERE locktype = 'advisory' 13 ORDER BY classid, objid, objsubid; 14 15 16-- pg_advisory_unlock_all() shouldn't release xact locks 17SELECT pg_advisory_unlock_all(); 18 19SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 20 21 22-- can't unlock xact locks 23SELECT 24 pg_advisory_unlock(1), pg_advisory_unlock_shared(2), 25 pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2); 26 27 28-- automatically release xact locks at commit 29COMMIT; 30 31SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 32 33 34BEGIN; 35 36-- holding both session and xact locks on the same objects, xact first 37SELECT 38 pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), 39 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); 40 41SELECT locktype, classid, objid, objsubid, mode, granted 42 FROM pg_locks WHERE locktype = 'advisory' 43 ORDER BY classid, objid, objsubid; 44 45SELECT 46 pg_advisory_lock(1), pg_advisory_lock_shared(2), 47 pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2); 48 49ROLLBACK; 50 51SELECT locktype, classid, objid, objsubid, mode, granted 52 FROM pg_locks WHERE locktype = 'advisory' 53 ORDER BY classid, objid, objsubid; 54 55 56-- unlocking session locks 57SELECT 58 pg_advisory_unlock(1), pg_advisory_unlock(1), 59 pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2), 60 pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1), 61 pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2); 62 63SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 64 65 66BEGIN; 67 68-- holding both session and xact locks on the same objects, session first 69SELECT 70 pg_advisory_lock(1), pg_advisory_lock_shared(2), 71 pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2); 72 73SELECT locktype, classid, objid, objsubid, mode, granted 74 FROM pg_locks WHERE locktype = 'advisory' 75 ORDER BY classid, objid, objsubid; 76 77SELECT 78 pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), 79 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); 80 81ROLLBACK; 82 83SELECT locktype, classid, objid, objsubid, mode, granted 84 FROM pg_locks WHERE locktype = 'advisory' 85 ORDER BY classid, objid, objsubid; 86 87 88-- releasing all session locks 89SELECT pg_advisory_unlock_all(); 90 91SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 92 93 94BEGIN; 95 96-- grabbing txn locks multiple times 97 98SELECT 99 pg_advisory_xact_lock(1), pg_advisory_xact_lock(1), 100 pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2), 101 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1), 102 pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2); 103 104SELECT locktype, classid, objid, objsubid, mode, granted 105 FROM pg_locks WHERE locktype = 'advisory' 106 ORDER BY classid, objid, objsubid; 107 108COMMIT; 109 110SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 111 112-- grabbing session locks multiple times 113 114SELECT 115 pg_advisory_lock(1), pg_advisory_lock(1), 116 pg_advisory_lock_shared(2), pg_advisory_lock_shared(2), 117 pg_advisory_lock(1, 1), pg_advisory_lock(1, 1), 118 pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2); 119 120SELECT locktype, classid, objid, objsubid, mode, granted 121 FROM pg_locks WHERE locktype = 'advisory' 122 ORDER BY classid, objid, objsubid; 123 124SELECT 125 pg_advisory_unlock(1), pg_advisory_unlock(1), 126 pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2), 127 pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1), 128 pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2); 129 130SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 131 132-- .. and releasing them all at once 133 134SELECT 135 pg_advisory_lock(1), pg_advisory_lock(1), 136 pg_advisory_lock_shared(2), pg_advisory_lock_shared(2), 137 pg_advisory_lock(1, 1), pg_advisory_lock(1, 1), 138 pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2); 139 140SELECT locktype, classid, objid, objsubid, mode, granted 141 FROM pg_locks WHERE locktype = 'advisory' 142 ORDER BY classid, objid, objsubid; 143 144SELECT pg_advisory_unlock_all(); 145 146SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 147