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.Transac tions.Transacti onAbortedExcept ion: Die Transaktion wurde
abgebrochen. ---System.Transact ions.Transactio nPromotionExcep tion: Fehler
beim Versuch, die Transaktion heraufzustufen. --->
System.Data.Sql Client.SqlExcep tion: Diesem Befehl ist bereits ein geöffneter
DataReader zugeordnet, der zuerst geschlossen werden muss.\r\n bei
System.Data.Sql Client.SqlInter nalConnectionTd s.ExecuteTransa ctionYukon(Tran sactionRequest
transactionRequ est, String transactionName , IsolationLevel iso,
SqlInternalTran saction internalTransac tion, Boolean
isDelegateContr olRequest)\r\n bei
System.Data.Sql Client.SqlInter nalConnectionTd s.ExecuteTransa ction(Transacti onRequest
transactionRequ est, String name, IsolationLevel iso, SqlInternalTran saction
internalTransac tion, Boolean isDelegateContr olRequest)\r\n bei
System.Data.Sql Client.SqlDeleg atedTransaction .Promote()\r\n --- Ende der
internen Ausnahmestapelü berwachung ---\r\n bei
System.Data.Sql Client.SqlDeleg atedTransaction .Promote()\r\n bei
System.Transact ions.Tr
ansactionStateP SPEOperation.PS PEPromote(Inter nalTransaction tx)\r\n bei
System.Transact ions.Transactio nStateDelegated Base.EnterState (InternalTransa ction
tx)\r\n --- Ende der internen Ausnahmestapelü berwachung ---\r\n bei
System.Transact ions.Transactio nStateAborted.C heckForFinished Transaction(Int ernalTransactio n
tx)\r\n bei
System.Transact ions.Transactio nStatePhase0.Pr omote(InternalT ransaction
tx)\r\n bei System.Transact ions.Transactio n.Promote()\r\n bei
System.Transact ions.Transactio nInterop.Conver tToOletxTransac tion(Transactio n
transaction)\r\ n bei
System.Transact ions.Transactio nInterop.GetExp ortCookie(Trans action
transaction, Byte[] whereabouts)\r\ n bei
System.Data.Sql Client.SqlInter nalConnection.E nlistNonNull(Tr ansaction
tx)\r\n bei System.Data.Sql Client.SqlInter nalConnection.E nlist(Transacti on
tx)\r\n bei
System.Data.Sql Client.SqlInter nalConnectionTd s.Activate(Tran saction
transaction)\r\ n bei
System.Data.Pro viderBase.DbCon nectionInternal .ActivateConnec tion(Transactio n
transaction)\r\ n
bei System.Data.Pro viderBase.DbCon nectionPool.Get Connection(DbCo nnection
owningObject)\r \n bei
System.Data.Pro viderBase.DbCon nectionFactory. GetConnection(D bConnection
owningConnectio n)\r\n bei
System.Data.Pro viderBase.DbCon nectionClosed.O penConnection(D bConnection
outerConnection , DbConnectionFac tory connectionFacto ry)\r\n bei
System.Data.Sql Client.SqlConne ction.Open()\r\ n bei
TestTransaktion .Program.worker 2(Object ar) in D:\\DATEN\\Scho ellerM\\Visual
Studio 2005\\Projects\ \TestTransaktio n\\TestTransakt ion\\Program.cs :Zeile
120.\r\n bei System.Threadin g.ThreadHelper. ThreadStart_Con text(Object
state)\r\n bei System.Threadin g.ExecutionCont ext.Run(Executi onContext
executionContex t, ContextCallback callback, Object state)\r\n bei
System.Threadin g.ThreadHelper. ThreadStart(Obj ect obj)"
Well personaly I don't belief the part with the DataReader..her e is the
code
using System;
using System.Collecti ons.Generic;
using System.Text;
using System.Data.Sql Client;
using System.Transact ions;
using System.Threadin g;
namespace TestTransaktion
{
class Program
{
static void Main(string[] args)
{
try
{
using (TransactionSco pe ts = new
TransactionScop e(TransactionSc opeOption.Requi red, TimeSpan.MaxVal ue))
{
Console.WriteLi ne("Check Transaction (RootStart): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Thread worker1 = new Thread(Program. worker1);
Thread worker2 = new Thread(Program. worker2);
worker1.IsBackg round = true;
worker2.IsBackg round = true;
worker1.Start(T ransaction.Curr ent.DependentCl one(DependentCl oneOption.Block CommitUntilComp lete));
worker2.Start(T ransaction.Curr ent.DependentCl one(DependentCl oneOption.Block CommitUntilComp lete));
//ThreadPool.Queu eUserWorkItem(w orker1,
Transaction.Cur rent.DependentC lone(DependentC loneOption.Bloc kCommitUntilCom plete));
//ThreadPool.Queu eUserWorkItem(w orker2,
Transaction.Cur rent.DependentC lone(DependentC loneOption.Bloc kCommitUntilCom plete));
Console.WriteLi ne("Check Transaction (RootEnd): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Console.WriteLi ne("About to complete the main thread");
ts.Complete();
}
Console.WriteLi ne("Transactio n Completed");
}
catch (Exception ex)
{
Console.WriteLi ne("Top Catch");
Console.WriteLi ne(ex.ToString( ));
}
Console.WriteLi ne("Enter <Enter>");
Console.ReadLin e();
}
static void worker1(object ar)
{
try{
DependentTransa ction dtx = (DependentTrans action)ar;
using (TransactionSco pe ts = new TransactionScop e(dtx))
{
Console.WriteLi ne("Check Transaction (Worker1Start): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
using (SqlConnection conn = new SqlConnection(" Data
Source=WKOEDEV0 1\\SQL2005;Init ial Catalog=WKOBASE _CLONE;Persist Security
Info=True;User ID=binreader;Pa ssword=readme20 02"))
{
conn.Open();
SqlCommand co = new SqlCommand("INS ERT INTO TEST VALUES(8, 'Test8')", conn);
co.ExecuteNonQu ery();
co = new SqlCommand("SEL ECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReade r();
while (r.Read())
{
Console.WriteLi ne("Reader1: {0}, {1}", r.GetInt64(0), r.GetString(1)) ;
System.Threadin g.Thread.Sleep( TimeSpan.FromSe conds(5));
}
r.Close();
}
}
Thread.Sleep(90 00);
//throw new Exception("Aufz ah!");
Console.WriteLi ne("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Console.WriteLi ne("About to complete the worker9 thread's transaction
scope");
ts.Complete();
}
Console.WriteLi ne("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLi ne("Worker1 Catch");
Console.WriteLi ne(ex.ToString( ));
throw;
}
}
static void worker2(object ar)
{
try{
DependentTransa ction dtx = (DependentTrans action)ar;
using (TransactionSco pe ts = new TransactionScop e(dtx))
{
Console.WriteLi ne("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
using(SqlConnec tion conn = new SqlConnection(" Data
Source=WKOEDEV0 1\\SQL2005;Init ial Catalog=WKOBASE _CLONE;Persist Security
Info=True;User ID=binreader;Pa ssword=readme20 02"))
{
conn.Open();
Thread.Sleep(Ti meSpan.FromSeco nds(10)); //<- Wait till Main Thread reaches
ts.Commit();
SqlCommand co = new SqlCommand("INS ERT INTO TEST VALUES(7, 'Test7')", conn);
co.ExecuteNonQu ery();
co = new SqlCommand("SEL ECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReade r();
while (r.Read())
{
Console.WriteLi ne("Reader2: {0}, {1}", r.GetInt64(0), r.GetString(1)) ;
System.Threadin g.Thread.Sleep( TimeSpan.FromSe conds(2));
}
r.Close();
}
}
Thread.Sleep(50 00);
Console.WriteLi ne("Check Transaction (Worker2End): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Console.WriteLi ne("About to complete the worker2 thread's transaction
scope");
ts.Complete();
}
Console.WriteLi ne("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLi ne("Worker2 Catch");
Console.WriteLi ne(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.Sq lClient.SqlExce ption: Distributed transaction completed.
Either enlist this session in a new transaction or the NULL transaction.\r\ n
bei System.Data.Sql Client.SqlConne ction.OnError(S qlException exception,
Boolean breakConnection )\r\n bei
System.Data.Sql Client.SqlInter nalConnection.O nError(SqlExcep tion exception,
Boolean breakConnection )\r\n bei
System.Data.Sql Client.TdsParse r.ThrowExceptio nAndWarning(Tds ParserStateObje ct
stateObj)\r\n bei System.Data.Sql Client.TdsParse r.Run(RunBehavi or
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleR esultSet bulkCopyHandler , TdsParserStateO bject stateObj)\r\n
bei System.Data.Sql Client.SqlComma nd.RunExecuteNo nQueryTds(Strin g
methodName, Boolean async)\r\n bei
System.Data.Sql Client.SqlComma nd.InternalExec uteNonQuery(DbA syncResult
result, String methodName, Boolean sendToPipe)\r\n bei
System.Data.Sql Client.SqlComma nd.ExecuteNonQu ery()\r\n bei
TestTransaktion .Program.worker 2(Object ar) in D:\\DATEN\\Scho ellerM\\Visual
St
udio 2005\\Projects\ \TestTransaktio n\\TestTransakt ion\\Program.cs :Zeile
133.\r\n bei System.Threadin g.ThreadHelper. ThreadStart_Con text(Object
state)\r\n bei System.Threadin g.ExecutionCont ext.Run(Executi onContext
executionContex t, ContextCallback callback, Object state)\r\n bei
System.Threadin g.ThreadHelper. ThreadStart(Obj ect obj)"
The Code
using System;
using System.Collecti ons.Generic;
using System.Text;
using System.Data.Sql Client;
using System.Transact ions;
using System.Threadin g;
namespace TestTransaktion
{
class Program
{
static SqlConnection conn1;
static SqlConnection conn2;
static void Main(string[] args)
{
try
{
conn1 = new SqlConnection(" Data Source=WKOEDEV0 1\\SQL2005;Init ial
Catalog=WKOBASE _CLONE;Persist Security Info=True;User
ID=binreader;Pa ssword=readme20 02");
conn2 = new SqlConnection(" Data Source=WKOEDEV0 1\\SQL2005;Init ial
Catalog=WKOBASE _CLONE;Persist Security Info=True;User
ID=binreader;Pa ssword=readme20 02");
conn1.Open();
conn2.Open();
using (TransactionSco pe ts = new
TransactionScop e(TransactionSc opeOption.Requi red, TimeSpan.MaxVal ue))
{
conn1.EnlistTra nsaction(Transa ction.Current);
conn2.EnlistTra nsaction(Transa ction.Current);
Console.WriteLi ne("Check Transaction (RootStart): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Thread worker1 = new Thread(Program. worker1);
Thread worker2 = new Thread(Program. worker2);
worker1.IsBackg round = true;
worker2.IsBackg round = true;
worker1.Start(T ransaction.Curr ent.DependentCl one(DependentCl oneOption.Block CommitUntilComp lete));
worker2.Start(T ransaction.Curr ent.DependentCl one(DependentCl oneOption.Block CommitUntilComp lete));
//ThreadPool.Queu eUserWorkItem(w orker1,
Transaction.Cur rent.DependentC lone(DependentC loneOption.Bloc kCommitUntilCom plete));
//ThreadPool.Queu eUserWorkItem(w orker2,
Transaction.Cur rent.DependentC lone(DependentC loneOption.Bloc kCommitUntilCom plete));
Console.WriteLi ne("Check Transaction (RootEnd): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Console.WriteLi ne("About to complete the main thread");
ts.Complete();
}
conn1.Close();
conn2.Close();
Console.WriteLi ne("Transactio n Completed");
}
catch (Exception ex)
{
Console.WriteLi ne("Top Catch");
Console.WriteLi ne(ex.ToString( ));
}
Console.WriteLi ne("Enter <Enter>");
Console.ReadLin e();
}
static void worker1(object ar)
{
try{
DependentTransa ction dtx = (DependentTrans action)ar;
using (TransactionSco pe ts = new TransactionScop e(dtx))
{
Console.WriteLi ne("Check Transaction (Worker1Start): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
//using (SqlConnection conn = new SqlConnection(" Data
Source=WKOEDEV0 1\\SQL2005;Init ial Catalog=WKOBASE _CLONE;Persist Security
Info=True;User ID=binreader;Pa ssword=readme20 02"))
SqlConnection conn = conn1;
{
//conn.Open();
SqlCommand co = new SqlCommand("INS ERT INTO TEST VALUES(8, 'Test8')", conn);
co.ExecuteNonQu ery();
co = new SqlCommand("SEL ECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReade r();
while (r.Read())
{
Console.WriteLi ne("Reader1: {0}, {1}", r.GetInt64(0), r.GetString(1)) ;
System.Threadin g.Thread.Sleep( TimeSpan.FromSe conds(5));
}
r.Close();
}
}
Thread.Sleep(90 00);
//throw new Exception("Aufz ah!");
Console.WriteLi ne("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Console.WriteLi ne("About to complete the worker9 thread's transaction
scope");
ts.Complete();
}
Console.WriteLi ne("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLi ne("Worker1 Catch");
Console.WriteLi ne(ex.ToString( ));
throw;
}
}
static void worker2(object ar)
{
try{
DependentTransa ction dtx = (DependentTrans action)ar;
using (TransactionSco pe ts = new TransactionScop e(dtx))
{
Console.WriteLi ne("Check Transaction (Worker1End): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
//using(SqlConnec tion conn = new SqlConnection(" Data
Source=WKOEDEV0 1\\SQL2005;Init ial Catalog=WKOBASE _CLONE;Persist Security
Info=True;User ID=binreader;Pa ssword=readme20 02"))
SqlConnection conn = conn2;
{
//conn.Open();
Thread.Sleep(Ti meSpan.FromSeco nds(10)); //<- Wait till Main Thread reaches
ts.Commit();
SqlCommand co = new SqlCommand("INS ERT INTO TEST VALUES(7, 'Test7')", conn);
co.ExecuteNonQu ery();
co = new SqlCommand("SEL ECT * FROM TEST", conn);
{
SqlDataReader r = co.ExecuteReade r();
while (r.Read())
{
Console.WriteLi ne("Reader2: {0}, {1}", r.GetInt64(0), r.GetString(1)) ;
System.Threadin g.Thread.Sleep( TimeSpan.FromSe conds(2));
}
r.Close();
}
}
Thread.Sleep(50 00);
Console.WriteLi ne("Check Transaction (Worker2End): L:{0} D:{1}",
Transaction.Cur rent.Transactio nInformation.Lo calIdentifier,
Transaction.Cur rent.Transactio nInformation.Di stributedIdenti fier);
Console.WriteLi ne("About to complete the worker2 thread's transaction
scope");
ts.Complete();
}
Console.WriteLi ne("Completing the dependent clone");
dtx.Complete();
}
catch (Exception ex)
{
Console.WriteLi ne("Worker2 Catch");
Console.WriteLi ne(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
