How to completely lock a row in Entity Framework

Question

I work with a situation when we are dealing with money transactions.

For example, I have a table of user wallets with their balance on this line.

UserId; Wallet Id; Balance 

Now on our website and web services, every time a certain transaction occurs, we need to:

• make sure that you have enough funds to complete this transaction:
• subtract transaction costs from the balance sheet.

How and what is the correct way to lock this line / object for the entire period of my transaction?

From what I read, there are some solutions where EF marks an object and then compares this sign when it saves it back to the database, however, what does it do when another user / program has already edited the amount?

Can I achieve this with EF? If there are no other options that I have?

Will a stored procedure call possibly allow me to lock the row correctly so that no one else can access that row on SQL Server while there is a lock in program A?

Answer 1

EF does not have a built-in locking mechanism, you probably need to use a raw request, for example

 using (var scope = new TransactionScope(...)) { using (var context = new YourContext(...)) { var wallet = context.ExecuteStoreQuery<UserWallet>("SELECT UserId, WalletId, Balance FROM UserWallets WITH (UPDLOCK) WHERE ..."); // your logic scope.Complete(); } } 

Answer 2

The whole point of a transactional database is that the data consumer determines how to isolate their data representation.

Regardless of whether your transaction is serialized , someone else can dirty read the same data that you just changed but did not commit.

First of all, you should take care of the integrity of your presentation, and then accept only the rejection of the quality of this presentation in order to improve system performance if you are sure that it is necessary.

Wrap everything in a TransactionScope with a Serialized isolation level, and you personally can't go wrong. Remove only the isolation level when you see that it is really necessary (for example, when something is wrong, sometimes everything is in order).

Someone asks about this here: SQL Server: preventing dirty reads in a stored procedure

Answer 3

you can set the isolation level in a transaction in the Entity infrastructure so that no one can change it:

 YourDataContext.Database.BeginTransaction(IsolationLevel.RepeatableRead) 

RepeatableRead Summary: Locks are placed in all the data used in the request and do not allow other users to update the data. Prevents non-duplicate reads, but phantom strings are still possible.