We've run into a very strange issue when running against Oracle using System.Data.OracleClient.
As part of an import process that we're running we're doing a number of
validations on data that has already been inserted into our database. For
each validation within our import process we're logging errors by doing something
along the lines of:
INSERT INTO ImportMessages (ErrorContext, ErrorType, Timestamp, RowId)
SELECT Col1, 1, CurrentDate, RowIdColumn
FROM TableWithImportedRecords
WHERE {validation condition}
We subsequently update each invalid record in our bulk import table with
a simple UPDATE statement.
UPDATE BulkImport SET IsValid = 0 WHERE {validation condition}
We have approximately 15 validations that are run against the database in
sequence.
foreach(ImportValidation validation in validations) {
validation.Validate(); // this executes the two SQL statements above
errorCount = validation.ErrorCount;
warningCount = validation.WarningCount;
}
When we run our imports we're getting extemely inconsistent (random) behavior
from Oracle. When we execute the process against Sql Server everything works
reliably and consistently. As part of our debugging efforts we tracked things
down to the foreach() loop shown above and found that there is some sort
of timing issue when we execute our commands against Oracle. When we run
in the debugger with a breakpoint set on the errorCount line everything works
perfectly, but, as soon as we take the breakpoint off the code results in
extemely inconsistent and incorrect results. As an experiment we placed
a Thread.Sleep(500) statement inside the loop which resulted in everything
working properly.
What could be happening when those statements are executed against Oracle
that would cause them to return unexpected results when run in a tight foreach
loop? Are there any Oracle specific settings that I should look into (auto-commit?)?
Any ideas? I'm fresh out