Clarification regarding managing advisory locks in postgresql
Hi Postgresql Team,
Need your support on understanding advisory locks in Postgresql and what is
the best practice to have advisory locks and unlocks to work properly when
we have multiple process forked from single process? is there any option
for setting the locktype or please share a sample program using libpq in C
explaining the above scenario if possible
Regards,
Sasmit Utkarsh
+91-7674022625
On Jan 24, 2024, at 19:17, Sasmit Utkarsh <utkarshsasmit@gmail.com> wrote:
Need your support on understanding advisory locks in Postgresql and what is the best practice to have advisory locks and unlocks to work properly when we have multiple process forked from single process?
Advisory locks are a shared resource across all of the database in which they were created, and PostgreSQL isn't aware of the process model that the client is using. If a session creates the advisory lock, that same session will need to be the one to release it, and it's up to the client program to keep track of which process is using which session.
Okay Thanks. Also please help me understand the below scenarios
From the above statement, I understand is (please correct if I'm wrong
here), When we fork a client process, each process gets its own database
connection or transaction context. Therefore, locks acquired in one process
(or transaction) do not directly affect locks in another process (or
transaction).
Now, I'm faced with another situation where I'm using libpq in C as client
programs and while calling some function it acquires pg_advisory_lock for
the request with some identifier in transaction A. This can be thought of
as “lock the operation with id = X” and then make some SQL
requests(retrieve) from the database. During that if it forks into another
process B, If any other transaction, e.g. B, will attempt to acquire lock
with the same id, then do some update operation on DB for the id=X then
will it wait until such lock will be released by A?
what would be status of the lock in process A and B? which process will
have the lock?
If they are independent of each other then how can we maintain
synchronization between the processes to access the locks properly, when
they are forked
Please let me know if you need any more details or clarfications
Regards,
Sasmit Utkarsh
+91-7674022625
On Thu, Jan 25, 2024 at 10:42 AM Christophe Pettus <xof@thebuild.com> wrote:
Show quoted text
On Jan 24, 2024, at 19:17, Sasmit Utkarsh <utkarshsasmit@gmail.com>
wrote:
Need your support on understanding advisory locks in Postgresql and what
is the best practice to have advisory locks and unlocks to work properly
when we have multiple process forked from single process?Advisory locks are a shared resource across all of the database in which
they were created, and PostgreSQL isn't aware of the process model that the
client is using. If a session creates the advisory lock, that same session
will need to be the one to release it, and it's up to the client program to
keep track of which process is using which session.
On Thu, Jan 25, 2024 at 4:44 AM Sasmit Utkarsh <utkarshsasmit@gmail.com>
wrote:
Okay Thanks. Also please help me understand the below scenarios
From the above statement, I understand is (please correct if I'm wrong
here), When we fork a client process, each process gets its own database
connection or transaction context.
So far so good
Therefore, locks acquired in one process (or transaction) do not directly
affect locks in another process (or transaction).
Not following you here. By definition, a lock impacts other processes;
that's the entire purpose. The affect other processes in that two
processes cannot take a lock on the same thing at the same time.
Now, I'm faced with another situation where I'm using libpq in C as client
programs and while calling some function it acquires pg_advisory_lock for
the request with some identifier in transaction A. This can be thought
of as “lock the operation with id = X” and then make some SQL
requests(retrieve) from the database. During that if it forks into another
process B,
Client side code should not fork and preserve connections across the fork.
This is multi-threaded access to a connection, and generally speaking you
should not have 2+ threads hitting the same connection returned from
libpq. This is undefined behavior, so that your questions below this I
suspect are moot.
merlin
Show quoted text
On Thu, Jan 25, 2024 at 5:44 AM Sasmit Utkarsh <utkarshsasmit@gmail.com>
wrote:
Therefore, locks acquired in one process (or transaction) do not directly
affect locks in another process (or transaction).
Um....maybe if you describe the exact problem you are trying to solve with
advisory locks, we can find the best solution. It may not even involve
advisory locks.
Cheers,
Greg