CODEWITHSUNDEEP
c#linqtransactionssql-server-ce
Benjamin
Benjamin

Reputation: 185

Transaction and TransactionScope isolation

I have to add to my database a lot of information. Adding this information takes about 5–7 minutes. And I need to add transaction.

I tried this:

try { 
    db.Connection.Open();
    db.Transaction = db.Connection.BeginTransaction(); 
    UpdateTable1();
    UpdateBigTable2();
    ...
    db.Transaction.Commit(); 
} catch {
    db.Transaction.Rollback();  
}

But when my database is updating, I cannot read or do anything with my DB.

I tried to set IsolationLevel, but any of it didn't help.

I tried this:

using (var ts = new TransactionScope()) {
    UpdateTable1();
    ts.Complete();
}

But program crashes after 2–3 minutes.

This solution didn't help too:

var transactionOptions = new TransactionOptions();
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
transactionOptions.Timeout = TimeSpan.MaxValue;
using (var ts = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
{
    ...
}

I have get access to my database, when it's updating, if I set TransactionScopeOption.Suppress But in this case Transaction doesn't work.

Upvotes: 2

Views: 6647

Answers (3)

Engin Ardıç
Engin Ardıç

Reputation: 2469

Did you tried this ?

transactionOptions.Timeout = TransactionManager.MaximumTimeout;

Upvotes: 0

m4ngl3r
m4ngl3r

Reputation: 560

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

Upvotes: 1

Marc Gravell
Marc Gravell

Reputation: 1064004

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

Upvotes: 2

Related Questions