On Thu, Apr 3, 2008 at 11:42 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Thu, Apr 3, 2008 at 11:32 AM, rihad <rihad@mail.ru> wrote:
> > Scott Marlowe wrote:
> >
> > > 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?
> > >
> >
> > 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 this is the case, you could use a sequence and just select using it
for the id until you hit a row that was null and use it. since all
access for this would use the sequence no one would hit the row at the
same time, they'd be one ahead or behind you. Set it to cycle and
you've got a self-maintaining system.
