I'm having a problem with the design of my application, that neither optimistic nor pessimistic locking tends to solve. Here is a simplified/altered version of the problem that describes the situation.
Premise of the problem:
- A document-processing application
- A database where documents are stored
- Multiple clients simultaneously access the database
What I try to achieve:
Say there are 10 unprocessed documents in the database. The first client requests the number of unprocessed documents. The response is 10. He requests 6 of them and starts processing. Before he finishes the second client requests the number of unprocessed documents. There is no point for him to process the same documents as the first client, so I want the program to answer "There are 4 unprocessed documents".
The problem if I optimistically lock those 6 documents:
In the particular case of this application transaction will take a long time and there is a big chance that other client still thinks that there are 10 unprocessed documents, not 4.
The problem if I pessimistically lock those 6 documents:
Again, the transaction takes a long time to complete and the other client will get stuck waiting for an answer about the number of documents.
The problem if I shorten the transaction and commit a status change before processing:
There is a chance that a power outage occurs, as a result 6 documents have a status "exported for processing", but in reality they aren't.
What alternative/custom locking strategy should be selected for this kind of scenario with concurrent access and long transactions?