Oracle behaving strangely with multiple commands executing in a tight loop


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
 
Ad

Advertisements

S

Shawn Wildermuth

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
 
S

Steven Eichert

We haven't tried to replicate it with any other providers as of yet.
 
S

Steve Eichert

I verified that I get the same behavior with Oracle's Managed Provider.
 
F

Frans Bouma [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.
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#)
------------------------------------------------------------------------
 
S

Steve Eichert

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?
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
 
Ad

Advertisements

F

Frans Bouma [C# MVP]

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
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#)
------------------------------------------------------------------------
 
Ad

Advertisements

S

Steve Eichert

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
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top