I am trying to insert the data into the
Wallets table of a SQL Server database.
There are can be many requests at the same time so due to the sensitive info I have to use transactions.
The work flow is the following:
read the amount of the user's wallet
insert the new record based on the previously received data
I tried different isolation levels but in all the cases the transaction blocks the whole table, not just the record I am working with. Even
RepeatableRead block the whole table.
Is there a way to block only the records I am working with?
Let me detail: I don't use any indexes in the table
The workflow (translating C# into SQL) is the following:
1) Select * from Balance 2) Insert ... INTO Balance
you should use optimistic locking. That will only lock the current row. Not whole table.
You can read below links for more reference :-