PostgreSQL uses a high performance, high concurrency MVCC engine to manage mutiple sessions reading and writing to the database. All locks in PostgreSQL use this engine and must obey transaction sementics -- except for advisory locks. While advisory locks can be used for various tricks, their most important role is to provide a database managed mutex of precisely controlled duration irresepctive of what else is going on in the system at the time. The obvious use for this is for long term pessimistic locks, but how else might they be used?
One scenario is to manage contention to resources that also do not follow transactional semantics. One such resource is a sequence -- even if in a serializable transaction other sessions can access that resource and alter your expected results. For example, if you pulled 10 values from a sequence in a pl/pgsql FOR loop, there is no guarantee that those values are contiguous, except to lock a resource before the first value is fetched and hold that lock for the duration of the transaction. Advisory locks provide the tools to give that guarantee with a shorter term lock.
Imagine you had a pl/pgsql function that was long or was part of a transaction that was long and had the following requirements:
- the sequence s must be incremented
- after incrementing s, execute an operation with a possible (handled) failure condition. If that fails, return s to the value before incrementing
- until the failure state of the operation is checked, no other sessions must increment s
Our transaction time line is:
B=Begin, S=increment S, C=Commit, T=Standard SQL transaction lock
using the lock, we have a mutex guarding S to C.
With advisory locks, a better solution emerges: lock some number that represents s and unlock that number once the failure state is checked and handled. Now our transaction timeline is:
L=Advisory lock, U=Advisory unlock
Obviously, this lowers contention and improves concurrency. In certain rare cases, this can provide enormous benefits.
free Special website The Most Powerful website Marketing On The Web!
Excellent notion, and just the hint I needed for a problem I am having now. Thanks!
Post a Comment