Oracle behaving strangely with multiple commands executing in a tight loop

  • Thread starter Thread starter Steven Eichert
  • Start date Start date
S

Steven Eichert

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
 
Hello Steven,

Can you replicate the behavior with Oracle's Managed Provider (e.g. Oracle.Data.OracleClient)?

Thanks,
Shawn Wildermuth
Speaker, Author and C# MVP
 
Steven said:
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.
Click to expand...

Oracle uses MVCC, which is a concurrency scheme within a transaction:
if a transaction with DML statements is executed, the transaction can
manipulate data without affecting other threads. This means that
another command issued could be executed in parallel with the insert
and it won't see the inserted data until that transaction is completed.

On SqlServer, the update statement will block till the insert has been
completed.

Do you execute the insert and update in a single transaction? If not,
could you try to run it in a single transaction?

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
Oracle uses MVCC, which is a concurrency scheme within a
transaction: if a transaction with DML statements is executed, the
transaction can manipulate data without affecting other threads. This
means that another command issued could be executed in parallel with
the insert and it won't see the inserted data until that transaction
is completed.

On SqlServer, the update statement will block till the insert has
been completed.

Do you execute the insert and update in a single transaction? If not,
could you try to run it in a single transaction?
Click to expand...

The issue that we're seeing isn't really a problem with the INSERT INTO and
UPDATE that is executed right after it. At least it doesn't seem to be.
We have a bunch of validations that are doing an INSERT INTO followed by
the update, it appears that they are stepping on one another which is resulting
in data not being inserted and rows not being updated. Would we have to
execute all commands within a single transaction, or just each individual
INSERT INTO/UPDATE combo?

Thanks,
Steve
 
Steve said:
The issue that we're seeing isn't really a problem with the INSERT
INTO and UPDATE that is executed right after it. At least it doesn't
seem to be. We have a bunch of validations that are doing an INSERT
INTO followed by the update, it appears that they are stepping on one
another which is resulting in data not being inserted and rows not
being updated. Would we have to execute all commands within a single
transaction, or just each individual INSERT INTO/UPDATE combo?
Thanks, Steve
Click to expand...

As soon as you don't group statements in 1 transaction, Oracle CAN
(but doesn't have to) schedule the queries to be executed in parallel.
I have no hard evidence that it doesn't happen as well inside a
transaction but it seems unlikely due to the uncommitted data only
reachable from the transaction participating connection.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------
 
As soon as you don't group statements in 1 transaction, Oracle CAN
(but doesn't have to) schedule the queries to be executed in parallel.
I have no hard evidence that it doesn't happen as well inside a
transaction but it seems unlikely due to the uncommitted data only
reachable from the transaction participating connection.

FB
Click to expand...

We ended up figuring this out. As you may have suspected it wasn't an issue
with the commands stepping on each other but a matter of the import process
running in parrallel with the validations. Setting the breakpoint made it
so the complete import finished before the validations. Setting up the process
to ensure the bulk import completes before running the validations resolved
our issue.

Moral of the story: Assume it's your code that is messed up not somebody
else's :)

Thanks for the help!

Cheers,
Stev
 
Back
Top