I have a child TransactionScope within a parent TransactionSope. The child TransactionScope is created, executed, and committed multiple times under the singular parent TransactionScope.
The parent TransactionScope takes care of Insert a single record into the database while waiting for the the second set of insert statements to complete execution one-by-one.
After completing the first transaction, on the first insert of the child transactions there was a problem. After a whole lot of research since yesterday, I would out now that the process that the second insert is running on is block by the first insert's process.
I found this out by running SP_WHO2 in sql server with the program was running.
Meanwhile, there is a one-many relationship between the two table involved in the insertion process. The first insert performs it's operation on the parent table while the second will be on the child table.
Whenever i remove the relationship constraint between the two tables the transaction goes through, but doesn't when the constraint is on.
My question is how to unblock the second process which is blocked by the first insert?