CODEWITHSUNDEEP
c#sqlsql-server-2005transactionssqlconnection
user1082916
user1082916

Reputation:

Isolation level in Sql Transaction

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.

Upvotes: 10

Views: 24645

Answers (4)

Agustin Garzon
Agustin Garzon

Reputation: 315

@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.

enter image description here

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

Best regards!

Upvotes: 3

Marc Gravell
Marc Gravell

Reputation: 1062520

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.

Upvotes: 16

Marcin
Marcin

Reputation: 3262

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?

Upvotes: 2

ntziolis
ntziolis

Reputation: 10221

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.

Upvotes: 3

Related Questions