samedi 27 juin 2015

C# - Nested Transactions Insert Fails

I'm using nested transactions by using IDBConecction interface on c#. I have to methods that insert data into 2 different tables, but when it comes to the second insert the first insert transaction locks the second one causing a timeout exception.

public void FirstInsert(){
    using (var cn = new Connection().GetConnection()){
        cn.Open();
        using (var tran = cn.BeginTransaction()){
            try{
                //1st insert
                SecondInsert() //calling second insert method
                tran.Commit();                
            }
            catch{
                tran.Rollback();
            }
        }
    }
}

public void SecondInsert(){
    using (var cn = new Connection().GetConnection()){
        cn.Open();
        using (var tran = cn.BeginTransaction()){
            try{
                //2nd insert, this one fails
                tran.Commit();                
            }
            catch{
                tran.Rollback();
            }
        }
    }
}

When I check on SqlServer fisrt insert has the SPID 56, then when the second insert is being performed with SPID 57, and I use

exec sp_who2

In the column "BlkBy" for SPID 57 it says it is blocked by SPID 56.

How can I overcome these problem?

Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire