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

You have exceeded the maximum character limit.

Please provide a Corporate Email 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.

NHS to digitise the ‘red book’ by 2023

How to resolve and avoid deadlocks in SQL Server databases

How many children are NOT being stalked on Snapchat

Lords call on ISPs, government to do more to safeguard children online