DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 146

Points: 1542

Post Posted: Mon Feb 13, 2017 6:56 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hi All,

While doing an update of a table which cotains million of rows, we face deadlock issue. Even though we dont have duplicate value based on the key column we come across this issue.


Fatal Error: The connector failed to execute the statement:
UPDATE FACT SET AMOUNT1 = ?, AMOUNT2 = ?, AMOUNT3 = ?, AMOUNT4 = ? WHERE KEY = ?. The reported errors are:
[SQLSTATE 61000] java.sql.BatchUpdateException: ORA-00060: deadlock detected while waiting for resource
[SQLSTATE 61000] java.sql.SQLException: ORA-00060: deadlock detected while waiting for resource


Fact Table ini_trans=16 and max_trans=255.

Kindly let me know your thoughts on the same.

Thanks & Regards,
Satheesh.R
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 53880
Location: Sydney, Australia
Points: 292392

Post Posted: Mon Feb 13, 2017 8:45 pm Reply with quote    Back to top    

What level of locking (transaction isolation) are you using?

How are your data partitioned?

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 146

Points: 1542

Post Posted: Mon Feb 13, 2017 10:57 pm Reply with quote    Back to top    

Hi Ray,

we are using Hash partition

Regards,
Satheesh.R
Rate this response:  
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 146

Points: 1542

Post Posted: Tue Feb 14, 2017 11:01 am Reply with quote    Back to top    

Hi All,

As we are using JDBC Connector stage to connect the target Db using Oracle thin client with Read committed as an isolation level and Hashed partition..still the issue occurs when we try to update the table with 4 nodes.


Kindly let me know your thoughts on the same.


Regards,
Satheesh.R
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41848
Location: Denver, CO
Points: 214702

Post Posted: Tue Feb 14, 2017 11:29 am Reply with quote    Back to top    

Sounds to me like you're not hashing properly. What are your hash key(s)?

_________________
-craig

The black queen chants the funeral march, the cracked brass bells will ring
To summon back the fire witch to the court of the crimson king
Rate this response:  
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 987
Location: Omaha, NE
Points: 6347

Post Posted: Tue Feb 14, 2017 1:42 pm Reply with quote    Back to top    

I haven't touched an Oracle database in years, so don't know if my comments would apply.

When I see a high-volume update statement (what percentage of the FACT table are you updating and what is the total volume?), I would also be concerned about how many indexes exist on the table. You normally would partition data by a primary key/clustering index, but it is frequently the locks on secondary index pages and the lock escalation policy of the database that can cause deadlocks.

Can you afford to drop and rebuild secondary indexes?

Mike
Rate this response:  
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 53880
Location: Sydney, Australia
Points: 292392

Post Posted: Wed Feb 15, 2017 2:45 am Reply with quote    Back to top    

I didn't ask about partitioning, I asked about locking.

Let me add an additional question about what array size and transaction size you are using.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
satheesh_color
Participant



Joined: 16 Jun 2005
Posts: 146

Points: 1542

Post Posted: Wed Feb 15, 2017 6:22 am Reply with quote    Back to top    

Hi All,

In JDBC connector we are using RecordCount as 100 and Session/BatchSize as 100. The default value is 2000. Eventhough we have reduced it, it causes deadlock.

Note: We are trying to update 6 million records and Foreign keys(1,2,3) having index. We are updating the table based on primary key.


When I try to run on single node / sequentially the job ran fine.

Regards,
Satheesh.R
Rate this response:  
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 41848
Location: Denver, CO
Points: 214702

Post Posted: Wed Feb 15, 2017 8:00 am Reply with quote    Back to top    

ray.wurlod wrote:
I didn't ask about partitioning, I asked about locking.

You ask about both, actually. Wink

ray.wurlod wrote:
What level of locking (transaction isolation) are you using?

How are your data partitioned?

And the fact that it runs fine on a single node still smells like a partitioning issue to me, the details of which are still unanswered. For whatever that is worth.

_________________
-craig

The black queen chants the funeral march, the cracked brass bells will ring
To summon back the fire witch to the court of the crimson king
Rate this response:  
Mike



Group memberships:
Premium Members

Joined: 03 Mar 2002
Posts: 987
Location: Omaha, NE
Points: 6347

Post Posted: Wed Feb 15, 2017 8:31 am Reply with quote    Back to top    

In addition to answering the multiple questions about how you are partitioning the data (by primary key?), how many rows are in your target table?

Updates are an expensive DML operation, so 6 million updates have a performance cost.

If your table size is 10 million, then you might benefit (as an example) by replacing the 6 million updates with a truncate/bulk load (which might have a lower overall performance cost). But if your table size is 100 million, then the 6 million updates might still be the lower cost alternative.

Right now your only working solution seems to be running 6 million updates serially (single node), and it does seem likely that there would be lower cost alternatives available to you.

Mike
Rate this response:  
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours