On 18th of February, Itagaki Takahiro committed patch:
Add transaction-level advisory locks. They share the same locking namespace with the existing session-level advisory locks, but they are automatically released at the end of the current transaction and cannot be released explicitly via unlock functions. Marko Tiikkaja, reviewed by me.
Ever since their introduction in 8.2, advisory locks tend to be somehow below the radar for many DBAs and DB programmers. Not really sure why – they provide really cool functionality, that is not-so-trivial to replicate using another means. And they're fast.
In case you're not familiar with advisory locks:
These locks do not lock database objects. They are obtained on numbers.
You can use either single range of INT8, or second range, that is split it into 432 ranges, each being INT4 in range.
All these numbers, can be locked in exclusive or shared mode, unlocked, and the locking can be tried (i.e. it will finish immediately if the lock is not obtainable).
The greatest thing about them was that they were not transactional in nature, so they could span multiple transactions without any problems.
This benefit had also problem – you had to remove the lock on your own, as simple “rollback" would not release it!
Since it didn't fit all possible cases where fast locking is needed, we got this new patch which adds transactional advisory locks.
To all previously available advisory lock methods we got couple of new functions:
- pg_advisory_xact_lock(key bigint)
- pg_advisory_xact_lock(key1 int, key2 int)
- pg_advisory_xact_lock_shared(key bigint)
- pg_advisory_xact_lock_shared(key1 int, key2 int)
- pg_try_advisory_xact_lock(key bigint)
- pg_try_advisory_xact_lock(key1 int, key2 int)
- pg_try_advisory_xact_lock_shared(key bigint)
- pg_try_advisory_xact_lock_shared(key1 int, key2 int)
As I mentioned – versions on (int8) or (int4, int4) represent the same logic, just the way range of locked number is provided is different.
Previously if you (for example) used advisory lock in your function, you had to add logic that will release the lock in case of exception.
Now, it happens automatically:
| Session 1 | Session 2 |
|---|---|
| begin; | |
| select pg_advisory_xact_lock(1); | |
| … | begin; |
| … | select pg_advisory_xact_lock(1); |
| … | waits for lock |
| exception happens, and implicit rollback | |
| wait for lock is over, lock is obtained. |
Of course, if you need locking that spans on multiple transactions you can still use old-fashioned advisory locks.
Given limited scope, one can ask – what is the reason to use advisory locks, and not normal “select for update".
Reason is simple – advisory locks are faster. And sometimes it's not really trivial to decide what to lock, and in which order.
you usually show us proof that “X is faster” instead of just saying so, and that is great for newbies like me.
so I’ll ask: how much faster is an advisory lock than a select for update?
@Anonymous:
It’s interesting question. For small number of rows the difference will be hardly visible. Tested table with 1000 rows, 1 column (integer from 1 to 1000), and unique index on it).
Running 1000 select for update statements on it, every time getting lock on single row took (on average) 170ms.
Running 1000 select pg_advisory_xact_lock(integer) took on average 118ms.
But the real power is that running select for update requires some kind of table scan (hopefully index scan), which means it’s complexity is ~ log(n) – read: the more rows you have in table, the more expensive is to lock any of them.
With advisory locks, cost is (as far as I know) constant, so 1000 locks will take the same time regardless of whether you’ll take them from 1-1000, or 100000000-100000999.
@Anonymous:
What I personally think are the two biggest advantages that advisory locks have over other types of locks in PG:
1) They’re not tied to any database objects. This gives the speed depesz was talking in the comment above. It is also noteworthy that you don’t even have to store any rows to lock the ranges.
2) You can do some clever tricks with the “try” variants of the lock functions. I’ve blogged about a couple of those tricks previously.
thank you both!
Just yesterday I was thinking of adding the transaction-controlled variants of these locks, and today I see it’s been already done 🙂
Am I glad to be using Postgres? You bet…