Re: Select for update with offset interferes with concurrent transactions

Hi,

Thanks for the quick answer, Andy.

On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson <andy@squeakycode.net>
wrote:

<snip>
> So, if I understand correctly, you:
>
> q = SELECT record_id FROM queue
>     WHERE project_id = my_project AND state = idle
>     LIMIT n OFFSET i FOR UPDATE
> while not q.eof
>     update queue set state = started where record_id = x;
>     process record_id
>     update queue set state = finsihed where record_id = x;
>     q.next;

Almost, the update to "started" is done for all selected elements first,
releasing the lock, then the items are processed one at a time, marking
each "finished" as they complete. (each processing step can take minutes,
so keeping a lock the whole time is not an option)

> Might I suggest and alternative:
>
> q = update queue set state = started
>     WHERE project_id = my_project AND state = idle
>     LIMIT n OFFSET i
>     RETURNING project_id;
> idlist = @q;
> commit;
>
> foreach x in idlist
>     process record_id
>     begin
>     update queue set state = finsihed where record_id = x;
>     commit;
>
> Forgive the part perl part python sudocode.  Oh, and I've never done
> this, no idea if it actually works. :-)

Thanks for that suggestion, I'll take a look at it.

While I hadn't caught on to the "RETURNING" part, I had been wondering if
using a single step UPDATE might be a solution. One concern I have is how
concurrent updates will affect the returned list (or if they will just be
skipped, as SELECT would in normal transaction mode, if I understood
correctly), or whether it might return with an error code (I know that the
normal update return value is the number of updated items, just not sure
if that applies for "RETURNING").

Although, I will note that this process (if it works) will, sort of, make
FOR UPDATE redundant. Or, if it doesn't, the current lock-policy might
cause issues for concurrent updates for the use-cases where FOR UPDATE is
relevant.

--
Sincerely,
Yngve N. Pettersen
********************************************************************
Senior Developer             Email: yngve@opera.com
Opera Software ASA                   http://www.opera.com/
Phone:  +47 23 69 32 60              Fax:    +47 23 69 24 01
********************************************************************