Is it possible to select data while a transaction is occuring?

Question

I am using transactionscope to ensure that data is being read to the database correctly. However, I may have a need to select some data (from another page) while the transaction is running. Would it be possible to do this? I'm very noob when it comes to databases.

I am using LinqToSQL and SQL Server 2005(dev)/2008(prod).

Answer 1

Yes, it is possible to still select data from a database while a transaction is running.

Data not affected by your transaction (for instance, rows in a table which are being not updated) can usually be read from other transactions. (In certain situations SQL Server will introduce a table lock that stops reads on all rows in the table but they are unusual and most often a symptom of something else going on in your query or on the server).

You need to look into Transaction Isolation Levels since these control exactly how this behaviour will work.

Here is the C# code to set the isolation level of a transaction scope.

TransactionOptions option = new TransactionOptions();        
options.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;        
using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required, options)
{
    // Code within transaction
}

In general, depending on the transaction isolation level specified on a transaction (or any table hints like NOLOCK) you get different levels of data locking that protect the rest of your application from activity tied up in your transaction. With a transaction isolation level of READUNCOMMITTED for example, you can see the writes within that transaction as they occur. This allows for dirty reads but also prevents (most) locks on data.

The other end of the scale is an isolation level like SERIALIZABLE which ensures that your transaction activity is entirely isolated until it has comitted.

Answer 2

Yes, by default a TransactionScope will lock the tables involved in the transaction. If you need to read while a transaction is taking place, enter another TransactionScope with TransactionOptions IsolationLevel.ReadUncommitted:

TransactionScopeOptions = new TransactionScopeOptions();
options.IsolationLevel = IsolationLevel.ReadUncommitted;
using(var scope = new TransactionScope(
    TransactionScopeOption.RequiresNew, 
    options
) {
    // read the database
}

With a LINQ-to-SQL DataContext:

// db is DataContext
db.Transaction = 
    db.Connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted);

Note that there is a difference between System.Transactions.IsolationLevel and System.Data.IsolationLevel. Yes, you read that correctly.

Answer 3

In adition to the already provided advice, I would strongly recommend you look into snapshot isolation models. There is a good discussion at Using Snapshot Isolation. Enabling Read Committed Snapshot ON on the database can aleviate a lot of contention problems because readers are no longer blocked by writers. Since default reads are performed under read commited isolation mode, this simple database option switch has immediate benefits and requires no changes in the app.

There is no free lunch, so this comes at a price, in this case the price being aditional load on tempdb, see Row Versioning Resource Usage.

If howeever you are using explict isolation levels and specially if you use the default TransactionScope Serializable mode, then you'll have to review your code to enforce the more bening ReadCommited isolation level. If you don't know what isolation level you use, it means you use ReadCommited.