[DocDB] [Advisory Lock] Unexpected Deadlock Error And Wait Despite Lock Ownership

[shishir2001-yb]

Jira Link: DB-15449

Description

Version: 2.25.1.0-b324

When using pg_advisory_lock() and pg_advisory_xact_lock() it appears that retries are exhausted, causing a deadlock error even when no deadlock occurs. If a session already holds a lock on a key, executing pg_advisory_xact_lock() within the same session should acquire the lock immediately, regardless of other sessions waiting for the same key. However, the query continues to wait and eventually fails with a deadlock error.

Session 1                                                     Session 2


SELECT pg_advisory_lock(1);                                  
                                                              begin;
                                                              SELECT pg_advisory_xact_lock(1);
                                                              <<<<<<<<Waits for session 1 to release the lock>>>>>>

begin;
SELECT pg_advisory_xact_lock(1);

<Continues to wait even when session1 has this lock>            ERROR:  deadlock detected (yb_max_query_layer_retries set to 60 are exhausted)
                                                               DETAIL:  Transaction bb5a32f9-e713-4966-a320-87894726c72e aborted due to a 
                                                               deadlock[ session level txn]06593c69-4572-44c1-8c09-f38905c3404d->
                                                               <1739995808906932>bb5a32f9-e713-4966-a320-87894726c72e->: kDeadlock

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.

[rthallamko3]

Removing the blocker label as it is a duplicate of #26006.