I have 5 sqlite tables, using the sqlite3 api.
In one process I'm moving a record from table 1 to 2, 2 to 3, etc. I'm doing the move with a lock on the table so no one else touches it during the move.
In the second process I'm going through the tables once per function call, trying to select the record to see which table the record is in. I try to select from table 1, if it's found I return the value 1. If not, I try table 2, etc. If it is not found in any table (the record can be moved back from table 2 to table 1 but then it gets deleted from table 1 so it would be considered deleted once it leaves table 2 and I don't have to find it in table 1 to be correct) then I return 0.
The second process does not use any locks.
I only want the second process to return 0 if the record is truly not there - if it's in transition between tables I don't want to return 0 by accident.
I think that since I'm locking the tables during transitions, and since I'm only allowed to move (and select) in the forward direction (1->2->3->4->5) I should not be missing the record. Is this a simple programming 101 problem that I'm just not remembering?
I'm wondering if anyone knows what this problem would be called (I know it's not quite producer/consumer, but some name like that) that I could look up to see it solved in a textbook?
Any info is appreciated. Thanks.