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?
Use one connection for both operations. This likely involves passing the connection object around.
Usually, the connection+transaction per request pattern solves this issue well. Opening a connection in all kinds of methods is a code smell. It shows that the infrastructure fails to handle that.