Обсуждение: Is what I want possible and if so how?
Hi, I have a table with events that must be handled by multiple clients. It does not matter which client handles an event, but no two clients may handle the same event and an event may only handled once. A client can only determine the availability of an event by querying the database. The access to the table should be queue-like with synchronization. My idea was that a client should do a "SELECT" on the table and mark the selected records as "being handled" to avoid double handling. I have read the manual about "LOCK", "SET TRANSACTION" and "SELECT...FOR UPDATE", but from what I understand, they cannot prevent a "SELECT" from another client, based on the "SELECT". Is there a way to make this possible? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes: > I have a table with events that must be handled by multiple clients. It > does not matter which client handles an event, but no two clients may > handle the same event and an event may only handled once. A client can > only determine the availability of an event by querying the database. > The access to the table should be queue-like with synchronization. > My idea was that a client should do a "SELECT" on the table and mark the > selected records as "being handled" to avoid double handling. > I have read the manual about "LOCK", "SET TRANSACTION" and "SELECT...FOR > UPDATE", but from what I understand, they cannot prevent a "SELECT" from > another client, based on the "SELECT". > Is there a way to make this possible? Sure, but everybody has to use SELECT FOR UPDATE and/or UPDATE. For instance, do this in READ COMMITTED mode: begin; select jobid from queue where status = 'waiting' order by priority limit 1 for update; [ if no row returned, rollback, sleep a bit, try again ] update queue set status = 'busy' where jobid = 'prev result'; commit; Once TX1 has selected a row for update, it's locked and TX2 will sleep until TX1 commits before selecting it; then it will see the change of status and TX2's select will not return the row. This isn't entirely perfect because LIMIT acts before FOR UPDATE: TX2's select will return nothing, rather than selecting the next available row as you might wish. So you might want to retry the select several times before deciding there's nothing to do. Also, an index on priority would be a good idea to make the select fast. regards, tom lane
Joost, Everyone has to do a select for update, then they will be serialized. Dave On 3-Jul-06, at 6:45 AM, Joost Kraaijeveld wrote: > Hi, > > I have a table with events that must be handled by multiple > clients. It > does not matter which client handles an event, but no two clients may > handle the same event and an event may only handled once. A client > can > only determine the availability of an event by querying the database. > The access to the table should be queue-like with synchronization. > > My idea was that a client should do a "SELECT" on the table and > mark the > selected records as "being handled" to avoid double handling. > > I have read the manual about "LOCK", "SET TRANSACTION" and > "SELECT...FOR > UPDATE", but from what I understand, they cannot prevent a "SELECT" > from > another client, based on the "SELECT". > > Is there a way to make this possible? > > TIA > > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > web: www.askesis.nl > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster >