title: Multithreaded application with database read - each thread unique records
body: <p>I have a .net application which basically reads about a million of records from database table each time (every 5 minutes), does some processing and updates the table marking the records as processed.</p>
  
  <p>Currently the application runs in single thread taking about top 4K records from DB table, processes it, updates the records, and takes the next.</p>
  
  <p>I'm using dapper with stored procedures. I'm using 4K records for retrieval to avoid DB table locks.</p>
  
  <p>What would be the most optimal way for retrieving records in multiple threads and at the same time ensuring that each thread gets a new 4K records?</p>
  
  <p>My current idea is that i would first just retrieve the ids of the 1M records. Sort the ids by ascending, and split them into 4K batches remembering lowest and highest id in a batch.
  Then in each thread i would call another stored procedure which would retrieve full records by specifying the lowest and highest ids of records retrieved, process that and so on.</p>
  
  <p>Is there any better pattern i'm not aware of?</p>