none
Transaction Deadlock RRS feed

  • Question

  • Hi,

    I'm getting an exception "Transaction was deadlock on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction" while trying to step through my program. Break is added at SubmitChanges as commented. See below for my source code.

    namespace WindowsFormsApplication28
    {
        public partial class Form1 : Form
        {
            private Thread t1;
            private Thread t2;
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                t1 = new Thread(new ThreadStart(this.func1));
                t2 = new Thread(new ThreadStart(this.func2));

                t1.Start();
                t2.Start();
            }

            private void func1()
            {
                DataClasses1DataContext dc = new DataClasses1DataContext();

                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable, Timeout = TimeSpan.MaxValue }))
                {
                    try
                    {
                        Employee em = (from employee in dc.GetTable<Employee>()
                                       where employee.id == 1
                                       select employee).SingleOrDefault();
                        em.name = "abc";

                        dc.SubmitChanges(); // Add breakpoint here
                    }
                    catch (ChangeConflictException ex)
                    {
                        dc.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
                    }

                    ts.Complete();
                }
            }

            private void func2()
            {
                DataClasses1DataContext dc = new DataClasses1DataContext();

                using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.Serializable, Timeout = TimeSpan.MaxValue }))
                {
                    try
                    {
                        Employee em = (from employee in dc.GetTable<Employee>()
                                       where employee.id == 1
                                       select employee).SingleOrDefault();
                        em.name = "123";

                        dc.SubmitChanges(); // Add breakpoint here
                    }
                    catch (ChangeConflictException ex)
                    {
                        dc.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
                    }

                    ts.Complete();
                }
            }
        }
    }

    I don't understand the reason a deadlock always occurred on one of the threads at SubmitChanges. Neither thread is waiting for any resource held by the other thread.How can I resolve this?

    A few other questions about transaction

    1. Shouldn't one of the threads gets blocked once it enters the transactionscope since the isolation level is set at serializable?

    2. Shouldn't the exception be ChangeConflictException instead?

     

    Thanks,

    chew

     

    Wednesday, October 13, 2010 3:28 PM

Answers

  • Here are the steps I would take.

    First, make sure your queries are optimized.  If you have two queries that take 20s execute right after each other, the third may deadlock.  If the timout is 30s.

    Second, catch a SqlException in the UI, and allow the user to try again.

    Third, I do believe there is a way to snapshot the database before a query.  To keep the editing seperate from the querying.  I would look into that.

    • Marked as answer by liurong luo Tuesday, October 19, 2010 9:34 AM
    Friday, October 15, 2010 3:08 PM
  • A bit odd but I guess what happens is:
    1) both reads get read locks on the record
    2) the first one that enters submitchanges and send an update statement will make SQL server want to escalate to an update lock. This fails due to another transaction having a read lock on the record.

    As a workaround, you may want to try putting an update lock in the beginning of the transaction:
    dc.ExecuteCommand("select id from employee with (rowlock,updlock) where id={0}", 1);

    Yes, that adds an extra roundtrip but may be necessary if you expect multiple users to attempt to work with the same records. Alternatively you can read the employee record through a view or stored procedure containing the update lock hint, to avoid adding an extra db roundtrip...

    Another alternative is to retrieve the employee record outside of the transaction, and only do the update/submit within the transaction. That would result in a change conflict exception instead of a tx deadlock...

    Some general recommendations on deadlocks: http://social.msdn.microsoft.com/Forums/en-us/adodotnetentityframework/thread/cdb207af-3de1-4afc-b049-7c9ded980138#152b6594-a49d-4889-8f3e-c76f00e2ef48


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by liurong luo Tuesday, October 19, 2010 9:34 AM
    Monday, October 18, 2010 2:34 AM
    Answerer

All replies

  • I am assuminig it is a test and not production, because you wouldn't have any benifit threading here unless your database has the proper setup.  I think this is a cool test, but I am not sure that it is a valid test.   I would guess by removing your break point it doesn't create a deadlock.

    The reason for the deadlock is that Sql on insert is locking the table Employees, and the second thread cannot get pass the lock so it times out.  The lock on the table, maybe timing out because of your debugging.

    Try testing it without debugging, and report back the results.

    Thursday, October 14, 2010 2:10 PM
  • Yes. It was a test. I'm trying to simulate the scenario where 2 users entering the transaction scope simultaneously. Anything is possible unless it is mutually exclusive. I didn't see any problem without the breakpoints.
    Thursday, October 14, 2010 2:39 PM
  • So the error does occur, or does not occur when you are not debugging?
    Thursday, October 14, 2010 2:42 PM
  • no. it didn't occur.
    Thursday, October 14, 2010 3:25 PM
  • So what are you actually trying to test here?  What is your goal?

    If you are getting dead locks I would try to figure out what queries you have that are taking a long time, and try to optimize them.

    It is really working as intended above.

    Thursday, October 14, 2010 4:13 PM
  • The purpose is to understand the problem, how to handle the exception and how to avoid the problem. The exception didn't occur without debugging doesn't implied it will never occur. How many times do you think I should test in order to convince myself there isn't such an error, 10 times, 1000 times, 1 million or even more? My purpose is to create a scenario where 2 users are trying to update the same record at the same time.
    Friday, October 15, 2010 12:40 AM
  • Here are the steps I would take.

    First, make sure your queries are optimized.  If you have two queries that take 20s execute right after each other, the third may deadlock.  If the timout is 30s.

    Second, catch a SqlException in the UI, and allow the user to try again.

    Third, I do believe there is a way to snapshot the database before a query.  To keep the editing seperate from the querying.  I would look into that.

    • Marked as answer by liurong luo Tuesday, October 19, 2010 9:34 AM
    Friday, October 15, 2010 3:08 PM
  • A bit odd but I guess what happens is:
    1) both reads get read locks on the record
    2) the first one that enters submitchanges and send an update statement will make SQL server want to escalate to an update lock. This fails due to another transaction having a read lock on the record.

    As a workaround, you may want to try putting an update lock in the beginning of the transaction:
    dc.ExecuteCommand("select id from employee with (rowlock,updlock) where id={0}", 1);

    Yes, that adds an extra roundtrip but may be necessary if you expect multiple users to attempt to work with the same records. Alternatively you can read the employee record through a view or stored procedure containing the update lock hint, to avoid adding an extra db roundtrip...

    Another alternative is to retrieve the employee record outside of the transaction, and only do the update/submit within the transaction. That would result in a change conflict exception instead of a tx deadlock...

    Some general recommendations on deadlocks: http://social.msdn.microsoft.com/Forums/en-us/adodotnetentityframework/thread/cdb207af-3de1-4afc-b049-7c9ded980138#152b6594-a49d-4889-8f3e-c76f00e2ef48


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Rule based class and property naming, Compare and Sync model <=> DB, Sync SSDL <=> CSDL (EF4)
     huagati.com/L2SProfiler - Query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by liurong luo Tuesday, October 19, 2010 9:34 AM
    Monday, October 18, 2010 2:34 AM
    Answerer