On 2/1/2011 12:10 PM, Yngve Nysaeter Pettersen wrote:
> On Tue, 01 Feb 2011 18:11:23 +0100, Andy Colson <andy@squeakycode.net>
>> I think the random limit thing is a race condition itself. Whenever
>> you have multiple processes hitting the same rows you're going to run
>> into problems. Have you thought of using a sequence instead of a
>> random limit? Each process could get the next 100 record_id'd via a
>> sequence, then there would be much less chance of deadlock.
>
> How would that work, in case you would like to provide an example?
>
> I am not really familiar with sequences, as I have only seen them used
> for the "id" field in Django generated tables.
>
> In case it is relevant, the processes does not (currently, at least)
> have a unique ID; though they have a local sequence number for the
> machine they are running on.
>
>
I have a really simple q table I use.
create table q (id integer not null, msg integer, primary key(id));
create sequence q_add;
create sequence q_read;
I insert via q_add:
andy=# insert into q(id, msg) values(nextval('q_add'), 20);
INSERT 0 1
andy=# insert into q(id, msg) values(nextval('q_add'), 4);
INSERT 0 1
andy=# select * from q;
id | msg
----+-----
1 | 20
2 | 4
(2 rows)
Then I run multiple batch proc's which get their next job like:
andy=# select msg from q where id = (select nextval('q_read'));
msg
-----
20
(1 row)
andy=# select msg from q where id = (select nextval('q_read'));
msg
-----
4
(1 row)
It works for me because I can empty the q table, reset the q_add and
q_read sequences and start over clean. Not sure if it would work for
your setup.
-Andy