From: | James Mancz <james(at)mancz(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Trx issues: SELECT FOR UPDATE LIMIT |
Date: | 2003-04-30 14:49:49 |
Message-ID: | 20030430144949.8F187475458@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Is the behaviour I describe below expected? I'm thinking that it isn't,
especially when compared with the comparable behaviour from Oracle and Sybase.
I'm running PostgreSQL 7.3.2.
I believe the problem is with transaction behaviour when using SELECT ... FOR
UPDATE with LIMIT.
I'll describe this by way of SQL to reproduce the issue:
Preparation:
CREATE TABLE work
(id int,
alloc int null);
INSERT INTO work VALUES (1, NULL);
INSERT INTO work VALUES (2, NULL);
Basically, the idea is that the work table in reality includes millions of rows
of 'work' for various clients to share. The client will grab a batch of rows,
process them, and then write the rows back to the database.
To grab a batch of rows to process, the client will SELECT a number of them, and
then update the alloc field to the ID of that client, thus marking them as being
worked on/work completed so that other clients don't process the same rows.
So, each client would do
BEGIN;
SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
(of course, in reality, the LIMIT value here would be 1000 or whatever and the
work table would include lots of additional data for the client to process)
UPDATE work SET alloc = 99 WHERE id = 1;
COMMIT;
The problem occurs when one or more SELECTs occur while another SELECT is in
progress; use this to reproduce:
So, client 1 sends:
BEGIN;
SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
at this time the table values are:
id alloc
----- --------
1 NULL
2 NULL
meanwhile client 2 sends:
BEGIN;
SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE;
This, of course, waits for client 1 to finish.
client 1 sends:
UPDATE work SET alloc = 99 WHERE id = 1;
COMMIT;
at this time the table values are:
id alloc
----- --------
1 99
2 NULL
client 2 can carry on processing now,
and its SELECT * FROM work WHERE alloc IS NULL LIMIT 1 FOR UPDATE; **returns no
rows** despite the fact the second row matches.
It _should_ return:
id alloc
----- --------
2 NULL
When similar functionality it tried on Oracle and Sybase (using their "versions"
of LIMIT -- rownum and SET rowcount respectively), they both return the second
row.
Thoughts?
james(at)mancz(dot)com
--- Msg sent via WebMail(at)mancz(dot)com - http://mail.mancz.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-04-30 15:07:44 | Re: Trx issues: SELECT FOR UPDATE LIMIT |
Previous Message | Tom Lane | 2003-04-30 14:26:11 | Re: Cygwin PostgreSQL CVS build issues |