Thread: pg_advisory_lock(bigint) vs. LOCK TABLE

Hi,

What's the difference between below two queue implementations?
 -- -- With advisory locks. -- BEGIN; SELECT pg_advisory_lock((SELECT oid                            FROM pg_class
                    WHERE relname = 'queue')); DELETE FROM queue       WHERE id = (SELECT MIN(id) FROM queue)
RETURNINGid, val; SELECT pg_advisory_unlock((SELECT oid                              FROM pg_class
      WHERE relname = 'queue')); COMMIT;  -- -- With LOCK table command. -- BEGIN; LOCK TABLE queue; DELETE FROM queue
    WHERE id = (SELECT MIN(id) FROM queue)   RETURNING id, val; COMMIT;
 

Any helps will be appreciated. (BTW, yep, I'm aware of PGQ module of
skyytools[1].)


Regards.

[1] http://skytools.projects.postgresql.org/

Volkan YAZICI wrote:
> Hi,
> 
> What's the difference between below two queue implementations?

They are two different lock spaces.  pg_advisory_lock does not conflict
with regular system locks, whereas LOCK TABLE does.


-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Thu, 17 Jul 2008, Alvaro Herrera <alvherre@commandprompt.com> writes:
> Volkan YAZICI wrote:
>> What's the difference between below two queue implementations?
>
> They are two different lock spaces.  pg_advisory_lock does not conflict
> with regular system locks, whereas LOCK TABLE does.

Is this documented in somewhere? I couldn't figure out that from the
documentation.


Regards.

Volkan YAZICI <yazicivo@ttmail.com> writes:
> On Thu, 17 Jul 2008, Alvaro Herrera <alvherre@commandprompt.com> writes:
>> They are two different lock spaces.  pg_advisory_lock does not conflict
>> with regular system locks, whereas LOCK TABLE does.

> Is this documented in somewhere? I couldn't figure out that from the
> documentation.

Advisory locks are defined as locking application-defined identifiers.
Why would you expect them to conflict with system locks, and what would
be the relationship exactly?
        regards, tom lane

On Thu, 2008-07-17 at 12:16 -0400, Alvaro Herrera wrote:
> Volkan YAZICI wrote:
> > Hi,
> > 
> > What's the difference between below two queue implementations?
> 
> They are two different lock spaces.  pg_advisory_lock does not conflict
> with regular system locks, whereas LOCK TABLE does.
> 
> 
> -- 
> Alvaro Herrera                                http://www.CommandPrompt.com/
> The PostgreSQL Company - Command Prompt, Inc.
> 

It also appears that advisory locks are tied to your session, where
system locks are tied to your transaction.  Also, lock table is a bit
more forceful, because it will affect things that don't bother checking
advisory locks (such as users, manual scripts, buggy applications, etc).

Don't forget that you can use select for update another locking
mechanism as well.

Well, that's my take on it.

-Mark

Mark Roberts wrote:
> 
> On Thu, 2008-07-17 at 12:16 -0400, Alvaro Herrera wrote:
> > Volkan YAZICI wrote:
> > > Hi,
> > > 
> > > What's the difference between below two queue implementations?
> > 
> > They are two different lock spaces.  pg_advisory_lock does not conflict
> > with regular system locks, whereas LOCK TABLE does.
> 
> It also appears that advisory locks are tied to your session, where
> system locks are tied to your transaction.

Now that you mention that, I recall that the other important difference
is that you can release pg_advisory_lock anytime you want.  With LOCK
TABLE it always happens automatically at transaction commit (and there's
no UNLOCK TABLE).

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