Q
Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

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.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

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

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close