Thread: FIFO Queue Problems
I'm having a race condition with a FIFO queue program that I've created... CREATE TABLE fifo ( id serial, data varchar(100), status int4 DEFAULT 0 ); I have a program that claims a row for itself my $processid = $$; my $sql_update = <<EOS; UPDATE fifo set status=$processid WHERE id = (SELECT min(id) FROM fifoWHERE status=0); EOS I can then my $sql_get = <<EOS SELECT data FROM fifo WHERE status=$processid EOS The problem occurrs when two of the processes grab the exact same row at the exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if setting the transactions to serializable would fix the problem since it occurrs between different postmasters. My experience with Views and Triggers is minimal. Would they even help? Could someone suggest a better FIFO queue method? __________________________________________________ Do you Yahoo!? HotJobs - Search new jobs daily now http://hotjobs.yahoo.com/
On Friday 01 Nov 2002 2:56 pm, Chris Gamache wrote: > I'm having a race condition with a FIFO queue program that I've created... > I have a program that claims a row for itself [code sample] > The problem occurrs when two of the processes grab the exact same row at > the exact same instant. It happens roughly 1 out of 1000 times. I'm not > sure if setting the transactions to serializable would fix the problem > since it occurrs between different postmasters. > > My experience with Views and Triggers is minimal. Would they even help? > Could someone suggest a better FIFO queue method? Check out SELECT FOR UPDATE - this locks the row and should do what you want. This means that 1 time in 1000 your query will fail and need to be retried. HTH -- Richard Huxton
On Fri, Nov 01, 2002 at 06:56:30 -0800, Chris Gamache <cgg007@yahoo.com> wrote: > > The problem occurrs when two of the processes grab the exact same row at the > exact same instant. It happens roughly 1 out of 1000 times. I'm not sure if > setting the transactions to serializable would fix the problem since it occurrs > between different postmasters. I played with this a bit and it looks like you want transaction isolation level serializable and have the application retry when necessary. Doing a select for update won't work cleanly. What will happen is that you will get 0 rows returned when there is a conflict and you will need to detect that same as you would by changing the isolation level. What happens in the select for update case is you first rewrite the select to use order by and limit to pick the minimum row since you can't use for update when returning an aggregate. Then when the matching row is found there is a possible lock contention with another update. If this happens then when the other update commits the locked row is rechecked and will no longer match and gets removed from the list of rows returned (leaving no rows).
Chris Gamache <cgg007@yahoo.com> writes:
> I have a program that claims a row for itself
> my $processid = $$;
> my $sql_update = <<EOS;
> UPDATE fifo
> set status=$processid
> WHERE id = (SELECT min(id) FROM fifo WHERE status=0);
> EOS
> The problem occurrs when two of the processes grab the exact same row at the
> exact same instant.
Probably the best fix is to do it this way:
BEGIN;LOCK TABLE fifo IN EXCLUSIVE MODE;UPDATE ... as above ...COMMIT;
The exclusive lock will ensure that only one process claims a row
at a time (while not preventing concurrent SELECTs from the table).
This way you don't need to worry about retrying.
regards, tom lane
Do you need to unlock the table fifo when you are done? On Fri, 2002-11-01 at 11:51, Tom Lane wrote: > Chris Gamache <cgg007@yahoo.com> writes: > > I have a program that claims a row for itself > > > my $processid = $$; > > my $sql_update = <<EOS; > > UPDATE fifo > > set status=$processid > > WHERE id = (SELECT min(id) FROM fifo WHERE status=0); > > EOS > > > The problem occurrs when two of the processes grab the exact same row at the > > exact same instant. > > Probably the best fix is to do it this way: > > BEGIN; > LOCK TABLE fifo IN EXCLUSIVE MODE; > UPDATE ... as above ... > COMMIT; > > The exclusive lock will ensure that only one process claims a row > at a time (while not preventing concurrent SELECTs from the table). > This way you don't need to worry about retrying. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Wei Weng <wweng@kencast.com>
On Fri, Nov 01, 2002 at 14:18:37 -0500, Wei Weng <wweng@kencast.com> wrote: > Do you need to unlock the table fifo when you are done? Locks only apply for the duration of a transaction. When you commit or roleback the lock will be released.
Wei Weng <wweng@kencast.com> writes:
> Do you need to unlock the table fifo when you are done?
That happens at COMMIT.
regards, tom lane