Table queue (locking)

From: Tomas Simonaitis <tomas(dot)simonaitis(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Table queue (locking)
Date: 2007-08-03 17:15:04
Message-ID: 200708032015.04823.tomas.simonaitis@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,

I've got following two-tables events queue implementation
(general idea is that multiple writers put events, while multiple readers
retrieve and handle them in order):

Table events:
ev_id: SERIAL
ev_data: bytea -- serialized event details

Table eventsconsumers:
con_name: text UNIQUE -- consumer name
con_lastevent: integer

Consumers issue:
SELECT * events WHERE ev_id > "con_lastevent" LIMIT XX
to fetch new events[1]
Once event is handled (or ignored) by a reader he sets con_lastevent to
handled ev_id.

Obviuos problem with this simple implementation is following race condition:

-- Writer1
BEGIN;
INSERT INTO events.... (ev_id = 1)
-- Writer2
BEGIN;
INSERT INTO events... (ev_id = 2)
COMMIT;
--Reader1
SELECT * FROM events WHERE ev_id > 0; -- first round
UPDATE eventsconsumers SET con_lastevent = 2 WHERE con_name = 'Reader1';
--Writer1
COMMIT; -- Reader1 missed ev_id = 1

I've got two ideas to solve it:
1- BEGIN; LOCK TABLE events IN ACCESS EXCLUSIVE MODE; INSERT INTO events...;
COMMIT;
Doesn't seem too bright: events might get posted in the begining of (rather
long) transaction and there are many active writers.

2-
<while not success>:
{
BEGIN;
LOCK TABLE events IN SHARE MODE NOWAIT; -- block writers
}
SELECT * FROM events....
COMMIT;
Intuitively I believe backing-off with NOWAIT is better (since readers
performance is not that important).

Could You suggest better ways to solve this problem?,
maybe I'm missing something obviuos here.

Thanks,
Tomas

[1]{LISTEN/NOTIFY is used for "new-event-arrived" notifications}

Browse pgsql-general by date

  From Date Subject
Next Message Jeff Davis 2007-08-03 19:42:38 Re: What do people like to monitor (or in other words, what might be nice in pgsnmpd)?
Previous Message Andrus 2007-08-03 16:09:32 pg_shdepend big