SQL connection cannot unenlist from transaction

SQL connection cannot unenlist from transaction by Mark A. Nicholson

Closed

as Fixed

Type:	Bug
ID:	477809
Opened:	7/28/2009 12:50:18 AM
Access Restriction:	Public

Workaround(s)

view

User(s) can reproduce this bug

The documentation on SqlConnection, transaction binding, "Explicit Unbind" is incorrect.

The documentation states the following: "Explicit Unbind: Causes the connection to remain attached to the transaction until the connection is closed or until EnlistTransaction is called with a null reference (Nothing in Visual Basic) value. An InvalidOperationException is thrown if Current is not the enlisted transaction or if the enlisted transaction is not active. This behavior enforces the strict scoping rules required for TransactionScope support."

This suggests that it is possible to unenlist from a transaction using SqlConnection.EnlistTransaction(null);

This is NOT possible. Once a SqlConnection is enlisted in a transaction it can never be unelisted from a transaction using SqlConnection.EnlistTransaction(null). The following are the only ways a SqlConnection can unenlist from a transaction:

1. "Transaction Binding=Implicit Unbind" is specified in the connection string and the transaction ends, either by being committed, aborted or timing out, or the SqlConnection is closed (SqlConnection.Close) or disposed (SqlConnection.Dispose).

2. "Transaction Binding=Explicit Unbind" is specified in the connection string and the SqlConnection is closed (SqlConnection.Close) or disposed (SqlConnection.Dipose).

Details (expand)

English

.NET Framework 4 Beta 1

Windows 7

English

Create a console application using Visual Studio 2010. Uncomment each of the numbered comments in succession to prove that you can never unenlist a SqlConnection object from a transaction when "Transaction Binding=Explicit Unbind" has been specified in the connection string.

using System.Data.SqlClient;
using System.Transactions;

namespace ConsoleApplication4
{
class Program
{
static void Main()
{
var connectionStringBuilder = new SqlConnectionStringBuilder();
connectionStringBuilder.DataSource = "(local)";
connectionStringBuilder.InitialCatalog = "master";
connectionStringBuilder.IntegratedSecurity = true;
connectionStringBuilder.TransactionBinding = "Explicit Unbind";
connectionStringBuilder.Enlist = true;

using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
{
using (var transaction1 = new TransactionScope())
{
// Open the connection. This will enlist in the current
// transaction scope.
connection.Open();

// 1. Uncomment this line to attempt to unenlist from the transaction at this point.
//connection.EnlistTransaction(null);

// Complete the transaction.
transaction1.Complete();

// 2. Uncomment this line to attempt to unenlist from the transaction at this point.
//connection.EnlistTransaction(null);
}

// 3. Uncomment this line to attempt to unenlist from the transaction at this point.
//connection.EnlistTransaction(null);

using (var transaction2 = new TransactionScope())
{
// 4. Uncomment this line to attempt to unenlist from the transaction at this point.
//connection.EnlistTransaction(null);

// Enlist the connection in a new transaction.
connection.EnlistTransaction(Transaction.Current);
transaction2.Complete();
}
}
}
}
}

InvalidOperationException: Connection currently has transaction enlisted. Finish current transaction and retry.

No exception, or change the documentation.

0 attachments

Posted by Jared Moore on 8/20/2010 at 11:38 AM

Hi,

Thanks for reporting this issue. It is now fixed in .Net 4.0, so EnlistTransaction(null) with Explicit Unbind now works how the documentation says.

Cheers,
Jared Moore
Software Development Engineer in Test
ADO.NET Managed Providers and DataSet Team
jared.moore@microsoft.com

Posted by David A Nelson on 10/6/2009 at 10:52 AM

Can someone please explain how this was fixed? Is EnlistTransaction(null) now supported as per the current documentation?

Posted by Microsoft on 9/22/2009 at 11:17 AM

I was asked to not update the documentation for this issue, because it is possible that this will be fixed in code and behave as documented.

David Schwartz
technical writer
dschwart@microsoft.com

Posted by Microsoft on 7/29/2009 at 12:04 AM

Thanks for your feedback. We are routing this bug to the product unit who works on that specific feature area. The team will review this issue and make a decision on whether they will fix it or not for the next release.

Thank you,
Visual Studio Product Team