Getting deadlocks in Oracle via ODBC Driver Inserts

[peternolan9]

Hi Guys,
I am running initial loads of a DW and running millions of millions of rows at a time....in some cases rows are written away to the same oracle table. I have set Rows per transaction to 1000 and parameter array size to 10 and isolation level to 'None'. I am getting occasional deadlocks in Oracle in loading rows.

This is only a problem during the inital load and I didn't feel much like making copies of jobs and loading through the Oracle OCI Load just for the initial loads.....kind of prepared to pay the price for longer run times on day 1 rather than change a bunch of code and take the chance on messing it up.

I also notice that when a table load has failed then sometimes there are no records in the table. And I would have expected/guess that some rows multiple of 10,000 rows should be there (rows per transaction * parameter array size). I am not sure how often the ODBC stage commits.

Like I said, not a big problem but I would be interested to know the answer if someone has seen this before...

Thanks

[SaiS]

Do you insert a row in one stream which you update using another?

[chulett]

I also notice that when a table load has failed then sometimes there are no records in the table. And I would have expected/guess that some rows multiple of 10,000 rows should be there (rows per transaction * parameter array size). I am not sure how often the ODBC stage commits.

Just an FYI - 'Rows per Transaction' is your commit level. The 'Array Size' is the number of records written to the database at any one time. So, your job will push 10 records into Oracle at a time and commit them after doing that 100 times.

If the table is empty after your problem occurs, then (for whatever reason) it was unable to commit the first 1000 rows.

Do you have foreign key constraints on this table, specifically unindexed foreign keys? I've seen 'Ask Tom' state that is the #1 reason for deadlocks.

[ray.wurlod]

In my experience with DataStage another common reason for deadlocks is updating the same table you are selecting from, and shooting for huge transaction size (unlimited rows per transaction). Oof!

[peternolan9]

I also notice that when a table load has failed then sometimes there are no records in the table. And I would have expected/guess that some rows multiple of 10,000 rows should be there (rows per transaction * parameter array size). I am not sure how often the ODBC stage commits.

Just an FYI - 'Rows per Transaction' is your commit level. The 'Array Size' is the number of records written to the database at any one time. So, your job will push 10 records into Oracle at a time and commit them after doing that 100 times.

If the table is empty after your problem occurs, then (for whatever reason) it was unable to commit the first 1000 rows.

Do you have foreign key constraints on this table, specifically unindexed foreign keys? I've seen 'Ask Tom' state that is the #1 reason for deadlocks.

Hi Craig/Ray,
that's why I was asking.....I've looked at all the 'nomal' things and still cannot for the life of me find the deadlock....and it is so infrequent and so little information is passed back about it that it's hard to find...

I thought someone else might have seen the same......no, not updating the same table I am reading from, but there are multiple jobs updating the same physical target table which is why I want commits every 1,000 or even 10,000 rows or something like that.....

Thanks for trying though...perhaps we will find this one later....I ran another huge batch last night, 50M rows, and not a deadlock amongst it...hhhmmmm....

[ray.wurlod]

Probably just imps and gremlins (other users?) doing random inserts on just the page where your process wants to be.

[chulett]

Yah, deadlocks of that nature are difficult if not darn near impossible to troubleshoot.

Something to consider - drop your indexes before job and then rebuild them after all inserts are complete. Not sure how feasible it might be to do that given your volumes, but it would eliminate another possible source of the deadlocking.