Issue with Transactions and IEnlistmentNotification

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 that in 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

Answer 1

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

Is it implemented as server-side stored procedure?

You should do everything on the server-side what can be done on the server-side. Do only on the client-side what is not doable on the server-side.

Article: "Although a stored procedure contains SQL commands, once
compiled it will interact with SQL Server very differently from the way
individual SQL statements (such as those passed from an ADO.NET command object) do. One of the key changes is that the SQL commands in a stored procedure are within transaction scope, which means that either all of the
SQL statements in a stored procedure will execute, or none will. This is known
as atomicity."

http://www.java-samples.com/showtutorial.php?tutorialid=1000

We can assist you better on the server-side as well.

Thanks.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

When you start the transaction with SqlTransaction.BeginTransaction() it does not change your transaction isolation level. 

When you start the transaction with System.Transactions.TransactionScope(), it defaults to SERIALIZABLE.  See:

Using new TransactionScope() Considered Harmful

You should probably stick with TransactionScope() and set the isolation level correctly. 

David

---

David http://blogs.msdn.com/b/dbrowne/