On Thu, Apr 3, 2008 at 11:32 AM, rihad <rihad@xxxxxxx> wrote: > Scott Marlowe wrote: > > > On Thu, Apr 3, 2008 at 10:44 AM, rihad <rihad@xxxxxxx> 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. So, then ANY id would do, would it not, as long as it was null when you picked it? > > > > > 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?..) Well, my most basic question was if that shared resource is a design flaw in the way it's set up. I'm still not convinced it isn't, but I know how you can get stuck with things like this too. Building a solution that works around this limitation may be as much work to get done as fixing whatever basic design flaw underlies this. If it is a design flaw. > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
