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@xxxxxxxxxxxx> wrote:
> On Jan 24, 2024, at 19:17, Sasmit Utkarsh <utkarshsasmit@xxxxxxxxx> 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.
