Обсуждение: 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
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 andunlocks 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 ofthe process model that the client is using. If a session creates the advisory lock, that same session will need to bethe 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?
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
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:
> 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 scenariosFrom 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
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