Search Postgresql Archives

Re: choosing the right locking mode

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux