Thanks (to other repliers too), That put me on the right track.
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf Sent: October-18-13 7:34 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SELECT and UPDATE? 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
