none
Issue with Transactions and IEnlistmentNotification and isolation level RRS feed

  • Question

  • Hello All,

    Facing a strange issue with database and .Net TransactionScope().

    Here is the scenario:

    I have a insert statement and inside the insert statement I have some select statements on the same data-range which is being done in the insert.

    I am using .Net TransactionScope() around that insert:

    So basically:

    using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))
    {
    
       // Do some search
       // FireSearch();
       // FireInsert()
       
       scope.Complete();
    }

    Now, I see that sometimes two parallel threads running this above insert scenario is getting deadlocked (which is what I expect) due to  Key-Range Locking as I can see thatin the deadlock graph (the transaction isolation level is SERIALIZABLE)

    But, now I am using the following IEnlistmentNotification implementation while doing the dbConnection.Open() for the insert:

            private class EnlistTransaction : IEnlistmentNotification
    	{
    			DbConnection conn;
    			DbTransaction trans;
    			string identifier;
    			public EnlistTransaction(DbConnection conn)
    			{
    				this.identifier = Petrel.Transactions.Transaction.Current.TransactionInformation.LocalIdentifier;
    				this.conn = conn;
    				this.trans = conn.BeginTransaction();
    			}
    			public void ConnectTransaction(DbCommand command)
    			{
    				command.Transaction = trans;
    			}
    			#region IEnlistmentNotification implementation
    			public void Commit (Enlistment enlistment)
    			{
    				Trace.Log.DebugFormat("committing transaction {0}", this.identifier);
    				try
    				{
    					this.trans.Commit();
    					enlistment.Done();
    				}
    				catch(Exception e)
    				{
    					Trace.Log.FatalFormat("Commit of the transaction failed! Is the transaction not completely committed? Exception: ", e);
    				}
    				finally
    				{
    					try { this.conn.Dispose(); }catch(Exception){}
    				}
    			}
    
    			public void InDoubt (Enlistment enlistment)
    			{
    				ConnectionState state = ConnectionState.Broken;
    				try{ state = this.conn.State; }catch(Exception){}//can give exception!
    				if(state == null || state != ConnectionState.Open && state != ConnectionState.Fetching && state != ConnectionState.Executing)
    				{
    					Trace.Log.Warn("Transaction is in doubt, but single transaction was already committed. This means that not all information is rolled back.");
    					enlistment.Done();
    				}
    				else
    					this.Rollback(enlistment);
    			}
    
    			public void Prepare (PreparingEnlistment preparingEnlistment)
    			{
    				if(connections.ContainsKey(this.identifier))
    					connections.Remove(this.identifier);
    				Trace.Log.DebugFormat("preparing transaction {0}", this.identifier);
    			    preparingEnlistment.Prepared();
    			}
    
    			public void Rollback (Enlistment enlistment)
    			{
    				try
    				{
    			        this.trans.Rollback();
    				}
    				catch(Exception e)
    				{
    					Trace.Log.FatalFormat("Rollback of the transaction failed! Is the transaction not rolled back now? Exception: ", e);
    				}
    				finally
    				{
                        try
                        {
                            if (!(conn is System.Data.SqlClient.SqlConnection))
                                this.conn.Dispose();
                        }
                        catch (Exception)
                        {
                        }
    					enlistment.Done();
    				}
    			}
    			#endregion			
    		}
    		

    and I enlist those Insert transaction like this after opening the connection:

    EnlistTransaction trans = new EnlistTransaction(dbConnection);
    Petrel.Transactions.Transaction.Current.EnlistVolatile(trans, EnlistmentOptions.None);
    trans.ConnectTransaction(command);

    Now the issue is using the above IEnlistmentNotification implementation I don't see deadlock any more!!! :)

    Earlier while running this query, without the above IEnlistmentNotification implementation, I could see that the Serializable transaction level being used in the database server, using the following query:

    select session_id, isolation_level =
          CASE transaction_isolation_level 
             WHEN 1 THEN 'ReadUncomitted'
             WHEN 2 THEN 'ReadCommitted'
             WHEN 3 THEN 'Repeatable'
             WHEN 4 THEN 'Serializable'
             WHEN 5 THEN 'Snapshot'
          END, login_name
    from sys.dm_exec_sessions
    where session_id > 49

    Now I only see ReadCommitted when I run the above query.

    Can someone please explain why is this happening?? 

    Regards,

    Deb


    Senior Software Engineer

    Monday, January 21, 2013 1:46 PM

All replies

  • Hi,

    Could you please provide the specific error message?

    Thursday, January 24, 2013 6:05 AM
  • For TransactionScope, the default isolation level is set to Serializable. You can refer below document for more information:

    I can not post link here, but you can search with below key words:

    Implementing an Implicit Transaction using Transaction Scope

    Especially below section. You can change it to readcommitted in your constructor to change the isolation level. If you use IEnlistmentNotification implementation, it will use Read Committed as default isolation level.

    Setting the TransactionScope isolation level

    Some of the overloaded constructors of TransactionScope accept a structure of type TransactionOptions to specify an isolation level, in addition to a timeout value. By default, the transaction executes with isolation level set to Serializable. Selecting an isolation level other than Serializable is commonly used for read-intensive systems. This requires a solid understanding of transaction processing theory and the semantics of the transaction itself, the concurrency issues involved, and the consequences for system consistency.

    In addition, not all resource managers support all levels of isolation, and they may elect to take part in the transaction at a higher level than the one configured.

    Monday, February 4, 2013 12:18 PM