CODEWITHSUNDEEP
c#sqltransactions
Grant H.
Grant H.

Reputation: 3717

TransactionScope unintentionally locks parts of the database

I have a web application that many users use at any given moment. From a DB perspective, they are constantly writing and reading data from any number of tables as they move through the system. In addition, we have a number of processes that import data into this system.

Today, while debugging an import process, my code stopped on an exception while in the middle of a transaction. Almost immediately I started getting reports of queries failing for users in the system. Somehow I associated my actions with it, hit Continue, and suddenly the queries started working again.

In short, I think the way I set up my transaction is responsible for this, but I don't understand the impact of it well enough to work around it.

I need a transaction so I can roll it back if anything goes wrong, but it appears to be locking the database while the scope is active or "hung". Is there a way I can change this to prevent the issue I'm seeing? 

using (TransactionScope scope = new TransactionScope(
          TransactionScopeOption.Required,
          System.TimeSpan.MaxValue))
            {
                using (var context = new EntityContext())
                {
                     //lots of write operations in here
                }
            }

Upvotes: 2

Views: 460

Answers (1)

usr
usr

Reputation: 171246

it appears to be locking the database while the scope is active or "hung"

Whether the thread in a given scope is running or not does not matter. You are correct, though, in that an active transaction can hold locks.

What locks are being held depends entirely on your workload and RDBMS. There is no magic "don't lock" option. The locks are there for a reason.

Given the fact that the question does not have information about the queries being run, the schema and the RDBMS I can only encourage you to make yourself familiar with locking in your RDBMS. There's no easy solution.

If you're using SQL Server, you should probably investigate the use of SNAPSHOT isolation for reading transactions. This can be a quick and comprehensive fix for blocking issues.

Upvotes: 3

Related Questions