Re: Multithreaded Database access with C# on an Sql2005 and TransactionScope class (Bug or did I some mistake?)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Ok I changed my test to reflect my needs a bit more.
I also disabled connection pooling. The effect was that my first version is
working now. (so I tried an long time test)
But this one is still not working and I don't know why...

Alway get the error (the one with the already open DataReader)
"System.Transactions.TransactionAbortedException: 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.ExecuteTransactionYukon(TransactionRequest
transactionRequest, String transactionName, IsolationLevel iso,
SqlInternalTransaction internalTransaction, Boolean
isDelegateControlRequest)\r\n bei
System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest
transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction
internalTransaction, Boolean isDelegateControlRequest)\r\n bei
System.Data.SqlClient.SqlDelegatedTransaction.Promote()\r\n --- Ende der
internen Ausnahmestapelüberwachung ---\r\n bei
System.Data.SqlClient.SqlDelegatedTransaction.Promote()\r\n bei
System.Transactions.Tr

ansactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)\r\n bei
System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction
tx)\r\n --- Ende der internen Ausnahmestapelüberwachung ---\r\n bei
System.Transactions.TransactionStateAborted.EndCommit(InternalTransaction
tx)\r\n bei System.Transactions.CommittableTransaction.Commit()\r\n bei
System.Transactions.TransactionScope.InternalDispose()\r\n bei
System.Transactions.TransactionScope.Dispose()\r\n bei
TestTransaktion.Program.Main(String[] args) in D:\\DATEN\\SchoellerM\\Visual
Studio 2005\\Projects\\TestTransaktion\\TestTransaktion\\Program.cs:Zeile
31.")


Always get an...
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.LocalIdentifier,
Transaction.Current.TransactionInformation.DistributedIdentifier);

for(int i = 0; i < 1000; ++i)

{

using (TransactionScope ts2 = new
TransactionScope(TransactionScopeOption.RequiresNew))

{

Console.WriteLine("Check Transaction (InnerStart): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalIdentifier,
Transaction.Current.TransactionInformation.DistributedIdentifier);

for (int j = 0; j < 10; ++j)

{

ThreadPool.QueueUserWorkItem(worker3,
Transaction.Current.DependentClone(DependentCloneOption.BlockCommitUntilComplete));

}

Console.WriteLine("Check Transaction (InnerEnd): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalIdentifier,
Transaction.Current.TransactionInformation.DistributedIdentifier);

Console.WriteLine("About to complete the Innter Transaction");

ts2.Complete();

}

}

Console.WriteLine("Check Transaction (RootEnd): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalIdentifier,
Transaction.Current.TransactionInformation.DistributedIdentifier);

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 worker3(object ar)

{

try

{

DependentTransaction dtx = (DependentTransaction)ar;

using (TransactionScope ts = new TransactionScope(dtx))

{

Console.WriteLine("Check Transaction (Worker3Start): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalIdentifier,
Transaction.Current.TransactionInformation.DistributedIdentifier);

using (SqlConnection conn = new SqlConnection("Data
Source=WKOEDEV01\\SQL2005;Initial Catalog=WKOBASE_CLONE;Persist Security
Info=True;User ID=binreader;Password=readme2002;Pooling=false"))

{

conn.Open();

SqlCommand co = new SqlCommand("SELECT * FROM TEST", conn);

{

SqlDataReader r = co.ExecuteReader();

while (r.Read())

{

Console.WriteLine("Reader3: {0}, {1}", r.GetInt64(0), r.GetString(1));

System.Threading.Thread.Sleep(TimeSpan.FromSeconds(2));

}

r.Close();

}

}

Console.WriteLine("Check Transaction (Worker3End): L:{0} D:{1}",
Transaction.Current.TransactionInformation.LocalIdentifier,
Transaction.Current.TransactionInformation.DistributedIdentifier);

Console.WriteLine("About to complete the worker3 thread's transaction
scope");

ts.Complete();

}

Console.WriteLine("Completing the dependent clone");

dtx.Complete();

}

catch (Exception ex)

{

Console.WriteLine("Worker3 Catch");

Console.WriteLine(ex.ToString());

}

}

}

}




"Michael Schöller" <michael.schoeller@xxxxxxxxxxxxxx> schrieb im Newsbeitrag
news:utp53JdqIHA.1436@xxxxxxxxxxxxxxxxxxxxxxx
Well yes.

This is only an Test.

The real Programm should calculate some values for Customers.

There is a method that takes an Customer-Id as parameter an calculates all
values for that Customer.

The application process customers in blocks of 50. A Transaction is opend
50 Custumervalues are calculated and the transaction is closes. (If
everything goes right). This is done by calling the method in an loop with
an customer ID one by one.

The machine where the application is running has 8 CPUs so I was thinking
about speeding up the application by calling the method 10 times as
Threads with differend Customer-Ids and do data reading and calculating of
the values parallel.

The results of the calculation are taken and written to the database.

However if an error occur the whole block has to be taken back (In worst
case all 49 correctly calculated customers. The Transactionblock is logged
and marked as error then).

Data reading and calculaing is done within the loop so there is already an
open transaction around that.




.

Relevant Pages