PostgreSQL has a very convenient mechanism for advisory locks , they are also advisory locks . We at Tensor use them in many places in the system, but few people understand in detail how exactly they work, and what problems can be obtained if mistreated .
Advantages of recommendation locks
The fundamental difference between this mechanism and “ordinary” table / page / record level locks is that there are several key features.Custom ID Lock
“Normal” locks in PG are always tied to a specific database object (table, record, data page) and the process serving the connection. Advisory locks is also a process, but instead of a real object, an abstract identifier can be set as (bigint) or as (integer, integer) .By the way, attaching each lock to a process means that by naming it through pg_terminate_backend(pid)or correctly completing the connection from the client side, you can get rid of all the locks imposed by it.CAS Check for Lock Capture
CAS is a Compare-and-Set , that is, the verification of the capture capability and the capture of the lock itself take place as one atomic operation, and obviously no one can “wedge” between them.That is, if you first make a verification request to pg_locks , look at the result, then decide whether or not to lock, then no one guarantees that between these operations no one will manage to take the object you need. But if you use pg_try_advisory_lock , then you will either immediately receive this lock, or the function will simply return FALSE.No-capture without exceptions and expectations
“Normal” locks exist in the model “If you already asked for a lock, then wait. If you did not want to wait ( NOWAIT, statement_timeout, lock_timeout) - here's an exception " . This approach greatly interferes within the transaction, because then you have to either implement a block BEGIN-EXCEPTION-ENDfor processing or roll back ( ROLLBACK) the transaction.The only way to avoid this behavior is to use the design SELECT ... SKIP LOCKEDthat came with version 9.5. Unfortunately, with this method, the options “didn’t have anything to block” and “was, but already blocked” become indistinguishable.Recommended locks caused by try functions simply return TRUE/FALSE.Do not confuse pg_advisory_lockand - the first function will wait until it receives a lock, and the second will simply return immediately if it is impossible to capture it "right now."pg_try_advisory_lockFALSE
Locks within and after the transaction
As I mentioned above, object locks are “attached” to the process and exist only as part of the current transaction in it. Even just to impose - will not succeed:LOCK TABLE tbl;
Accordingly, at the end of the transaction, all the locks imposed on it are removed. In contrast, advisory locks were originally designed with the ability to hold locks and outside the scope of a transaction :SELECT pg_advisory_lock(1);
SELECT * FROM pg_locks WHERE pid = pg_backend_pid() AND locktype = 'advisory';
-[ RECORD 1 ]------+--------------
locktype | advisory
database | 263911484
relation |
page |
tuple |
virtualxid |
transactionid |
classid | 0 <-- int4 #1 int8
objid | 1 <-- int4 #2 int8
objsubid | 1
virtualtransaction | 416/475768
pid | 29264
mode | ExclusiveLock
granted | t
fastpath | f
But already from version 9.1, xact versions of advisory functions have appeared that allow you to implement the behavior of “ordinary” locks that are automatically released when the transaction that imposed them is completed.Examples of use in VLSI
Actually, like any other lock, advisory serve to ensure the uniqueness of processing a resource. In our country, such resources are usually either the entire table or a specific record of the table, which for some reason does not want to be "locked down".Worker's monoprocessing
If the need to process some data in the database is triggered by an external event, but multiprocessing is redundant or can lead to a race condition , it is reasonable to make only one process work on this data at a time .To do this, we will try to lock with the table identifier as the first parameter and the specific application processing ID as the second:SELECT pg_try_advisory_lock(
'processed_table'::regclass::oid
, -1
);
If we returned FALSE, then someone else is already holding such a lock, and specifically this process does not need to do anything, but it’s best to just end quietly. This is how, for example, the process of dynamic calculation of the cost of goods in a warehouse works, in isolation for each individual client scheme.Concurrent Queue Processing
Now the task is “the opposite” - we want the tasks in some queue table to be processed as quickly as possible, multi-threaded, fault-tolerant, and even from different business logics (well, we lack the power of one) - for example, as our operator does on the transfer of electronic reporting to government agencies or the OFD service .Since the “processing” BLs are different servers, no mutex can be hung up anymore. It’s not safe to single out some special task-distributing process coordinator, “die” he - and everything will rise. And so it turns out that it is most efficient to distribute tasks directly at the database level, and there is such a way - in ancient times, the model was honestly spied on by Dmitry Koterov and then creatively modified.In this case, we impose a lock on the table ID and PK of a particular record:SELECT
*
FROM
queue_table
WHERE
pg_try_advisory_lock('queue_table'::regclass::oid, pk_id)
ORDER BY
pk_id
LIMIT 1;
That is, the process will receive from the table the first record that has not yet been blocked by its competing brothers.However, if PK does not consist of (integer) , but of (integer, integer) (as in the same cost calculation, for example), you can impose a lock directly on this pair - it is unlikely that an intersection with the "competitor" will occur.Important! Do not forget to periodically properly maintain your queue table !Exclusive Document Processing
It is used everywhere in document management solutions . Indeed, in a distributed web-system one and the same document can be opened for viewing by different users at the same time, but it can only be processed (change its state, etc.) at any time by just one.Traditional issues
Where without them! Almost all boils down to one thing : they did not unlock what they locked .Multi-overlay of one advisory lock
RTFM , as they say:If several requests for blocking are received at once, they accumulate, so if one resource has been blocked three times, it must be unlocked three times to be available in other sessions.
Put too many locks at once
Thousands of them! Read the manual again :Both advisory and regular locks are stored in the shared memory area, the size of which is determined by the configuration parameters max_locks_per_transactionand max_connections. It is important that this memory is sufficient, because otherwise the server will not be able to issue any lock . Thus, the number of recommended locks that a server can issue is usually limited to tens or hundreds of thousands, depending on the server configuration.
In general, if a situation arises when you want to impose several thousand advisory locks (even if you remove them correctly later) - think very hard where you will run when the server gets up.Leaks while filtering records
Here we take the previous request and add a harmless condition such as parity check ID - AND pk_id % 2 = 0. Both conditions for each entry will be checked ! As a result, it was pg_try_advisory_lockcompleted, the lock was superimposed, and then the record was filtered by parity.
Or an option from the manual:SELECT pg_advisory_lock(id) FROM foo WHERE id = 12345;
SELECT pg_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100;
That's all - the blocking remains , but we are not aware of this. It is treated with the correct requests, in the worst case - pg_advisory_unlock_all.Oh, confused!
Classics of the genre ...Confuse and , and wonder why it works for a long time. But because the non-try-version is waiting . Confuse and , and wonder where the lock went - and it "ended" with the transaction. And the transaction consisted of one request, because nowhere was it "explicitly" declared, yeah.pg_try_advisory_lockpg_advisory_lockpg_try_advisory_lockpg_try_advisory_xact_lockWork through pgbouncer
This is a separate source of pain for many when, for the sake of performance, working with the database goes through pgbouncer in transaction mode .This means that two of your neighboring transactions running on the same connection to the database (which actually goes through the pgbouncer) can be executed in different "physical" connections on the base side. And they have their own locks ... Each has
a few options:- or go to work through a direct connection to the database
- or invent an algorithm so that all advisory locks are only within the transaction (xact)
That's all for now.