Using transactions or locking in the Entity Framework to ensure proper operation

Question

I am new to EF and SQL in general, so I could use some help clarifying this point.

Let's say I have a wallet table (and a code key of the first EF code code) with an identifier and balance. I need to do this operation:

if(wallet.balance > 100){ doOtherChecksThatTake10Seconds(); wallet.balance -= 50; context.SaveChanges(); } 

As you can see, it checks if the condition is really valid, then if so, you need to perform a bunch of other operations that take a lot of time (in this exaggerated example we say 10 seconds), then if it is he subtracts $ 50 from the wallet and saves new data.

The problem is that there are other things that can change the wallet balance at any time (this is a web application). If this happens:

• wallet.balance = 110;
• this operation passes the if check because wallet.balance> 110
• when he executes the doOtherChecksThatTake10Seconds () command, the user transfers $ 40 from his wallet
• now wallet.balance = 70
• "doOtherChecksThatTake10Seconds ()" completes, subtracts 50 from the wallet. Balance and then save the context with the new data.

In this case, the wallet check> balances> 100 is no longer valid, but the operation is still due to a delay. I need to find a way to lock the table and not let it go until the whole operation is completed, so nothing is edited during the editing process. What is the most efficient way to do this?

It should be noted that I tried to perform this operation in TransactionScope (), I'm not sure if this will have the intended effect or not, but I noticed that it started to cause a lot of deadlocks with a completely different database operation that works.

Answer 1

Use Optimistic concurrency http://msdn.microsoft.com/en-us/data/jj592904

 //Object Property: public byte[] RowVersion { get; set; } //Object Configuration: Property(p => p.RowVersion).IsRowVersion().IsConcurrencyToken(); 

This allows dirty reading. BUT, when you switch to updating the record, the system checks that the rowversion variable has not changed on average, it fails if someone changes the record in the meantime. Rowversion is maintained by DB every time a record is changed.

Extremely optimized EF lock.

Answer 2

you can use the transaction scope.

Import namespace

 using System.Transactions; 

and use it as below:

 public string InsertBrand() { try { using (TransactionScope transaction = new TransactionScope()) { //Do your operations here transaction.Complete(); return "Mobile Brand Added"; } } catch (Exception ex) { throw ex; } } 

Answer 3

Another approach could be to use one or more internal queues and use this queue (s) with only one thread (template-producer-consumer). I use this approach in the reservation system and it works very well and very easily.

In my case, I have several queues (one for each "product") that are created and deleted dynamically and several consumers, where only one consumer can be assigned to one queue. It also allows handling higher concurrency. In a high concurrency scenario with user houndredthousands, you can also use separate servers and queues such as msmq to handle this.

There may be a problem with this approach in the ticket system, where many users want to have a ticket for a concert or in the trading system when the new “Harry Potter” appears, but I don’t have such scenarios.