I am working on distriuted transactions on Oracle databases.
I have the following code, see below and 2 questions. targetConnection and sourceConnection are both Oracle databases and are located on different networks.
1) Which of the command will be commited first, when the "Complete" method is called?
2) What will happen when one of the connections is lost during the "Complete" method? For example: Complete method is reached and targetCommand is commited, but now the connection to the sourceConnection is lost. In my case the whole scope should be rolled back because the sourceCommand cannot be commited.
private bool MergeRowNew(String mergeStmt, Dictionary<string, object> row, ColumnList columns)
{
bool result = false;
bool targetTransaction = false;
bool sourceTransaction = false;
String mergeStmt2 = "merge into ...";
Exception exception = null;
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew,
new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted }))
{
try
{
//Using target connection
using (OracleConnection targetConnection = new OracleConnection(this.target.ConnectionString))
{
targetConnection.Open();
//Create new command for target
using (var targetCommand = targetConnection.CreateCommand(mergeStmt))
{
//Add parameters to target command
foreach (Column col in columns)
{
targetCommand.Parameters.Add(col.Name, row[col.Name]);
}
//Affected rows paramaeter
var rowCount = targetCommand.Parameters.Add(":numRows", OracleDbType.Integer, ParameterDirection.Output);
//execute insert in target db
targetCommand.ExecuteNonQuery();
//command succssesful when only one row was affected
if (Convert.ToInt32(rowCount.Value) == 1)
{
targetTransaction = true;
}
}
}
//If target transaction was succssesful then start the local transaction
if (targetTransaction)
{
//Using source connection
using (OracleConnection sourceConnection = new OracleConnection(this.source.ConnectionString))
{
sourceConnection.Open();
using (var sourceCommand = sourceConnection.CreateCommand(mergeStmt2))
{
sourceCommand.Parameters.Add("...","...");
var sourceRowcount = sourceCommand.Parameters.Add(":numRows", OracleDbType.Integer, ParameterDirection.Output);
sourceCommand.ExecuteNonQuery();
//command succssesful when only one row was affected
if (Convert.ToInt32(sourceRowcount.Value) == 1)
{
sourceTransaction = true;
}
}
}
}
}
catch (Exception ex)
{
exception = ex;
}
//if both commands were succssesful then complete the scope
if (targetTransaction && sourceTransaction)
{
scope.Complete();
result = true;
}
} //End scope.
//There was an exception.
if (exception != null)
{
//Do some logging.
}
return result;
}
