Did it help? If yes - maybe you can help me?
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
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|
|…||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.