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.

Legitimize

What is your intention here? No not take the lock when updating? or for other readers to ignore the locks? (which are there for very good reasons) – Milson

I do not want to have lock in transacted table for other users....the transaction can go for long duration because at the time of transaction, ten of thousands data inserts into the table so it probably takes long time...So, at period of transaction, I want other user to have access on that table and can read data....... – Legitimize

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.

Milson

your second solution is pretty much what the snapshot isolation is doing for you when its used for a transaction that performs writes ;) – Kure

I have used Snapshot Isolation level but again it gives same problem....Actually it works when I use "select * from tbl_name with (nolock)" but this select is not appropriate for me because of: 1. It already displays the data of transaction which is yet to be committed. 2. the table already got queried from application so it is not feasible for me to go through all query in code and change it...... – Legitimize

@akash88 it is the reader that needs to use snapshot isolation; if it didn't help, then I wonder if you simply hadn't enabled snapshot isolation. But fundamentally, you are trying to do something that is against the system. An example equivalent might be "I have a large file open while I rewrite large chunks of it; I don't want to allow shared access because then the readers would see the in-progress edits; but not the file is locked until I close it! How do I fix this?" answer: you don't; you design the system differently. – Milson

@akash88 you will need to provide more detail, specifically where you use which transactions/isolation levels. Marc already answered the question with the necessary detail, if it does not work you are not following his guidelines or using the c# classes in a not intended manner – Kure

Answer 2

Try to execute your reads within a transaction as well and use the isolation level READ UNCOMMITTED. This will prevent the read from being locked, but might produce invalid results:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED    
BEGIN TRANSACTION

SELECT * FROM Table

COMMIT TRANSACTION

There is a misconception that dealing with transactions/isolation levels only matters when writing, when in fact it is equally important when reading.

Kure

Thank u for response.....I have used all type of isolation level but it could worked out with my scenario...Actually, I am using it in C# using BeginTransaction() method of SqlTransaction....I want to give access to other user for those table which are susing in transaction.....But,in every isolation level, exclusive lock is happening........ – Legitimize

If you are writing to the table exclusive locks are mandatory. The only exception is when you use the isolation level snapshot, but as far as I know this is only available starting from sql 2008. So the only way to allow reads is by using read uncommited or chaos isolation level during reads as outlined above – Kure

Just checked, snapshot isolation is available in SQL 2005 already, so there you go. Use snapshot isolation when writing and you should be good to go. Since you only seem to be reading in the other operations there should be no issues with that. – Kure

I have used Snapshot Isolation level but again it gives same problem....Actually it works when I use "select * from tbl_name with (nolock)" but this select is not appropriate for me because of: 1. It already displays the data of transaction which is yet to be committed. 2. the table already got queried from application so it is not feasible for me to go through all query in code and change it...... – Legitimize

Answer 3

@AKASH88, SNAPSHOT isolation level is what you are looking for.

You say that even with SNAPSHOT it is not working as expected, exclusive lock is happening, I can understand that, I had the same issue.

Make sure you don't just enable SNAPSHOT on the database options, but also READ COMMITTED SNAPSHOT must be turned on.

This is SQL Server 2008, so it's still uncertain if this answer will help :(

Best regards!

Goodson

Answer 4

The problem is not on the level of writing into database but on the level of reading values. You are trying to read values that are inserting. Try to change your select query to following:

select * from your_table_with_inserts with (nolock)

however this one overrides isolation level of current transaction and can cause dirty reads.

So the question is : if you are using transaction on all queries or only insert/update?

Berwick