Isolation level in Sql Transaction

Question

I have implemented SqlTransaction in c# to begin, commit and rollback transaction. Everything is going right, but I've got some problem while accessing those tables which are in connection during transaction. I was not able to read table during the transaction(those table which are in transaction). While searching about this, I found that it happens due to an exclusive lock. Any subsequent selects on that data in turn have to wait for the exclusive lock to be released. Then, I have gone through every isolation level provided by SqlTransaction, but it did not work. So, I need to release exclusive lock during transaction so that other user can have access on that table and can read the data. Is there any method to achieve this? Thanks in advance.

Here's my c# code for the transaction

try
{
  SqlTransaction transaction = null;                         
  using (SqlConnection connection=new SqlConnection(Connection.ConnectionString))
  {
       connection.Open();
       transaction=connection.BeginTransaction(IsolationLevel.Snapshot,"FaresheetTransaction");            
       //Here all transaction occurs   
       if (transaction.Connection != null)
       {     
               transaction.Commit();
               transaction.Dispose();
       } 
   }
}
catch (Exception ex)
{
   if (transaction.Connection != null)
       transaction.Rollback();
   transaction.Dispose();   
}   `                         

This code is working fine, but the problem is that when I access the data of tables (those accessed during the transaction) during the time of transaction. The tables are being accessed by other parts of the application. So, when I tried to read data from the table, it throws an exception.

Answer 1

A SQL transaction is, by design, ACID. In particular, it is the "I" that is hurting you here - this is designed to prevent other connections seeing the inconsistent intermediate state.

An individual reading connection can elect to ignore this rule by using the NOLOCK hint, or the READ UNCOMMITTED isolation level, but it sounds like you want is for the writing connection to not take locks. Well, that isn't going to happen.

However, what might help is for readers to use snapshot isolation, which achieves isolation without the reader taking locks (by looking at, as the name suggests, a point-in-time shapshot of the consistent state when the transaction started).

However, IMO you would be better advised to look at either:

• multiple, more granular, transactions from the writer
• performing the work in a staging table (a parallel copy of the data), then merging that into the real data in a few mass-insert/update/delete operations, minimising the transaction time

The first is simpler.

The simple fact is: if you take a long-running transaction that operates on a lot of data, yes you are going to be causing problems. Which is why you don't do that. The system is operating correctly.