Updating parent and child records at the same time

We have an application that updates a child record and based on that change there is a change to the parent table's status_id column. There is a trigger that is supposed to handle this, but we are getting deadlocks because we are trying to update both of these at the same time. Is there a way to commit the change in the child and get the parent updated at the same time?

Published: 14 Sep 2005

We have a situation where the application updates a child record and based on that change there is a change to the parent table's status_id column. There is a trigger that is supposed to handle this, but we are getting deadlocks when users are updating the child in the application because we are trying to update both of these at the same time. Is there a way to commit the change in the child and get the parent updated with the correct status at the same time? Thanks!

The first thing I would do is ensure that the update of the child record and the parent record are committed as soon as possible in your application. If you delay your commit, then you increase your chances of a deadlock condition. In your code, simply issue the following:

UPDATE child_table SET ...;
UPDATE parent_table SET ...;
COMMIT;

This will perform the updates at relatively the same time and commit them both as one transaction.

The next thing to check is to ensure that your UPDATE statement is optimal. If your UPDATE statement takes 15 minutes...

Step 2 of 2:

You forgot to provide an Email Address.

This email address doesn’t appear to be valid.

This email address is already registered. Please login.

You have exceeded the maximum character limit.

Please provide a Corporate E-mail Address.
- I agree to TechTarget’s Terms of Use, Privacy Policy, and the transfer of my information to the United States for processing to provide me with relevant information as described in our Privacy Policy.
Please check the box if you want to proceed.
- I agree to my information being processed by TechTarget and its Partners to contact me via phone, email, or other means regarding information relevant to my professional interests. I may unsubscribe at any time.
Please check the box if you want to proceed.

to complete, then you have a 15-minute window where you could have a deadlock condition. Optimize your UPDATE statements just as you would optimize any SQL statement. Ensure that your UPDATE statements are using indexes where appropriate, etc.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

Meet all of our Oracle Database / Applications experts

View all Oracle Database / Applications questions and answers

SearchDataManagement

Dig Deeper on Oracle and SQL

Have a question for an expert?

Start the conversation

0 comments

Register

Login

Forgot your password?

Your password has been sent to:

Please create a username to comment.