Detect if Distributed Transaction is used or just SQL 2005 internal transaction?

[John Lee]

Hi,

I have few questions related to .NET 2.0 TransactionScope class behavior:

1. Check Transaction.Current.TransactionInformation.DistributedIdentifier to
identify if distributed transaction is used - is it accurate way?

2. I have the following code blocks -

In code block 1, the first check the DistributedIdentifier is ALL 0s so it
tells me the distributed transaction is not used yet but right after the
second call to SqlHelper.ExecuteNonQuery(connectionString) it looks like the
distributed transaction is started - the connection string is exactly same
and access SQL 2005 database - it should only use SQL 2005 internal
transaction according to document

In code block 2 is almost identical to the block1 except I specifically open
the Sqlconnection and then pass the connection object instead of the
connection string to SqlHelper.ExecuteNonQuery(SqlConnection) AND this works
fine and NO distributed transaction is started.

It's OK in beta2 but I would think .NET runtime should be able to understand
only SQL 2005 database is involved even using connectionString instead of
passing SqlConnection around. Any thoughts?

ALSO, the TransactionScope's default isolationLevel is Serializable - which
I think it should be set to ReadCommitted

Block 1:
using (TransactionScope ts = new
TransactionScope(TransactionScopeOption.Required))
{
string SQL = "INSERT INTO test(name) values('John')";
SqlHelper.ExecuteNonQuery(this.m_connectionString, CommandType.Text,
SQL);

string msg = "";
if (Transaction.Current != null)
{
TransactionInformation ti = Transaction.Current.TransactionInformation;
msg = String.Format("LocalID = {0} Status = {1} Distributed ID = {2}
Isolation = {3}", ti.LocalIdentifier, ti.Status.ToString(),
(ti.DistributedIdentifier == null ? "" :
ti.DistributedIdentifier.ToString()),
Transaction.Current.IsolationLevel.ToString());
}
else
{
msg = "No Transaction Context";
}
MessageBox.Show(msg);

SQL = "INSERT INTO test(name) values('Jack')";
SqlHelper.ExecuteNonQuery(this.m_connectionString, CommandType.Text,
SQL);
if (Transaction.Current != null)
{
TransactionInformation ti = Transaction.Current.TransactionInformation;
msg = String.Format("LocalID = {0} Status = {1} Distributed ID = {2}
Isolation = {3}", ti.LocalIdentifier, ti.Status.ToString(),
(ti.DistributedIdentifier == null ? "" :
ti.DistributedIdentifier.ToString()),
Transaction.Current.IsolationLevel.ToString());
}
else
{
msg = "No Transaction Context";
}
MessageBox.Show(msg);

ts.Complete();
}

Block2:
using (TransactionScope ts = new
TransactionScope(TransactionScopeOption.Required))
{
using (SqlConnection conn = new SqlConnection(m_connectionString))
{
conn.Open();

string SQL = "INSERT INTO test(name) values('John')";
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL);

string msg = "";
if (Transaction.Current != null)
{
TransactionInformation ti =
Transaction.Current.TransactionInformation;
msg = String.Format("LocalID = {0} Status = {1} Distributed ID = {2}
Isolation = {3}", ti.LocalIdentifier, ti.Status.ToString(),
(ti.DistributedIdentifier == null ? "" :
ti.DistributedIdentifier.ToString()),
Transaction.Current.IsolationLevel.ToString());
}
else
{
msg = "No Transaction Context";
}
MessageBox.Show(msg);

SQL = "INSERT INTO test(name) values('Jack')";
SqlHelper.ExecuteNonQuery(conn, CommandType.Text, SQL);
if (Transaction.Current != null)
{
TransactionInformation ti =
Transaction.Current.TransactionInformation;
msg = String.Format("LocalID = {0} Status = {1} Distributed ID = {2}
Isolation = {3}", ti.LocalIdentifier, ti.Status.ToString(),
(ti.DistributedIdentifier == null ? "" :
ti.DistributedIdentifier.ToString()),
Transaction.Current.IsolationLevel.ToString());
}
else
{
msg = "No Transaction Context";
}
MessageBox.Show(msg);

ts.Complete();
}


Thanks very much!
John

 

[Steven Cheng[MSFT]]

Hi John,

Welcome to .NET newsgroup.
As for new Trasaction supporting question you mentioned in .net 2.0, we'll
have a check in the latest beta version and update you as soon as we got
any info.
Thanks for your understanding.

Steven Cheng
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Kevin Yu [MSFT]]

Hi John,

1. Yes, I think it's accurate. If the transaction is promoted to a
two-phase commit transaction, this property returns its unique identifier.
If the transaction is not promoted, the value is null.

2. When you pass a connection string to the SqlHelper.ExecuteNonQuery
method, it will create a new SqlConnection object automatically, which
makes the trasaction promoted. Since the final version of .NET framework
hasn't been released yet, we're not quite sure if the feature will change
in the future.

For ADO.NET 2.0 issues, you can also post in the following newsgroup. It is
dedicated to ADO.NET 2.0 issues. Thanks!

http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=45

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."