On Thu, Apr 3, 2008 at 10:44 AM, rihad <[EMAIL PROTECTED]> 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
