Re: Transaction-scope advisory locks

On Fri, Jan 28, 2011 at 17:12, Marko Tiikkaja
<marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> wrote:
> I still didn't address
> the issue with pg_advisory_unlock_all() releasing transaction scoped locks,

I guess you don't want independent locks, right? If an user object
is locked by session locks, it also blocks backends trying to lock it
with transaction locks.

If so, I think an ideal behavior is below:
- The transaction-or-session property is overwritten by the last lock
function call. We can promote session locks from/to transaction locks.
- Shared and exclusive locks are managed independently.
We could have shared session lock and exclusive transaction
lock on the same resource in a transaction.
- Unlock functions releases both transaction and session locks.
- unlock_all() releases all both locks.

Those might be odd in DBMS-perspective, but would be natural as
programming languages. I guess advisory locks are often used in
standard programming like flows.

> Another issue I found while testing the behaviour here:
> http://archives.postgresql.org/pgsql-hackers/2011-01/msg01939.php
> is that if a session holds both a transaction level and a session level lock
> on the same resource, only one of them will appear in pg_locks.  Is that
> going to be a problem from the user's perspective?  Could it be an
> indication of a well-hidden bug?  Based on my tests it seems to work, but
> I'm not at all confident with the code.

In the above proposal, we won't have both session and transaction lock
on the same resource at the same time, though we still need to show
exclusive and shared locks in different lines.

--
Itagaki Takahiro