Re: choosing the right locking mode

On Thu, Apr 3, 2008 at 10:44 AM, rihad <rihad@mail.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?  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.