error 6107 after 10000+ calls on same transaction
bulk processing using TransactionScope object from C#.net, running command via a web service connecting to Oracle 9.2.0.8 back end on win2k server.
get the following error after ~10000 SELECT/UPDATE commands in a single transaction:
System.Data.OracleClient.OracleException: Unable to get error message (6107) (0)
i could update fewer records; however, it would be nice to be able to run an unlimited number in a single transaction.
following post contains code wich can reproduce this error.
using
System; using
System.Collections.Generic; using
System.Data; using
System.Data.OracleClient; using
System.Globalization; using
System.Text; using
System.Transactions; namespace
OracleTransaction {
class Program {
const string connection = "data source=ORAINST;user id=SCOTT;password=TIGER"; #region
sql strings const string tableDrop = "DROP TABLE oratest CASCADE CONSTRAINTS"; const string tableCreate = "CREATE TABLE oratest ( key1 number(22) not null, field1 varchar(20) default 'X' not null, field2 number(22) default 0 not null )"; const string indexCreate = "ALTER TABLE oratest ADD CONSTRAINT oratest_pk PRIMARY KEY ( key1 )"; // insert the name of your favourite huge table here const string insertSql = "INSERT INTO oratest ( key1 ) SELECT rownum FROM all_objects"; const string updateSql = "UPDATE oratest SET ( field2 ) = ( :field2 ) WHERE key1 = :key1"; #endregion
sql strings #region
helper functions static int ExecuteSql(string sql) {
using ( OracleConnection cnx = new OracleConnection(connection) ) {
cnx.Open();
using ( OracleCommand cmd = new OracleCommand(sql, cnx) ) {
return cmd.ExecuteNonQuery(); }
}
}
static int FillDataSet(DataSet theSet, string tableName, string sql) {
using ( OracleConnection cnx = new OracleConnection(connection) ) {
cnx.Open();
using ( OracleDataAdapter dap = new OracleDataAdapter(sql, cnx) ) {
return dap.Fill(theSet, tableName); }
}
}
#endregion
helper functions [
STAThread()] static void Main(string[] args) {
//only need to execute once really try {
ExecuteSql(tableDrop);
ExecuteSql(tableCreate);
ExecuteSql(indexCreate);
ExecuteSql(insertSql);
}
catch { } // don't care using ( DataSet theSet = new DataSet() ) {
theSet.Locale =
CultureInfo.CurrentCulture; using ( TransactionScope scope = new TransactionScope() ) {
FillDataSet(theSet,
"oratest", "SELECT key1 FROM oratest ORDER BY key1"); if ( theSet.Tables != null && theSet.Tables.Contains("oratest") ) {
foreach ( DataRow row in theSet.Tables["oratest"].Rows ) {
using ( OracleConnection cnx = new OracleConnection(connection) ) {
cnx.Open();
using ( OracleCommand cmd = new OracleCommand(updateSql, cnx) ) {
cmd.Parameters.Add(
new OracleParameter("key1", row["key1"])); cmd.Parameters.Add(
new OracleParameter("field2", 12345)); cmd.ExecuteNonQuery();
}
}
}
}
}
}
}
}
}
TMCDNR
oracle's considered response is this:
The bug has been closed as Not A Bug on the grounds that this many branches within a distributed transaction is unreasonable.
Each connection enlists as a separate branch in the distributed transaction; this means at the point where the application fails, you have around 10,000 branches, I have around 35,000 branches and Dev have around 80,000 (which was achieved by moving the TransactionScope object into the for( ; ; ) loop and closing and disposing it each time, although I haven't tested this).
The problem with this many branches is that if something were to go wrong at, say, the 50,000th branch, you would then have to rollback the previous 49,999 branches so that the distributed transaction would be handled correctly. But since all updates are on the same database, a distributed transaction seems to be unnecessary.
i guess this a warning to anyone planning to use straight c# (i.e no explicit sql) as a language to update an oracle database.
if you use strongly typed objects (i.e. one record per object) and read and write them one at a time to the database, make sure you never need to write more than 10k records in a single transaction.
i'll update when i get more from oracle.
The response from Oracle doesn't seem that outrageous.
Try ...
using (TransactionScope)
using (OracleConnection)
Fill...
foreach...
using (OracleCommand)
Update...
)
)
)
)
this should give you only one connection, and therefore one 'branch' to your transaction
that was oracle's suggested solution.
unfortunately you may occasionally need to open another connection.
especially if you're proceduralising your code properly or you're mixing back ends.
the problem is that opening a second distributed transaction causes System.Data.OracleClient to throw an error.
this is bad, since you can't tell when this is going to happen.
i'm preparing (or will when i get some time) a proper summary of my discussion with oracle to submit to Microsoft to see if they can deal with the bug.
thanks,
e.
in this thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124293&SiteId=1 i read this:
Quote:
TransactionManager wants to promote the Transaction to a distributed transaction when the SqlConnection is opened for the second call. This requires network access for my MSDTC
could this be what is causing the 6107 error?
if so, how do it give MSDTC network access, and, er, what and where is it?
many thanks,
e.
the error appears to occur when MTS opens a second distributed transaction.
or, to show my working explicitly, when the Transaction.Current.TransactionInformation.DistributedIdentifier changes.
this would appear to be a bug in MTS.
i'm having difficulty reproducing this consistently.
Oracle won't deal with me at all on this because i'm using System.Data.OracleClient, which is written by Microsoft. Oracle claim that their data provider is the only one they'll support.
Microsoft claim that this is an Oracle problem. i find it really unlikely that ORA-06107(network server not found) could be thrown in this context, and the error thrown is not of type OracleException.
i've found a sort-of workaraound, though, which is to declare the TransactionScope object after you've instatiated and opened a connection object. this way you only get a local transaction and Transaction.Current.TransactionInformation.DistributedIdentifier is always null.
Code Snippet
using ( OracleConnection cnx = new OracleConnection(...) )
{
cnx.Open();
using ( TransactionScope scope = new TransactionScope() )
{
// ...
}
}
this workaraound is very sort-of, in that it accomplishes its objective, which is to stop the current transaction becoming distibuted, but unfortunately the transaction is not associated with the connection and all updates are committed immediately.
oh well,back to the drawing board. 
