SQL Server concurrency issue while transaction amount real time limit validation

Question

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 day.

Now in below scenario both the transaction will process successfully as its comes from different session via API in same time.

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

question from:https://stackoverflow.com/questions/66058232/sql-server-concurrency-issue-while-transaction-amount-real-time-limit-validation

Answer 1

Your query slowness is almost certainly down to :

1. Lack of suitable indexes to quickly find the associated rows
2. The function being applied to the TrnTime column - forcing a table scan

If you create an additional calculated column for MONTH(TrnTime) and include that in a non-clustered index with Amount and DebitAccount then your query would be quicker.

Also, calculating a balance (which I assume you are by calculating the sum of amounts on an account) while using NOLOCK is full of dangers through phantom reads and repeated reads so I would not trust those query results.

With the size of the table, also consider partitioning it - either by MONTH(TrnTime) or by DebitAccount columns