Member Avatar for HunainHafeez

if i have table in database(sql server) and has 1500 records and i run 2 applications(at same time) that will retrieve 500 records each so how can i do this

app1 will retrieve first 500 records
app2 will retrieve next 500 records means no over lap

SET Transaction Isolation Level Read committed not working for me

note: actually my app have one db and many client apps so (running 1 or more at a time) while reading records from DB i have to lock those rows so that it can't be read by others' requests and after that i will update status. question is how can i LOCK rows while reading ?

Edited by pritaeas because: Moved.
  • 2 Contributors
  • 2 Replies
  • 322 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by HunainHafeez

Dani AI

Generated

Short answer: don’t rely on READ COMMITTED alone — you need an atomic “claim rows” operation so each client marks which 500 rows it will process. The usual, safe pattern is: start a short transaction, atomically update a TOP(N) set of rows from Status='New' to Status='InProgress' (or set a LockedBy value), return the updated IDs with OUTPUT, commit, then process those rows outside the transaction.

Example pattern (SQL Server):

BEGIN TRAN;

WITH toclaim AS (
  SELECT TOP (500) *
  FROM dbo.YourTable WITH (ROWLOCK, READPAST, UPDLOCK)
  WHERE Status = 'New'
  ORDER BY Id
)
UPDATE toclaim
SET Status = 'InProgress', LockedBy = 'App1', LockedAt = GETUTCDATE()
OUTPUT inserted.Id;

COMMIT TRAN;

Why this works: UPDLOCK takes an update lock while selecting so other claimers won’t pick the same rows; READPAST makes concurrent claimers skip locked rows; ROWLOCK helps avoid escalation to page/table locks. Returning IDs with OUTPUT gives the exact set the app should work on, avoiding overlap even with many clients.

Practical tips and caveats: keep the transaction that claims rows as short as possible (do the heavy processing after commit). Use a deterministic ORDER BY to reduce deadlocks. If you claim large batches, SQL Server may escalate locks — break into smaller batches or use ROWLOCK. SNAPSHOT isolation won’t prevent duplicate claims (it avoids blocking but does not lock rows), so it’s not a substitute for marking rows. For high throughput, queuing solutions (Service Broker, MSMQ) or the approach mentioned are reasonable alternatives; sp_getapplock can serialize work but is coarser-grained.

If overlap still appears, verify that every client uses the same claiming SQL (with UPDLOCK/READPAST or the atomic UPDATE), test with concurrent clients, and monitor for lock escalation or deadlocks.

Member Avatar for pritaeas

Will you share your solution?

Member Avatar for HunainHafeez

i used Microsoft Queue for keeping cache of records as a thread in MS queue :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.