Re: choosing the right locking mode

On Thu, Apr 3, 2008 at 11:32 AM, rihad <rihad(at)mail(dot)ru> wrote:
> Scott Marlowe wrote:
>
> > 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?
> >
>
> 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.

>