Thread: Advisory transaction lock for 128-bit space

Hello.
My application need to set advisory lock on UUID key, almost like it
does pg_advisory_xact_lock function. The problem is argument type of
this function - it consumes 8-byte value, not 16-byte.

I can not lock on any(hi, low or middle) 8-byte part of UUID, as far
as it can produce unexpected deadlock issues, because locking on some
ID in this way will imply locking on more "wide" set of ID then I
requested.

Now I am doing the 'trick' using indexing insert/delete, e.g.:
INSERT INTO table_with_uuid_pk(locking_value);
DELETE FROM table_with_uuid_pk WHERE <inserted_row_above>;

It works, but I did not found any description of such 'feature' of
indexes. Can u, please, help to solve this synchronization issue, and
comment the way I am dealing with it now(with index locking)

P.S. The most significant fear I know have, is that currently used
method suffers with same problem as locking for part of UUID - doest
insert/delete really locks only on the value i passed to it?

--
Regards,
Andrey

In response to Andrey Chursin <andll@danasoft.ws>:

> Hello.
> My application need to set advisory lock on UUID key, almost like it
> does pg_advisory_xact_lock function. The problem is argument type of
> this function - it consumes 8-byte value, not 16-byte.
>
> I can not lock on any(hi, low or middle) 8-byte part of UUID, as far
> as it can produce unexpected deadlock issues, because locking on some
> ID in this way will imply locking on more "wide" set of ID then I
> requested.
>
> Now I am doing the 'trick' using indexing insert/delete, e.g.:
> INSERT INTO table_with_uuid_pk(locking_value);
> DELETE FROM table_with_uuid_pk WHERE <inserted_row_above>;
>
> It works, but I did not found any description of such 'feature' of
> indexes. Can u, please, help to solve this synchronization issue, and
> comment the way I am dealing with it now(with index locking)
>
> P.S. The most significant fear I know have, is that currently used
> method suffers with same problem as locking for part of UUID - doest
> insert/delete really locks only on the value i passed to it?

Have you considered using row locking?  If you need the lock to
extend across multiple transactions, then row locking won't work.

The problem with what you're doing is that if a process crashes, or
is otherwise uncleanly disconnected, the lock table is polluted.
My recommendation would be to add another column to the table with
the UUID key that keeps a unique 8 byte number that you can use
the advisory lock on.  You can use a sequence to automatically
generate unique values for it, and as long as you don't exceed
2 billion rows, you'll be fine.  Of course, if you expect that
you might exceed 2 billion rows, that won't work either.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

On Thu, Mar 8, 2012 at 2:05 AM, Kiriakos Georgiou
<kg.postgresql@olympiakos.com> wrote:
> Indeed, if there is not some sort of implementation limitation, it would be
> cool to be able to lock two big integers like so:
>
>     pg_try_advisory_xact_lock(key1 bigint, key2 bigint)

Well, this would require expanding the structure that holds the
in-memory lock.  This is not free, since it's also used by the
database for internal lock tables.  I would advise trying to work
under the constraints of the current system.

If you want a database-wide advisory lock for rows, probably the best
bet is to make a sequence that is shared by all tables that want to
participate in advisory locking.  This is simple and works very well
regardless on how your keys are defined (uuid, natural, etc).  It's a
good use for a domain:

create sequence lockid_seq;
create domain lockid_t bigint default nextval('lockid_seq');
alter table foo add column lockid lockid_t;

etc.  You'll never exhaust a 64 bit sequence.  In fact, you can
reserve a few bits off the top in case you want to do some other
advisory locking for different reasons.  A bit hacky maybe, but it
works quite well.

merlin