I am using an sql database to manage the order in which I undertake a series of tasks:
I have an sql database, and select from it a row with the lowest value on the priority column, where the value on the processed column has the word "unprocessed". I then immediately change the processed status to work_in_progress and commit the change (while my code goes about working on that row).
I intend to have multiple separate instances of the program interface with the same sql database (i.e. looking for the lowest priority unprocessed row).
How do i avoid the situation where two separate programs (i.e. separate connections) will query the database concurrently, (i.e. before the change is committed by the first one) - i want to have it such that each connection queries, updates and commits before the next connection is able to query.
For reference, I am using sqlite3 on python.
