Thread: BUG #5036: Advisory locks have unexpected behavior

The following bug has been logged online:

Bug reference:      5036
Logged by:          Dennis Seran
Email address:      dseran@novonics.com
PostgreSQL version: 8.4
Operating system:   Windows XP and RHEL
Description:        Advisory locks have unexpected behavior
Details:

Here is the scenario that was played out step by step:
- Everything is running locally on an XP machine.
- I have 3 client sessions open on the same machine and logged in the same
dB and at this point no exclusive or shared locks are owned.

- Client A prompts the command pg_try_advisory_lock_shared(12345) and
returns true and obtains shared lock(12345)

- Client B prompts the command pg_try_advisory_lock_shared(12345) and
returns true and obtains shared lock(12345)

- Client B prompts the command pg_advisory_unlock_shared(12345) and returns
true thus releasing shared lock(12345)

- Client B prompts the command pg_try_advisory_lock_shared(12345) and
returns true thus REOBTAINING shared lock(12345)

- Client B prompts the command pg_advisory_unlock_shared(12345) and returns
true thus RERELEASING shared lock(12345)

- Client C prompts the command pg_advisory_lock(12345) and enters the queue
to wait since Client A still holds the shared lock(12345)

- Client B again prompts the command pg_try_advisory_lock_shared(12345) in
an attempt to reobtain the shared lock(12345) but returns false and fails to
obtain the shared lock (SHOULDN'T THIS CLIENT BE ABLE TO OBTAIN THE SHARED
LOCK?)

- The above result happens when all 3 clients are on the same machine.  If
the same steps were followed, but this time with clients A and B on a RHEL
machine and the client C and the server on an XP machine, the result is a
bit different.  The above step results in Client B going into the queue as
well as Client C even though Client A currently holds the shared lock.
(AGAIN, SHOULDN'T THIS CLIENT BE ABLE TO OBTAIN THE SHARED LOCK?)

Dennis Seran wrote:

> - Client B again prompts the command pg_try_advisory_lock_shared(12345) in
> an attempt to reobtain the shared lock(12345) but returns false and fails to
> obtain the shared lock (SHOULDN'T THIS CLIENT BE ABLE TO OBTAIN THE SHARED
> LOCK?)

No; it gets to sleep until after client C has released the exclusive
lock.  Otherwise a constant stream of shared lockers would starvate
exclusive lockers.

> - The above result happens when all 3 clients are on the same machine.  If
> the same steps were followed, but this time with clients A and B on a RHEL
> machine and the client C and the server on an XP machine, the result is a
> bit different.  The above step results in Client B going into the queue as
> well as Client C even though Client A currently holds the shared lock.
> (AGAIN, SHOULDN'T THIS CLIENT BE ABLE TO OBTAIN THE SHARED LOCK?)

I think this sounds like a bug.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Dennis Seran wrote:
>> - The above result happens when all 3 clients are on the same machine.  If
>> the same steps were followed, but this time with clients A and B on a RHEL
>> machine and the client C and the server on an XP machine, the result is a
>> bit different.  The above step results in Client B going into the queue as
>> well as Client C even though Client A currently holds the shared lock.
>> (AGAIN, SHOULDN'T THIS CLIENT BE ABLE TO OBTAIN THE SHARED LOCK?)

> I think this sounds like a bug.

It's really, really, really hard to believe that the behavior of
advisory locks would vary depending on where the client is located.

What I think is more likely is that there was some pilot error involved,
such as entering "pg_try_advisory_lock(12345)" instead of
"pg_try_advisory_lock_shared(12345)".  Another line of thought is that
the client code being used on the RHEL machine might not be the same as
what is on the XP machine, and is issuing slightly different commands.

            regards, tom lane

Tom,

I ran the test scenario again and it appears that you are right.  The
first test most likely was executed with the command
"pg_try_advisory_lock(12345)" instead of
"pg_try_advisory_lock_shared(12345)" as you stated.  When I ran the
scenario again using "pg_try_advisory_lock_shared(12345)", the result was
the same as when it was run on the same machine, which was a "false"
return.

Thanks for your help and clarifying this for us.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Saturday, September 05, 2009 6:02 PM
To: Alvaro Herrera
Cc: Dennis Seran; pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #5036: Advisory locks have unexpected behavior

Alvaro Herrera <alvherre@commandprompt.com> writes:
> Dennis Seran wrote:
>> - The above result happens when all 3 clients are on the same machine.
If
>> the same steps were followed, but this time with clients A and B on a
RHEL
>> machine and the client C and the server on an XP machine, the result is
a
>> bit different.  The above step results in Client B going into the queue
as
>> well as Client C even though Client A currently holds the shared lock.
>> (AGAIN, SHOULDN'T THIS CLIENT BE ABLE TO OBTAIN THE SHARED LOCK?)

> I think this sounds like a bug.

It's really, really, really hard to believe that the behavior of
advisory locks would vary depending on where the client is located.

What I think is more likely is that there was some pilot error involved,
such as entering "pg_try_advisory_lock(12345)" instead of
"pg_try_advisory_lock_shared(12345)".  Another line of thought is that
the client code being used on the RHEL machine might not be the same as
what is on the XP machine, and is issuing slightly different commands.

            regards, tom lane