On Fri, 18 Oct 2013 13:57:18 -0400
"Normand Mongeau" <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
