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.
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?..)
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
