For multiple consumers you might do something like:
BEGIN;
UPDATE processingqueue SET status = 'failed' WHERE status =
'processing' AND processor = :processor;
UPDATE processingqueue SET status = 'processing', processor =
:processor, started = strftime('%s')
WHERE queueid = (SELECT queueid from processingqueue where status =
'ready' AND processor IS NULL ORDER BY queueid LIMIT 1);
SELECT * FROM processingqueue WHERE status = 'processing' AND processor
= :processor;
UPDATE processingqueue SET status = 'ready', processor = NULL WHERE
status = 'failed' AND processor = :processor;
COMMIT;
and run it as a single statement providing the processor name binding
to the named variable :processor and you will get back one row to
processes on :processor, assuming that there is work to do. Otherwise
you will not get back a row.
If the same :processor asks for more work to do and it is already
processing, then the currently dispatched task failed and you should
get a new one. The failed job will then be returned to ready and can
be dispatched to any worker in need of work.
schema would look like:
create table processingqueue
(
queueid integer primary key,
status text collate nocase,
started integer,
... other data you need ...
unique (processor, status, queueid)
);
On Fri, 18 Oct 2013 19:04:54 -0400
"James K. Lowden" <jklow...@schemamania.org> wrote:
>On Fri, 18 Oct 2013 13:57:18 -0400
>"Normand Mongeau" <nmong...@theobjects.com> wrote:
>
>> Also, the consuming should be a 2-step process because the
>processing
>> is involved and may fail for reasons too long to explain here. So in
>> essence, select a record, modify it to indicate it's being
>processed,
>> and once the processing is done delete the record. Is there a way to
>> do the initial selection in one swoop (select and update) or is it
>> two SQL statements? I have to avoid two different processes
>selecting
>> and modifying the same record to minimize rollbacks/retries.
>
>Read after write, not write after read.
>
>Counterintuitive, perhaps, but if the reading process begins by
>*updating* the record it's about to process, you have idempotent
>processing without the need for a user-defined transaction.
>
>writer:
> insert ... (status, key, data) values ('queued', 1, 'foo');
>
>reader:
> update ... set status = 'processing'
> where key = (select min(key) ... where status <> 'done');
> select ... where status = 'processing';
> /* work work work */
> update ... set status = 'done' where key = @key;
>
>SQL-92 IIRC defines an OUTPUT clause for UPDATE, which would do what
>you want (select+output in one statement). But that's not a SQLite
>feature.
>
>--jkl
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@sqlite.org
>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
----
#include <shrinkwrap-disclaimer.h>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
