Re: choosing the right locking mode

Scott Marlowe wrote:
> On Thu, Apr 3, 2008 at 10:44 AM, rihad <rihad(at)mail(dot)ru> wrote:
>> Given this type query:
>>
>> UPDATE bw_pool
>> SET user_id=?
>> WHERE bw_id=
>> (SELECT MIN(bw_id) FROM bw_pool WHERE user_id IS NULL)
>> RETURNING bw_id
>>
>> The idea is to "single-threadedly" get at the next available empty slot, no
>> matter how many such queries run in parallel. So far I've been
>> semi-successfully using LOCK TABLE bw_pool before the UPDATE, but it
>> deadlocks sometimes. Maybe I could use some less restrictive locking mode
>> and prevent possible collisions at the same time?
>
> So, is there some reason a sequence won't work here?

bw_pool is pre-filled with 10 thousand rows of increasing bw_id, each of
which is either set (user_id IS NOT NULL) or empty (user_id IS NULL).
The state of each can change any time.

> If you've got a
> requirement for a no-gap id field, there are other, less locky-ish
> ways to do it. Locking the table doesn't scale, and that's likely
> what problem you're seeing.
>
There's a shared resource backed by bw_pool that I absolutely need
single-threaded access to, despite multiple cpus, hence an all-exclusive
lock (or?..)