Re: Advisory transaction lock for 128-bit space

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)

That would solve your problem with locking UUIDs (although you still couldn't lock UUIDs simultaneously across different tables without risking lock interference.) It would also enable the use of advisory locks on multiple tables that have bigserial (bigint) as the primary key, eg:

pg_try_advisory_xact_lock(t.id, t.tableoid::bigint)

Obviously you don't need a bigint for tableoid but being able to lock two bigints allows you to go to 16-bytes if need be.

This came up when I was thinking about how to implement processing queues. It's doable if you assign an int4 id for each queue row (each queue is limited to not grow beyond 2B rows, which seems reasonably generous), then you can do:

pg_try_advisory_xact_lock(t.qid, t.tableoid::int4)

This is supported by the current postgresql version.

Kiriakos

On Mar 7, 2012, at 12:52 PM, Andrey Chursin wrote:

> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general