4

ASP.NET MVC project 4.5 and EntityFramework Database First.

I have some command, which will be triggered when the user clicks a button

using (TransactionScope scope = new TransactionScope())
{
    using (DbContext context = new DbContext())
    {
      //update about 3 tables
      scope.Complete();
    }
}

In the other hand, I have another method which ONLY read (not updating anything) data from one of the previous tables, but it's working every 2 seconds (there is a timer which triggers this read process).

Problem: sometimes (not always) I am receiving the following exception from the reading process (not the updating process).

System.Data.SqlClient.SqlException: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

although the update process is working correctly and success and although the read process fails (sometimes not always), which is not a big deal because It will be requested again after 2 seconds and success at that time. I am afraid that I am not doing it the correct way, Is there any advice to get rid of this exception completely?

NOTE: I was not receiving this exception before, I started to receive this exception when I started to use the TransactionScope.

Update (Possible Solution)
Actually, I tried to play with the IsolationLevel as suggested in the comments, Actually, this caused a great reduction in this exception.

I created the Transaction like the following

new TransactionScope(TransactionScopeOption.RequiresNew, new TransactionOptions()
        {
            IsolationLevel = IsolationLevel.RepeatableRead
        })

NOTE: the level SnapShot did not work because the database does not support this level.

Update here is the Deadlock profile

enter image description here

Hakan Fıstık
  • 16,800
  • 14
  • 110
  • 131
  • why not use the newer, `Database.BeginTransaction` also, take a look at isolation level – TheGeneral Nov 19 '18 at 08:40
  • 1
    May be you already read this article - [Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim (Msg 1205)](https://sqlbak.com/academy/transaction-process-id-was-deadlocked-on-lock-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-msg-1205/) – kgzdev Nov 19 '18 at 08:40
  • @TheGeneral Actually I am considering that but there are some technical problems by using this, I mean no problem, I can apply this, but it will take some time (about an hour to apply it), Are you sure it could solve the problem? if so I can invest in that – Hakan Fıstık Nov 19 '18 at 08:52
  • @ikram Thank you, I read that article, it is kind of helpful, but It did not give the cure I need. – Hakan Fıstık Nov 19 '18 at 08:53
  • `BeginTransaction` wont solve your problem, and db deadlocks can be caused by very subtle and sometimes non obvious situations, however, from your description, i would try to play around with isolation levels if the transaction has provoked the problem. – TheGeneral Nov 19 '18 at 08:56
  • Have you considered using snapshot isolation? – mjwills Nov 19 '18 at 10:29
  • @mjwills until now I did not see the exception, I will consider using this level if I faced any problem at the `RepeatableRead` level. I read the documentation of those levels, and I think the `RepeatableRead` level is enough (or I hope so) – Hakan Fıstık Nov 19 '18 at 10:41
  • If you are reading every 2 seconds and can cope with dirty reads (reading uncommitted changes that may revert if the write Tx rolls back) then you can tell EF to do a dirty read on your timer. See https://stackoverflow.com/questions/926656/entity-framework-with-nolock – Steve Py Nov 19 '18 at 22:12

0 Answers0