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...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
Step 2 of 2:
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.
