1-- 2-- ADVISORY LOCKS 3-- 4BEGIN; 5SELECT 6 pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), 7 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); 8 pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared 9-----------------------+------------------------------+-----------------------+------------------------------ 10 | | | 11(1 row) 12 13SELECT locktype, classid, objid, objsubid, mode, granted 14 FROM pg_locks WHERE locktype = 'advisory' 15 ORDER BY classid, objid, objsubid; 16 locktype | classid | objid | objsubid | mode | granted 17----------+---------+-------+----------+---------------+--------- 18 advisory | 0 | 1 | 1 | ExclusiveLock | t 19 advisory | 0 | 2 | 1 | ShareLock | t 20 advisory | 1 | 1 | 2 | ExclusiveLock | t 21 advisory | 2 | 2 | 2 | ShareLock | t 22(4 rows) 23 24-- pg_advisory_unlock_all() shouldn't release xact locks 25SELECT pg_advisory_unlock_all(); 26 pg_advisory_unlock_all 27------------------------ 28 29(1 row) 30 31SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 32 count 33------- 34 4 35(1 row) 36 37-- can't unlock xact locks 38SELECT 39 pg_advisory_unlock(1), pg_advisory_unlock_shared(2), 40 pg_advisory_unlock(1, 1), pg_advisory_unlock_shared(2, 2); 41WARNING: you don't own a lock of type ExclusiveLock 42WARNING: you don't own a lock of type ShareLock 43WARNING: you don't own a lock of type ExclusiveLock 44WARNING: you don't own a lock of type ShareLock 45 pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock_shared 46--------------------+---------------------------+--------------------+--------------------------- 47 f | f | f | f 48(1 row) 49 50-- automatically release xact locks at commit 51COMMIT; 52SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 53 count 54------- 55 0 56(1 row) 57 58BEGIN; 59-- holding both session and xact locks on the same objects, xact first 60SELECT 61 pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), 62 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); 63 pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared 64-----------------------+------------------------------+-----------------------+------------------------------ 65 | | | 66(1 row) 67 68SELECT locktype, classid, objid, objsubid, mode, granted 69 FROM pg_locks WHERE locktype = 'advisory' 70 ORDER BY classid, objid, objsubid; 71 locktype | classid | objid | objsubid | mode | granted 72----------+---------+-------+----------+---------------+--------- 73 advisory | 0 | 1 | 1 | ExclusiveLock | t 74 advisory | 0 | 2 | 1 | ShareLock | t 75 advisory | 1 | 1 | 2 | ExclusiveLock | t 76 advisory | 2 | 2 | 2 | ShareLock | t 77(4 rows) 78 79SELECT 80 pg_advisory_lock(1), pg_advisory_lock_shared(2), 81 pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2); 82 pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared 83------------------+-------------------------+------------------+------------------------- 84 | | | 85(1 row) 86 87ROLLBACK; 88SELECT locktype, classid, objid, objsubid, mode, granted 89 FROM pg_locks WHERE locktype = 'advisory' 90 ORDER BY classid, objid, objsubid; 91 locktype | classid | objid | objsubid | mode | granted 92----------+---------+-------+----------+---------------+--------- 93 advisory | 0 | 1 | 1 | ExclusiveLock | t 94 advisory | 0 | 2 | 1 | ShareLock | t 95 advisory | 1 | 1 | 2 | ExclusiveLock | t 96 advisory | 2 | 2 | 2 | ShareLock | t 97(4 rows) 98 99-- unlocking session locks 100SELECT 101 pg_advisory_unlock(1), pg_advisory_unlock(1), 102 pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2), 103 pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1), 104 pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2); 105WARNING: you don't own a lock of type ExclusiveLock 106WARNING: you don't own a lock of type ShareLock 107WARNING: you don't own a lock of type ExclusiveLock 108WARNING: you don't own a lock of type ShareLock 109 pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared 110--------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+--------------------------- 111 t | f | t | f | t | f | t | f 112(1 row) 113 114SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 115 count 116------- 117 0 118(1 row) 119 120BEGIN; 121-- holding both session and xact locks on the same objects, session first 122SELECT 123 pg_advisory_lock(1), pg_advisory_lock_shared(2), 124 pg_advisory_lock(1, 1), pg_advisory_lock_shared(2, 2); 125 pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock_shared 126------------------+-------------------------+------------------+------------------------- 127 | | | 128(1 row) 129 130SELECT locktype, classid, objid, objsubid, mode, granted 131 FROM pg_locks WHERE locktype = 'advisory' 132 ORDER BY classid, objid, objsubid; 133 locktype | classid | objid | objsubid | mode | granted 134----------+---------+-------+----------+---------------+--------- 135 advisory | 0 | 1 | 1 | ExclusiveLock | t 136 advisory | 0 | 2 | 1 | ShareLock | t 137 advisory | 1 | 1 | 2 | ExclusiveLock | t 138 advisory | 2 | 2 | 2 | ShareLock | t 139(4 rows) 140 141SELECT 142 pg_advisory_xact_lock(1), pg_advisory_xact_lock_shared(2), 143 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock_shared(2, 2); 144 pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock_shared 145-----------------------+------------------------------+-----------------------+------------------------------ 146 | | | 147(1 row) 148 149ROLLBACK; 150SELECT locktype, classid, objid, objsubid, mode, granted 151 FROM pg_locks WHERE locktype = 'advisory' 152 ORDER BY classid, objid, objsubid; 153 locktype | classid | objid | objsubid | mode | granted 154----------+---------+-------+----------+---------------+--------- 155 advisory | 0 | 1 | 1 | ExclusiveLock | t 156 advisory | 0 | 2 | 1 | ShareLock | t 157 advisory | 1 | 1 | 2 | ExclusiveLock | t 158 advisory | 2 | 2 | 2 | ShareLock | t 159(4 rows) 160 161-- releasing all session locks 162SELECT pg_advisory_unlock_all(); 163 pg_advisory_unlock_all 164------------------------ 165 166(1 row) 167 168SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 169 count 170------- 171 0 172(1 row) 173 174BEGIN; 175-- grabbing txn locks multiple times 176SELECT 177 pg_advisory_xact_lock(1), pg_advisory_xact_lock(1), 178 pg_advisory_xact_lock_shared(2), pg_advisory_xact_lock_shared(2), 179 pg_advisory_xact_lock(1, 1), pg_advisory_xact_lock(1, 1), 180 pg_advisory_xact_lock_shared(2, 2), pg_advisory_xact_lock_shared(2, 2); 181 pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared | pg_advisory_xact_lock | pg_advisory_xact_lock | pg_advisory_xact_lock_shared | pg_advisory_xact_lock_shared 182-----------------------+-----------------------+------------------------------+------------------------------+-----------------------+-----------------------+------------------------------+------------------------------ 183 | | | | | | | 184(1 row) 185 186SELECT locktype, classid, objid, objsubid, mode, granted 187 FROM pg_locks WHERE locktype = 'advisory' 188 ORDER BY classid, objid, objsubid; 189 locktype | classid | objid | objsubid | mode | granted 190----------+---------+-------+----------+---------------+--------- 191 advisory | 0 | 1 | 1 | ExclusiveLock | t 192 advisory | 0 | 2 | 1 | ShareLock | t 193 advisory | 1 | 1 | 2 | ExclusiveLock | t 194 advisory | 2 | 2 | 2 | ShareLock | t 195(4 rows) 196 197COMMIT; 198SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 199 count 200------- 201 0 202(1 row) 203 204-- grabbing session locks multiple times 205SELECT 206 pg_advisory_lock(1), pg_advisory_lock(1), 207 pg_advisory_lock_shared(2), pg_advisory_lock_shared(2), 208 pg_advisory_lock(1, 1), pg_advisory_lock(1, 1), 209 pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2); 210 pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared 211------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+------------------------- 212 | | | | | | | 213(1 row) 214 215SELECT locktype, classid, objid, objsubid, mode, granted 216 FROM pg_locks WHERE locktype = 'advisory' 217 ORDER BY classid, objid, objsubid; 218 locktype | classid | objid | objsubid | mode | granted 219----------+---------+-------+----------+---------------+--------- 220 advisory | 0 | 1 | 1 | ExclusiveLock | t 221 advisory | 0 | 2 | 1 | ShareLock | t 222 advisory | 1 | 1 | 2 | ExclusiveLock | t 223 advisory | 2 | 2 | 2 | ShareLock | t 224(4 rows) 225 226SELECT 227 pg_advisory_unlock(1), pg_advisory_unlock(1), 228 pg_advisory_unlock_shared(2), pg_advisory_unlock_shared(2), 229 pg_advisory_unlock(1, 1), pg_advisory_unlock(1, 1), 230 pg_advisory_unlock_shared(2, 2), pg_advisory_unlock_shared(2, 2); 231 pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared | pg_advisory_unlock | pg_advisory_unlock | pg_advisory_unlock_shared | pg_advisory_unlock_shared 232--------------------+--------------------+---------------------------+---------------------------+--------------------+--------------------+---------------------------+--------------------------- 233 t | t | t | t | t | t | t | t 234(1 row) 235 236SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 237 count 238------- 239 0 240(1 row) 241 242-- .. and releasing them all at once 243SELECT 244 pg_advisory_lock(1), pg_advisory_lock(1), 245 pg_advisory_lock_shared(2), pg_advisory_lock_shared(2), 246 pg_advisory_lock(1, 1), pg_advisory_lock(1, 1), 247 pg_advisory_lock_shared(2, 2), pg_advisory_lock_shared(2, 2); 248 pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared | pg_advisory_lock | pg_advisory_lock | pg_advisory_lock_shared | pg_advisory_lock_shared 249------------------+------------------+-------------------------+-------------------------+------------------+------------------+-------------------------+------------------------- 250 | | | | | | | 251(1 row) 252 253SELECT locktype, classid, objid, objsubid, mode, granted 254 FROM pg_locks WHERE locktype = 'advisory' 255 ORDER BY classid, objid, objsubid; 256 locktype | classid | objid | objsubid | mode | granted 257----------+---------+-------+----------+---------------+--------- 258 advisory | 0 | 1 | 1 | ExclusiveLock | t 259 advisory | 0 | 2 | 1 | ShareLock | t 260 advisory | 1 | 1 | 2 | ExclusiveLock | t 261 advisory | 2 | 2 | 2 | ShareLock | t 262(4 rows) 263 264SELECT pg_advisory_unlock_all(); 265 pg_advisory_unlock_all 266------------------------ 267 268(1 row) 269 270SELECT count(*) FROM pg_locks WHERE locktype = 'advisory'; 271 count 272------- 273 0 274(1 row) 275 276