Re: choosing the right locking mode

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? 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.