Re: 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?
>
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/