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