How to ensure records are read from a table in order they are inserted.
We have a custom publish service, and are having issues with records not being retrieved in the order they are written.
There are a number of "batch" processes that use a global transaction to process all data in the batch. When this happens, the individual records are not committed until the entire batch is processed. But since we are running a multi-threaded (multiple MS on multiple app servers) environment, other threads write to the table and commit immediately.
Then when a read request is received, the read is based on the last transaction ID sent from the app. When the uncommitted records exist, they end up being skipped.
Has anybody dealt with how to ensure publishes to downstream systems happen in the proper order, especially when dealing with batch and interactive processes at the same time?