CODEWITHSUNDEEP
c#sqlentity-frameworkobjectcontext
user1955255
user1955255

Reputation: 229

Multiple console applications updating same sql table using EF objectcontext

I have multiple console applications but they would be reading and inserting updating same sql table. I used transactionscope but I am getting this error "Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

I get an exception in my catch block. How can I avoid this? I know what the problem is but I am not sure what should be the possible solution. I see a lot questions being asked on different forums but none of them really helped me.

Is everything good in my code?

bool isMaster = false;
tourneyInstanceTrackerId = 0;
using (JG_RummyEntities dbContext = new JG_RummyEntities())
{
try
{
using (TransactionScope transaction = new    TransactionScope(TransactionScopeOption.Required,new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
                {
bool isAnyMaster = dbContext.TourneyInstanceTrackers.Any(t => t.IsMaster & t.TournamentId == tournamentId);
TourneyInstanceTracker tourneyInstanceTracker = new TourneyInstanceTracker
                    {
                        TournamentId = tournamentId,
                        IsMaster = !isAnyMaster,
                        CreateDate = DateTime.Now
                    };
                       dbContext.AddToTourneyInstanceTrackers(tourneyInstanceTracker);
dbContext.SaveChanges();
                    var result = dbContext.TourneyInstanceTrackers.Where(t => t.TournamentId == tournamentId)
                            .OrderByDescending(t => t.CreateDate)
                            .Select(t => new { t.IsMaster, t.Id })
                            .FirstOrDefault();
                    if (result != null)
                    {
                        isMaster = result.IsMaster;
                        tourneyInstanceTrackerId = result.Id;
                    }
                    transaction.Complete();
                }
            }
            catch (Exception ex)
            {
                Logger.Log("Got exception in SetTournamentInTourneyInstanceTracker : " + ex.Message + ", " + ex.StackTrace + ", "+ tournamentId);
            }
        }

Upvotes: 0

Views: 195

Answers (1)

Scott Chamberlain
Scott Chamberlain

Reputation: 127603

IsolationLevel.RepeatableRead acts like a ReaderWriterLockSlim, many threads can read a record at the same time but as soon as one wants to write you must wait for all readers to finish then take a exclusive lock on the object till you finish writing.

So imagine you have a program, it reads object "A" waits a bit then writes object "A". Now lets look at what happens when two copies of the program runs.

Program 1     Program 2
---------     ---------
Begin Trans   Not Started
Read A        Begin Trans
Wait          Read A
Try Write A   Wait
Try Write A   Try Write A
Try Write A   Try Write A
Try Write A   Try Write A

You can see Program 1 is waiting for Program 2 to release its read lock, however Program 2 can't release its read lock until Program 1 releases its read lock. This situation where the two are waiting for the other to finish is called a deadlock.

The are two ways you get around this, one you could use the more restrictive IsolationLevel.Serializable, this blocks other readers until the transaction finishes.

Program 1     Program 2
---------     ---------
Begin Trans   Not Started
Read A        Begin Trans
Wait          Try Read A
Write A       Try Read A
End Trans     Read A
              Wait
              Write A
              End Trans

The other is you follow the exception's advice, on the block of code that caught the exception you re-run the function again until it gets through without another copy of the program blocking it.

Upvotes: 2

Related Questions