Hi,

We've been using Transactions mostly to create 'atomic' logic, so to be sure either everything succeeds or everything fails. Nothing in between. Our target isn't really locking because of concurrency problems, but really because of the atomicy.

Now we've been reading the documentation about the different levels but can't really come to the conclusion which level to use. We would prefer a level that takes as little (or none) locks as possible and really is only used for what I stated above.

Any suggestion on what level to use or what additional documentation to read on this?

Thanks a lot.

Levels are about isolation levels to connections outside the transaction. The transaction isolation levels available are the ones available through ADO.NET which are equal to the isolation levels available in the SQL Server database.

More info about the isolation levels is available in Books online of SQL Server. Did you consult these docs?

If you're using another DB than SQL server, you should read up about the isolation levels of that particular DB, but changes are you either have no / crappy isolation (mysql) or no problems with writers blocking readers (postgresql, oracle, db2)

Hi,

I've been further studying on this and came to the conclusion that I didn't expect:

If I create a transaction with isolation level ReadUncommitted and insert a entity it will still lock reads on the whole table (GetMulti(null)) by other threads / code not using that transaction. If I however do it from another transaction that's also set to be ReadUncommited I can read it directly.

This is a problem for us since our system has multiple users and one of the actions we allow is the importing of files which can take up quite some time. We need to do that in a transaction to be sure that either the whole file succeeds and commits or we can revert everything if an error occurs. But this means no other user can access data in the tables used in the import.

Therefore I have 2 questions: 1. Can we set all reads without a transaction to default to ReadUncommited (we take the risks of that over the locking issues we have now) 2. What is the default transaction type used when doing a recursive save? And can we alter that to a default as well?

More on my investigation:

We're thinking about using this:

There is an undocumented feature in the SqlServerDQE (undocumented because I don't want people to abuse it, but it's there for emergencies). Code:

SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;

http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=20297&ThreadID=2482

The above is in contrast to what I interpreted from:

If you don't want a transaction to lock out other users, create the transaction with the ReadUncommitted isolation level, for example: adapter.StartTransaction(...) // do things here, also recursive saves adapter.Commit();

this will make sure your transaction won't lock out readers. Though this shouldn't be necessary: row changes/deletes should be done at the end of a routine so the changes can be pushed to the database and the time readers are locked out is minimized. I mean by this that should avoid starting a transaction, do a lot of reading, open forms wait for user input etc. etc. and after that committing it. Transactions should be started right before some activity on the database has to be performed and should be closed right after that and reads should preferably be done outside the transaction.

http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=15256&ThreadID=2721

The code example demonstrates the behaviour I mentioned:

    // Low time out, early notice of locks
    CommonDaoBase.CommandTimeOut = 1;

    Transaction transactionReadUncommited = new Transaction(IsolationLevel.ReadUncommitted, "Transaction 1");

    // Insert an Entity using the Transaction
    AgeEntity age = new AgeEntity();
    age.Age = 18;
    transactionReadUncommited.Add(age);
    age.Save();

    AgeCollection ages = null;
    try
    {
        // Try to get all rows of the Age table without a Transaction
        ages = new AgeCollection();
        ages.GetMulti(null);
    }
    catch
    { 
        // We'll come here, because we get a timeout due to the lock
    }

    Transaction transactionReadUncommited2 = new Transaction(IsolationLevel.ReadUncommitted, "Transaction 2");
    try
    {
        // Try to get all rows of the Age table with a second Transaction, also read uncommited             
        ages = new AgeCollection();
        transactionReadUncommited2.Add(ages);
        ages.GetMulti(null);
    }
    catch
    { 
        // We don't come here, we can read all the rows.
    }
    finally
    {
        transactionReadUncommited2.Dispose();
    }

    transactionReadUncommited.Commit();
    transactionReadUncommited.Dispose();

gabrielk wrote:

Hi,

I've been further studying on this and came to the conclusion that I didn't expect:

If I create a transaction with isolation level ReadUncommitted and insert a entity it will still lock reads on the whole table (GetMulti(null)) by other threads / code not using that transaction. If I however do it from another transaction that's also set to be ReadUncommited I can read it directly.

This is a problem for us since our system has multiple users and one of the actions we allow is the importing of files which can take up quite some time. We need to do that in a transaction to be sure that either the whole file succeeds and commits or we can revert everything if an error occurs. But this means no other user can access data in the tables used in the import.

Therefore I have 2 questions: 1. Can we set all reads without a transaction to default to ReadUncommited (we take the risks of that over the locking issues we have now)

Yes, Use the nolock hint as described below. After this, all selects will use NOLOCK, which effectively comes down to it will read regardless of whatever lock level there's set. This isn't recommended of course.

1. What is the default transaction type used when doing a recursive save? And can we alter that to a default as well?

Readcommitted. No, you can't change it, other than starting the transaction manually, and specify the isolation level. Remember that the isolation level you select is for the transaction and the work as it appears to the outside world. So all actions INSIDE the transaction will be able to do anything, but the work will set locks at the level that the outside world sees the changes according to the isolation level set.

As SQL Server doesn't have MVCC in normal isolation levels, there's always some locking involved. There are always read locks, which will make OTHER transactions not being able to WRITE, as readers block writers.

You could look into snapshot isolation though (for the import). That way the transaction takes place in total isolation of other threads. Did you try that?

Long-running transactions in a system where readers block writers and vice versa (== sql server) it can be a challenge...

More on my investigation: We're thinking about using this:

There is an undocumented feature in the SqlServerDQE (undocumented because I don't want people to abuse it, but it's there for emergencies). Code: SD.LLBLGen.Pro.DQE.SqlServer.DynamicQueryEngine.UseNoLockHintOnSelects = true;

http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=20297&ThreadID=2482