Member Avatar for swaransoft

We are working on a project & finding an issue while accessing the data from SQL Server.

Problem: Display of record in search console of application on number of times it has been processed.

Consider we have a record which needs to be displayed 5 times a day in search console & it has been running on client server architecture. Now the record has been displayed for 4 times in a day & then at the same moment 3 client machines request for same data at same time. So if we allow display of that data to all 3 machines & stops the processing on 2 machines ..then it will be not user friendly approach as we should stop display rather than stop the processing.

Solution :
1. We can check out for concurrent request to record (can this be handled by any system or sql command ? )
2. We can display the record but not allow the processing (I don’t want this approach)

Looking ahead for some solution.

  • 2 Contributors
  • 1 Reply
  • 130 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Ole Raptor

Dani AI

Generated

The underlying problem is a classic race: separate “read current count, then display” steps let multiple clients read the same remaining quota and all proceed. wants to cap daily displays at N even under simultaneous requests; ’s idea of counting in a history table is right conceptually, but the check must be made atomic at the database so concurrent readers cannot exceed the limit.

Three practical, proven patterns in SQL Server:

  • Atomic conditional UPDATE (recommended). Perform a single conditional UPDATE that increments the counter only when it is below the daily limit and return the updated row via OUTPUT. If the UPDATE affects a row (check @@ROWCOUNT or the OUTPUT result), allow display; otherwise deny. This is simple, fast, and avoids explicit locks:
BEGIN TRANSACTION;
UPDATE dbo.SearchItems
SET DisplayCount = DisplayCount + 1
OUTPUT inserted.*
WHERE Id = @Id
  AND DisplayDate = CAST(GETDATE() AS date)
  AND DisplayCount < 5;

IF @@ROWCOUNT = 1
  COMMIT TRANSACTION; -- permit display (use returned row)
ELSE
  ROLLBACK TRANSACTION; -- limit hit; do not display

  • Application-level or DB advisory locks. Use sp_getapplock to serialize access per logical key (record/day) when a strict sequence is needed. This is explicit and clear but can increase contention. See sp_getapplock documentation: sp_getapplock (Transact-SQL).

  • Optimistic concurrency with rowversion. Read the rowversion, attempt UPDATE ... WHERE rowversion = @rv and DisplayCount < limit, and retry on failure. This reduces blocking but requires retry logic and is documented here: rowversion (Transact-SQL).

Notes and gotchas:

  • Keep the check-and-reserve inside the database; client-side checks alone will fail under concurrency.
  • Use indexes on the key/date to avoid table scans.
  • To keep audit/history, insert history rows in the same transaction (use OUTPUT INTO) so history always matches the enforced count: .
  • Avoid READ UNCOMMITTED / NOLOCK for the gating queries; that will produce incorrect counts.
  • Test with concurrent clients (stress tests) to validate behavior under load.

These approaches let the application reliably stop display once the daily quota is reached while allowing multiple clients to request simultaneously without overrunning the limit.

Member Avatar for Ole Raptor

How are you keeping track of the times accessed? Is it a history table? If you try to access a record that lets say has a history and has been accessed 5 times it adds it to history. programmatically query for total times accessed where date = now. It is very unlikely that other machines will access this at the exact moment. (Not impossible just very unlikely) If the history count is >= 5 just dont display it.
I am assuming this is how you are pulling the info. a little more info on how you are keeping track of the hits per day and what you are using for the client programming will be helpful aswell.

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.