none
Typed Datasets and SQL deadlocks RRS feed

  • Question

  • Hi!

    I have been encountering an issue with typed datasets and deadlocks in my SQL server. Here is an overly simplified scenario: my database has 2 tables, TableA and TableB. TableA has 2 columns, ID (PK) and Status. TableB has 3 columns: ID (PK), TableAID (FK) and Status. The FK has ON UPDATE: CASCADE. I have left all Lock types and Isolation levels at their default values. I also have a typed dataset that contains typed datatables to represent the same layout. I have 2 threads, each with their own datasets. Both threads constantly update a single value. ThreadA modifies TableA.Status, and ThreadB modifies TableB.Status. After each modification, the threads update their value to the DB. This should not cause deadlocks, as I never try to modify any ID or the FK column.

    This scenario will quikcly result in SQL throwing a deadlock exception. A quick 1222 trace reveals that each thread is holding its table's PK lock and trying to access the other table's PK lock. I am assuming that the problem comes from the fact that TableAdaptors will update every single value of a row that is detected has having changes. Therefore, when I change Status on either table, the TableAdaptor's UPDATE command also updates the ID column. This triggers the ON CASCADE on the parent, and on the child the UPDATE needs to verify that there is a valid Parent ID. I can see why this would cause a deadlock.

    Now I was able to stop getting these exceptions by changing ON UPDATE to NO ACTION on the FK.

    My question is two-fold: First, is MS aware of this issue? Second, is there a better workaround? I rather enjoy ON UPDATE: CASCADE since it reduces the amount of things programmers have to remember, so working it back in my app would be great.

    Friday, May 13, 2011 7:39 PM

Answers

  • Hi Billy,

    Welcome!

    According to your description, I think you can use separated tableadapter, the deadlock doesn't happen on my computer, I will show my test code:

    Create table TableA(
    ID int primary key,
    [Status]nvarchar(20) not null
    )
    create table TableB(
    ID int primary key,
    TableAID int not null,
    [Status] nvarchar(20) not null
    )
    
    Alter table TableB
    add constraint FK_TableAB
    foreign key(TableAID) references TableA(ID)
    ALTER TABLE [dbo].[TableB] CHECK CONSTRAINT [FK_TableAB]
    
    Insert into TableA values(1,'true')
    insert into TableB values(1,1,'false')
    
    select * from TableA
    select * from TableB
    

    --------------------------------------------------------------------------------

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading;
    namespace Cascade
    {
      class Program
      {
        DataSet1 ds = new DataSet1();
        static void Main(string[] args)
        {     
          Thread thread1 = new Thread(new Program().UpdateTableA);
          Thread thread2 = new Thread(new Program().UpdateTableB);
          thread1.Start();
          thread2.Start();
        }
         void UpdateTableA()
        {    
          DataSet1TableAdapters.TableATableAdapter A = new DataSet1TableAdapters.TableATableAdapter();
          A.Fill(ds.TableA);
          DataSet1.TableARow row = (DataSet1.TableARow)ds.TableA.Rows[0];
          row.Status = "helloA";
          A.Update(ds.TableA);
        }
         void UpdateTableB()
        {    
          DataSet1TableAdapters.TableBTableAdapter B = new DataSet1TableAdapters.TableBTableAdapter();
          B.Fill(ds.TableB);
          DataSet1.TableBRow row = (DataSet1.TableBRow)ds.TableB.Rows[0];
          row.Status = "helloB";
          B.Update(ds.TableB);
        }
      }
    }
    
    

    I think your explaination is make sense.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 17, 2011 1:46 PM
    Moderator

All replies

  • Hi Billy,

    Welcome!

    According to your description, I think you can use separated tableadapter, the deadlock doesn't happen on my computer, I will show my test code:

    Create table TableA(
    ID int primary key,
    [Status]nvarchar(20) not null
    )
    create table TableB(
    ID int primary key,
    TableAID int not null,
    [Status] nvarchar(20) not null
    )
    
    Alter table TableB
    add constraint FK_TableAB
    foreign key(TableAID) references TableA(ID)
    ALTER TABLE [dbo].[TableB] CHECK CONSTRAINT [FK_TableAB]
    
    Insert into TableA values(1,'true')
    insert into TableB values(1,1,'false')
    
    select * from TableA
    select * from TableB
    

    --------------------------------------------------------------------------------

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading;
    namespace Cascade
    {
      class Program
      {
        DataSet1 ds = new DataSet1();
        static void Main(string[] args)
        {     
          Thread thread1 = new Thread(new Program().UpdateTableA);
          Thread thread2 = new Thread(new Program().UpdateTableB);
          thread1.Start();
          thread2.Start();
        }
         void UpdateTableA()
        {    
          DataSet1TableAdapters.TableATableAdapter A = new DataSet1TableAdapters.TableATableAdapter();
          A.Fill(ds.TableA);
          DataSet1.TableARow row = (DataSet1.TableARow)ds.TableA.Rows[0];
          row.Status = "helloA";
          A.Update(ds.TableA);
        }
         void UpdateTableB()
        {    
          DataSet1TableAdapters.TableBTableAdapter B = new DataSet1TableAdapters.TableBTableAdapter();
          B.Fill(ds.TableB);
          DataSet1.TableBRow row = (DataSet1.TableBRow)ds.TableB.Rows[0];
          row.Status = "helloB";
          B.Update(ds.TableB);
        }
      }
    }
    
    

    I think your explaination is make sense.

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 17, 2011 1:46 PM
    Moderator
  • Your posted code is my current workaround.

    I modified your code to reproduce the issue:

    Create table TableA(
    
    ID int primary key,
    
    [Status] bit
    
    )
    
    create table TableB(
    
    ID int primary key,
    
    TableAID int not null,
    
    [Status] bit
    
    )
    
    
    
    ALTER TABLE dbo.TableB ADD CONSTRAINT
    
    	FK_TableB_TableA FOREIGN KEY
    
    	(
    
    	TableAID
    
    	) REFERENCES dbo.TableA
    
    	(
    
    	ID
    
    	) ON UPDATE CASCADE 
    
    	 ON DELETE CASCADE 
    
    
    
    Insert into TableA values(1,'true')
    
    insert into TableB values(1,1,'false')
    
    
    using System;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Text;
    
    using System.Threading;
    
    
    
    
    
    namespace Deadlocks
    
    {
    
     class Program
    
     {
    
      DataSet1 ds = new DataSet1();
    
      static void Main(string[] args)
    
      {
    
       Thread thread1 = new Thread(new Program().UpdateTableA);
    
       Thread thread2 = new Thread(new Program().UpdateTableB);
    
       thread1.Start();
    
       thread2.Start();
    
      }
    
      void UpdateTableA()
    
      {
    
       DataSet1TableAdapters.TableATableAdapter A = new DataSet1TableAdapters.TableATableAdapter();
    
       A.Fill(ds.TableA);
    
       DataSet1.TableARow row = (DataSet1.TableARow)ds.TableA.Rows[0];
    
       while (true)
    
       {
    
        row.Status = !row.Status;
    
        A.Update(ds.TableA);
    
       }
    
      }
    
      void UpdateTableB()
    
      {
    
       DataSet1TableAdapters.TableBTableAdapter B = new DataSet1TableAdapters.TableBTableAdapter();
    
       B.Fill(ds.TableB);
    
       DataSet1.TableBRow row = (DataSet1.TableBRow)ds.TableB.Rows[0];
    
       while (true)
    
       {
    
        row.Status = !row.Status;
    
        B.Update(ds.TableB);
    
       }
    
      }
    
    
    
     }
    
    }
    
    
    
    

     Notice differences: I added the "ON UPDATE: CASCADE" property, which I believe is central to the issue. I also put the threads in infinite loops. I also changed the type of Status to make it easier for the infinite loops, but that last part has no bearing on the issue.

    My posted code deadlocked in less than a second.


    EDIT: I noticed that the posted code uses a single DataSet for both threads. Before anyone starts pointing the finger there, I have tested with each thread owning its own DataSet, and the problem still occurs.
    Tuesday, May 17, 2011 7:06 PM
  • Hi Billy,

    Thanks for your feedback!

    I test your code on my computer, unfortunatly it works ok, there is no deadlock. My environment is vs2010. Would you please try it again or try other computer.

    Thanks for understanding.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 19, 2011 5:39 AM
    Moderator
  • That's unfortunate. I use VS2008, .Net 3.5 SP1, and SQL 2005. I will have to retest once I obtain newer versions.

    Are you sure you set the foreign key properly? For the deadlock to occur, you MUST have ON UPDATE: Cascade in the database.

    Thursday, May 19, 2011 1:20 PM
  • Hi Billy,

    I'm sure! I copy your code(T-sql) in SQL Server2008R2 to create my database.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, May 20, 2011 7:21 AM
    Moderator
  • Hi Billy,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 
    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 31, 2011 3:37 AM
    Moderator
  • Your workaround works, although it disables the ON UPDATE feature which is unfortunate. I have not tested my code on SQL Serv 2008 so this issue might not exist anymore.

    Thanks for your time!

    Thursday, June 2, 2011 3:39 PM