Re: choosing the right locking mode

On Thu, Apr 03, 2008 at 09:44:55PM +0500, rihad 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.

Do you "unblock" the pool slot by updating user_id to NULL in some later
transaction? If so, how about using INSERTs to lock and DELETEs to
unlock? You could have a table of locks:

CREATE TABLE bw_locks (
bw_id INTEGER PRIMARY KEY REFERENCES bw_pool (bw_id),
user_id INTEGER NOT NULL REFERENCES users
);

and have a function to perform the actual slot acquisition:

CREATE FUNCTION nextslot (INTEGER) RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE
id INTEGER;
BEGIN
LOOP
BEGIN
INSERT INTO bw_locks (bw_id,user_id)
SELECT MIN(bw_id), $1
FROM bw_pool p LEFT JOIN bw_locks l USING (bw_id)
WHERE l.bw_id IS NULL
RETURNING (MIN(bw_id)) INTO id;
IF FOUND THEN
RETURN id;
END IF;
RAISE EXCEPTION 'no free slots---panic!';
EXCEPTION
WHEN unique_violation THEN RAISE NOTICE 'nextslot() spinning';
END;
END LOOP;
END; $$;

This will keep trying to find the smallest id, looping when somebody
else uses it at the same time. I've not tested this code, nor written
anything much like it before so test liberally.

> 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?

This problem is always going to be awkward with a relational database
though. The problem you want to solve is the opposite of their model.

Sam