Named advisory locks

On 5/04/2011 5:42 PM, rihad wrote:

Hi, all. I'm looking for a way to lock on an arbitrary string, just how
MySQL allows doing with GET_LOCK('foo.123') / RELEASE_LOCK('foo.123'). I
know that at least Postgres 8.3 has pg_advisory_lock() /
pg_advisory_unlock() but they seem to accept integer values only, and
we're already using integer values elsewhere.

Already using _string_ values elsewhere?

Alas, I don't know of any way to use string based advisory locks directly.

You could store a mapping of lock strings to allocated ints in your app
or in the DB.

Alternately, you could maybe use the full 64 bits of the single-argument
form locks to pack in the initial chars of the lock ID strings if
they're short. If you can cheat and require that lock identifiers
contain only the "base 64" characters - or even less - you can pack 10
or more characters into the 64 bits rather than the 8 chars you'd get
with one byte per char. Of course, you can't do that if your strings are
in any way user-supplied or user-visible because you can't support
non-ascii charsets when doing ugly things like that.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On 5/04/2011 5:42 PM, rihad wrote:

Hi, all. I'm looking for a way to lock on an arbitrary string, just how
MySQL allows doing with GET_LOCK('foo.123') / RELEASE_LOCK('foo.123'). I
know that at least Postgres 8.3 has pg_advisory_lock() /
pg_advisory_unlock() but they seem to accept integer values only, and
we're already using integer values elsewhere.

Already using _string_ values elsewhere?

No, what I meant was that we're already using ints for a different
purpose in another app on the same server, so I cannot safely reuse
them. Aren't advisory lock ID's unique across the whole server? The sole
purpose of the string ID is to be able to supply an initial namespace
prefix ("foo.NNN") so NNN wouldn't clash in different subsystems of the
app. MySQL is pretty convenient in this regard. Now I think it would be
easier for me to work around this Postgres limitation by simply LOCKing
on some table (maybe one created specifically as something to lock on
to) instead of using pg_advisory_lock explicitly.

Alas, I don't know of any way to use string based advisory locks directly.

You could store a mapping of lock strings to allocated ints in your app or in the DB.

Alternately, you could maybe use the full 64 bits of the single-argument form locks to pack in the initial chars of the lock ID strings if they're short. If you can cheat and require that lock identifiers contain only the "base 64" characters - or even less - you can pack 10 or more characters into the 64 bits rather than the 8 chars you'd get with one byte per char. Of course, you can't do that if your strings are in any way user-supplied or user-visible because you can't support non-ascii charsets when doing ugly things like that.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On Apr 5, 2011, at 7:35 AM, rihad wrote:

No, what I meant was that we're already using ints for a different purpose in another app on the same server, so I cannot safely reuse them. Aren't advisory lock ID's unique across the whole server? The sole purpose of the string ID is to be able to supply an initial namespace prefix ("foo.NNN") so NNN wouldn't clash in different subsystems of the app. MySQL is pretty convenient in this regard. Now I think it would be easier for me to work around this Postgres limitation by simply LOCKing on some table (maybe one created specifically as something to lock on to) instead of using pg_advisory_lock explicitly.

Simply locking tables might be easy, but probably won't be optimal. Why are you using advisory locks at all? They certainly have their place, but they can also be an overused crutch, especially for people less familiar with MVCC.

On 04/05/2011 08:29 PM, Ben Chobot wrote:

On Apr 5, 2011, at 7:35 AM, rihad wrote:

No, what I meant was that we're already using ints for a different
purpose in another app on the same server, so I cannot safely reuse
them. Aren't advisory lock ID's unique across the whole server? The
sole purpose of the string ID is to be able to supply an initial
namespace prefix ("foo.NNN") so NNN wouldn't clash in different
subsystems of the app. MySQL is pretty convenient in this regard.
Now I think it would be easier for me to work around this Postgres
limitation by simply LOCKing on some table (maybe one created
specifically as something to lock on to) instead of using
pg_advisory_lock explicitly.

Simply locking tables might be easy, but probably won't be optimal.
Why are you using advisory locks at all? They certainly have their
place, but they can also be an overused crutch, especially for people
less familiar with MVCC. .

We're using advisory locks to limit access to an external shared resource.

On Tue, Apr 5, 2011 at 10:35 AM, rihad <rihad@mail.ru> wrote:

No, what I meant was that we're already using ints for a different purpose
in another app on the same server, so I cannot safely reuse them. Aren't
advisory lock ID's unique across the whole server? The sole purpose of the
string ID is to be able to supply an initial namespace prefix ("foo.NNN") so
NNN wouldn't clash in different subsystems of the app. MySQL is pretty
convenient in this regard. Now I think it would be easier for me to work
around this Postgres limitation by simply LOCKing on some table (maybe one
created specifically as something to lock on to) instead of using
pg_advisory_lock explicitly.

so if you have a namespace problem, solve that. the range of integers is
quite large. just assign a range to each application so they don't clash.

On Tue, Apr 5, 2011 at 10:35 AM, rihad <rihad(at)mail(dot)ru> wrote:

No, what I meant was that we're already using ints for a different purpose
in another app on the same server, so I cannot safely reuse them. Aren't
advisory lock ID's unique across the whole server? The sole purpose of the
string ID is to be able to supply an initial namespace prefix ("foo.NNN") so
NNN wouldn't clash in different subsystems of the app. MySQL is pretty
convenient in this regard. Now I think it would be easier for me to work
around this Postgres limitation by simply LOCKing on some table (maybe one
created specifically as something to lock on to) instead of using
pg_advisory_lock explicitly.

so if you have a namespace problem, solve that. the range of integers is
quite large. just assign a range to each application so they don't clash.

Can't do that, because I'm simply using some table's serial value as the
lock ID, which is itself a bigint.

The workaround of LOCKing on a table looks fine to me.

On Tue, Apr 5, 2011 at 2:49 PM, rihad <rihad@mail.ru> wrote:

Can't do that, because I'm simply using some table's serial value as the
lock ID, which is itself a bigint.

So you assigned the entire namespace to the other purpose.... seems to be
programmer's bad planning :(

On 04/06/2011 12:20 AM, Vick Khera wrote:

On Tue, Apr 5, 2011 at 2:49 PM, rihad <rihad@mail.ru
<mailto:rihad@mail.ru>> wrote:

Can't do that, because I'm simply using some table's serial value as
the lock ID, which is itself a bigint.

So you assigned the entire namespace to the other purpose.... seems to
be programmer's bad planning :(

Better programmers have invented refactoring ;-)