Hello,
First of all english is not my natural language so please fogive me some bad
mistakes in gramatic and use of some vocables :).
I have a great problem here. Well I will not use it anymore but I want to
know why it is as it is ^^. I tried with .NET3.0 but I think it will be the
same with 2.0 and 3.5.
MSDTC is configured and working.
On the SQL2005-Server I created a Table Test (bigint, varchar(20)) filled
with
1, 'Test1'
2, 'Test2'
3, 'Test3'
4, 'Test4'
5, 'Test5'
6, 'Test6'
Execution the following Code works 1 of 20 Times (the other timed the
following Exception appears). After deleten the added Lines in the table and
Execute it again you always get the Exception:
(Sorry the Exception is partitially german and I don't know the english
counterpart but must be something like "Error while upgrading the
Transaction" and "There is already an opened DataReader assigned to this
connection" )
"System.Transactions.TransactionAbortedExcepti on: Die Transaktion wurde
abgebrochen. ---System.Transactions.TransactionPromotionException: Fehler
beim Versuch, die Transaktion heraufzustufen. --->
System.Data.SqlClient.SqlException: Diesem Befehl ist bereits ein geöffneter
DataReader zugeordnet, der zuerst geschlossen werden muss.\r\n bei
System.Data.SqlClient.SqlInternalConnectionTds.Exe cuteTransactionYukon(TransactionRequest
transactionRequest, String transactionName, IsolationLevel iso,
SqlInternalTransaction internalTransaction, Boolean
isDelegateControlRequest)\r\n bei
System.Data.SqlClient.SqlInternalConnectionTds.Exe cuteTransaction(TransactionRequest
transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction
internalTransaction, Boolean isDelegateControlRequest)\r\n bei
System.Data.SqlClient.SqlDelegatedTransaction.Prom ote()\r\n --- Ende der
internen Ausnahmestapelüberwachung ---\r\n bei
System.Data.SqlClient.SqlDelegatedTransaction.Prom ote()\r\n bei
System.Transactions.Tr
ansactionStatePSPEOperation.PSPEPromote(InternalTr ansaction tx)\r\n bei
System.Transactions.TransactionStateDelegatedBase. EnterState(InternalTransaction
tx)\r\n --- Ende der internen Ausnahmestapelüberwachung ---\r\n bei
System.Transactions.TransactionStateAborted.CheckF orFinishedTransaction(InternalTransaction
tx)\r\n bei
System.Transactions.TransactionStatePhase0.Promote (InternalTransaction
tx)\r\n bei System.Transactions.Transaction.Promote()\r\n bei
System.Transactions.TransactionInterop.ConvertToOl etxTransaction(Transaction
transaction)\r\n bei
System.Transactions.TransactionInterop.GetExportCo okie(Transaction
transaction, Byte[] whereabouts)\r\n bei
System.Data.SqlClient.SqlInternalConnection.Enlist NonNull(Transaction
tx)\r\n bei System.Data.SqlClient.SqlInternalConnection.Enlist (Transaction
tx)\r\n bei
System.Data.SqlClient.SqlInternalConnectionTds.Act ivate(Transaction
transaction)\r\n bei
System.Data.ProviderBase.DbConnectionInternal.Acti vateConnection(Transaction
transaction)\r\n
bei System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection
owningObject)\r\n bei
System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection
owningConnection)\r\n bei
System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)\r\n bei
System.Data.SqlClient.SqlConnection.Open()\r\n bei
TestTransaktion.Program.worker2(Object ar) in D:\\DATEN\\SchoellerM\\Visual
Studio 2005\\Projects\\TestTransaktion\\TestTransaktion\\ Program.cs:Zeile
120.\r\n bei System.Threading.ThreadHelper.ThreadStart_Context( Object
state)\r\n bei System.Threading.ExecutionContext.Run(ExecutionCon text
executionContext, ContextCallback callback, Object state)\r\n bei
System.Threading.ThreadHelper.ThreadStart(Object obj)"
Well personaly I don't belief the part with the DataReader..here is the
code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;
using System.Threading;
namespace TestTransaktion
{
class Program
{
static void Main(string[] args)
{
try
{
using (TransactionScope ts = new
TransactionScope(TransactionScopeOption.Required, TimeSpan.MaxValue))
{
Console.WriteLine("Check Transaction (RootStart): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Thread worker1 = new Thread(Program.worker1);
Thread worker2 = new Thread(Program.worker2);
worker1.IsBackground = true;
worker2.IsBackground = true;
worker1.Start(Transaction.Current.DependentClone(D ependentCloneOption.BlockCommitUntilComplete));
worker2.Start(Transaction.Current.DependentClone(D ependentCloneOption.BlockCommitUntilComplete));
//ThreadPool.QueueUserWorkItem(worker1,
Transaction.Current.DependentClone(DependentCloneO ption.BlockCommitUntilComplete));
//ThreadPool.QueueUserWorkItem(worker2,
Transaction.Current.DependentClone(DependentCloneO ption.BlockCommitUntilComplete));
Console.WriteLine("Check Transaction (RootEnd): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Console.WriteLine("About to complete the main thread");
ts.Complete();
}
Console.WriteLine("Transaction Completed");
}
catch (Exception ex)
{
Console.WriteLine("Top Catch");
Console.WriteLine(ex.ToString());
}
Console.WriteLine("Enter <Enter>");
Console.ReadLine();
}
static void worker1(object ar)
{
try{
DependentTransaction dtx = (DependentTransaction)ar;
using (TransactionScope ts = new TransactionScope(dtx))
{
Console.WriteLine("Check Transaction (Worker1Start): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
using (SqlConnection conn = new SqlConnection("Data
Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security
Info=True;User ID=binreader;Password=readme2002"))
{
conn.Open();
SqlCommand co = new SqlCommand("INSERT INTO TEST VALUES(8, 'Test8')", conn);
co.ExecuteNonQuery();
co = new SqlCommand("SELECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReader();
while (r.Read())
{
Console.WriteLine("Reader1: {0}, {1}", r.GetInt64(0), r.GetString(1));
System.Threading.Thread.Sleep(TimeSpan.FromSeconds (5));
}
r.Close();
}
}
Thread.Sleep(9000);
//throw new Exception("Aufzah!");
Console.WriteLine("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Console.WriteLine("About to complete the worker9 thread's transaction
scope");
ts.Complete();
}
Console.WriteLine("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLine("Worker1 Catch");
Console.WriteLine(ex.ToString());
throw;
}
}
static void worker2(object ar)
{
try{
DependentTransaction dtx = (DependentTransaction)ar;
using (TransactionScope ts = new TransactionScope(dtx))
{
Console.WriteLine("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
using(SqlConnection conn = new SqlConnection("Data
Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security
Info=True;User ID=binreader;Password=readme2002"))
{
conn.Open();
Thread.Sleep(TimeSpan.FromSeconds(10)); //<- Wait till Main Thread reaches
ts.Commit();
SqlCommand co = new SqlCommand("INSERT INTO TEST VALUES(7, 'Test7')", conn);
co.ExecuteNonQuery();
co = new SqlCommand("SELECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReader();
while (r.Read())
{
Console.WriteLine("Reader2: {0}, {1}", r.GetInt64(0), r.GetString(1));
System.Threading.Thread.Sleep(TimeSpan.FromSeconds (2));
}
r.Close();
}
}
Thread.Sleep(5000);
Console.WriteLine("Check Transaction (Worker2End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Console.WriteLine("About to complete the worker2 thread's transaction
scope");
ts.Complete();
}
Console.WriteLine("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLine("Worker2 Catch");
Console.WriteLine(ex.ToString());
throw;
}
}
}
}
Alternativ habe ich auch diesen Code hier versucht der eher dem enspricht
was wir Momentan verwenden. Allerdings liefert der wiederum folgende
Exception (fast immer oder eben die obere)
Alternativ I tried out the following code, that was in a locical pouint of
few a little bit more like the one I am using at the moment. This code
throws the following execption (not every time but mostly the other time the
exception I showed you earlyer was thrown) (BTW funny thing same PC same
Studio but now an english exception.)
"System.Data.SqlClient.SqlException: Distributed transaction completed.
Either enlist this session in a new transaction or the NULL transaction.\r\n
bei System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception,
Boolean breakConnection)\r\n bei
System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception,
Boolean breakConnection)\r\n bei
System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject
stateObj)\r\n bei System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\r\n
bei System.Data.SqlClient.SqlCommand.RunExecuteNonQuer yTds(String
methodName, Boolean async)\r\n bei
System.Data.SqlClient.SqlCommand.InternalExecuteNo nQuery(DbAsyncResult
result, String methodName, Boolean sendToPipe)\r\n bei
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() \r\n bei
TestTransaktion.Program.worker2(Object ar) in D:\\DATEN\\SchoellerM\\Visual
St
udio 2005\\Projects\\TestTransaktion\\TestTransaktion\\ Program.cs:Zeile
133.\r\n bei System.Threading.ThreadHelper.ThreadStart_Context( Object
state)\r\n bei System.Threading.ExecutionContext.Run(ExecutionCon text
executionContext, ContextCallback callback, Object state)\r\n bei
System.Threading.ThreadHelper.ThreadStart(Object obj)"
The Code
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Transactions;
using System.Threading;
namespace TestTransaktion
{
class Program
{
static SqlConnection conn1;
static SqlConnection conn2;
static void Main(string[] args)
{
try
{
conn1 = new SqlConnection("Data Source=WKOEDEV01\\SQL2005;Initial
Catalog=WKOBASE_CLONE;Persist Security Info=True;User
ID=binreader;Password=readme2002");
conn2 = new SqlConnection("Data Source=WKOEDEV01\\SQL2005;Initial
Catalog=WKOBASE_CLONE;Persist Security Info=True;User
ID=binreader;Password=readme2002");
conn1.Open();
conn2.Open();
using (TransactionScope ts = new
TransactionScope(TransactionScopeOption.Required, TimeSpan.MaxValue))
{
conn1.EnlistTransaction(Transaction.Current);
conn2.EnlistTransaction(Transaction.Current);
Console.WriteLine("Check Transaction (RootStart): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Thread worker1 = new Thread(Program.worker1);
Thread worker2 = new Thread(Program.worker2);
worker1.IsBackground = true;
worker2.IsBackground = true;
worker1.Start(Transaction.Current.DependentClone(D ependentCloneOption.BlockCommitUntilComplete));
worker2.Start(Transaction.Current.DependentClone(D ependentCloneOption.BlockCommitUntilComplete));
//ThreadPool.QueueUserWorkItem(worker1,
Transaction.Current.DependentClone(DependentCloneO ption.BlockCommitUntilComplete));
//ThreadPool.QueueUserWorkItem(worker2,
Transaction.Current.DependentClone(DependentCloneO ption.BlockCommitUntilComplete));
Console.WriteLine("Check Transaction (RootEnd): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Console.WriteLine("About to complete the main thread");
ts.Complete();
}
conn1.Close();
conn2.Close();
Console.WriteLine("Transaction Completed");
}
catch (Exception ex)
{
Console.WriteLine("Top Catch");
Console.WriteLine(ex.ToString());
}
Console.WriteLine("Enter <Enter>");
Console.ReadLine();
}
static void worker1(object ar)
{
try{
DependentTransaction dtx = (DependentTransaction)ar;
using (TransactionScope ts = new TransactionScope(dtx))
{
Console.WriteLine("Check Transaction (Worker1Start): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
//using (SqlConnection conn = new SqlConnection("Data
Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security
Info=True;User ID=binreader;Password=readme2002"))
SqlConnection conn = conn1;
{
//conn.Open();
SqlCommand co = new SqlCommand("INSERT INTO TEST VALUES(8, 'Test8')", conn);
co.ExecuteNonQuery();
co = new SqlCommand("SELECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReader();
while (r.Read())
{
Console.WriteLine("Reader1: {0}, {1}", r.GetInt64(0), r.GetString(1));
System.Threading.Thread.Sleep(TimeSpan.FromSeconds (5));
}
r.Close();
}
}
Thread.Sleep(9000);
//throw new Exception("Aufzah!");
Console.WriteLine("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Console.WriteLine("About to complete the worker9 thread's transaction
scope");
ts.Complete();
}
Console.WriteLine("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLine("Worker1 Catch");
Console.WriteLine(ex.ToString());
throw;
}
}
static void worker2(object ar)
{
try{
DependentTransaction dtx = (DependentTransaction)ar;
using (TransactionScope ts = new TransactionScope(dtx))
{
Console.WriteLine("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
//using(SqlConnection conn = new SqlConnection("Data
Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security
Info=True;User ID=binreader;Password=readme2002"))
SqlConnection conn = conn2;
{
//conn.Open();
Thread.Sleep(TimeSpan.FromSeconds(10)); //<- Wait till Main Thread reaches
ts.Commit();
SqlCommand co = new SqlCommand("INSERT INTO TEST VALUES(7, 'Test7')", conn);
co.ExecuteNonQuery();
co = new SqlCommand("SELECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReader();
while (r.Read())
{
Console.WriteLine("Reader2: {0}, {1}", r.GetInt64(0), r.GetString(1));
System.Threading.Thread.Sleep(TimeSpan.FromSeconds (2));
}
r.Close();
}
}
Thread.Sleep(5000);
Console.WriteLine("Check Transaction (Worker2End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalId entifier,
Transaction.Current.TransactionInformation.Distrib utedIdentifier);
Console.WriteLine("About to complete the worker2 thread's transaction
scope");
ts.Complete();
}
Console.WriteLine("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLine("Worker2 Catch");
Console.WriteLine(ex.ToString());
throw;
}
}
}
}
I have no Idea why nothig there is working like I would expected it.
Is there an working method to access an database with multible Threads or
should I bury that idea?
Examples of working Templates are wery welcome.
With the request for help
Michael Schöller
