Click here to Skip to main content
15,029,433 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have transaction table like below where transaction entry will insert and after that validation is happening for transaction such as
no account number perform transaction more than 5000 ( 5K) in a Month.
Now in below scenario both the transaction will process successfully as its comes from different session via API in same time.


<pre>tranid	Amount	AccountNumber	TrnTime
1001	5000	12345		2021-01-31 20:20:57.713
1002	2000	12345		2021-01-31 20:20:57.713



SQL Query
=========
select sum(Amount) from tblTransaction with(NOLOCK)
where DebitAccount='12345' and MONTH(TrnTime)=MONTH(GETDATE())

What I have tried:

I removed with(NOLOCK) but query performance very slow .This table contain records more than crores and same time almost 100 transactions coming per millisecond so I have restriction on removing no lock which will create locking/delay in other transaction processing.
Posted
Updated 4-Feb-21 23:18pm
v2

1 solution

Using NOLOCK is not a good solution. It will cause lots of problems.
Bad habits : Putting NOLOCK everywhere[^]

It would be better to fix the indexes and/or partitioning on your table so that you can run the query without using this dangerous hint.
   
Comments
vishal_h 5-Jul-21 9:49am
   
Index and partitioning is already in place but the issue with concurrency as we have to calculate the SUM(Amount) on the fly to validate each concurrence request.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900