score:2

Yes, if you start a transaction that manipulates lots of records, and takes a long time to complete, then as a direct result competing operations will be blocked. This is especially true for "serializable" transactions, since they take the most locks (including key-range locks etc). This is the nature of a transaction; it is the I in ACID.

Options:

  • don't do everything in one huge transaction
  • have your read operations intentionally read past the locks (this is hugely double-edged - can be fine, but can cause big problems - treat with caution) - for example NOLOCK or READ UNCOMMITTED.
  • on full SQL server (not CE), try using snapshot isolation

score:1

using (var trans = new TransactionScope(
 TransactionScopeOption.Required, 
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadUncommitted
    }
))
{
    // Your LINQ to SQL query goes here where you read some data from DB
}

while updating tables (inserting, deleting or updating), they become locked, so if you want to read the data, which is not yet commit, so, you can use Transaction IsolationLevel.ReadUncommitted to allow dirty reads

score:0

Did you tried this ?

transactionOptions.Timeout = TransactionManager.MaximumTimeout;

Related Articles