Worker may have a race-condition when accepting work

[drigz]

I'm not sure how rowcount is set, so when Worker goes to update a work item as being taken by it, it assumes that of two concurrent executions of:

UPDATE uploaded SET state=1 WHERE state=0 AND id=1;
COMMIT;

after the commit, one cursor will have rowcount=1 and one will have rowcount=0. However, this may not be true, but I'm not sure how to correctly implement a queue removal like this in SQL, or even if it's a sensible thing to try.

An alternative would be to use Python's multiprocessing.Queue, but this is not particularly important while load is low...

[drigz]

Hmmm, rowcount is definitely not safe:

If a separate thread makes changes on the same database connection while sqlite3_changes() is running then the value returned is unpredictable and not meaningful.

• https://www.sqlite.org/c3ref/changes.html

[drigz]

"column restrictions" may help for this, whatever they are.