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