How do I avoid issues caused by cascading deleting of objects?
Environment:
Oracle B2C Service Product versions 21C and below
Resolution:
Use extreme caution when deleting multiple records. When records have been deleted, they are completely removed from the database and cannot be retrieved. The cascading nature of object deletes can cause unexpected site database activity. Depending on the volume of deletes in combination with other site activity this can cause performance issues and even errors, and particularly when deleting incident objects.
The following describes the underlying objects also deleted for the main object types to consider:
-
Organizations - deletes all contacts and incidents in addition to the organization
-
Contacts - deletes all the incidents for the contacts in addition to the contact
-
Incidents - deletes from all the associated table data including but not limited to transactions, inc_performance, and incidents
-
Accounts - in addition to deleting accounts updates references to the account in but not limited to transactions and inc_performance
Symptoms:
- Slowness on site
- Lock wait timeout errors in error_log.xml.
- Deadlocks on a table in the error_log.xml
- Max user connections in the error_log.xml
Avoiding:
To avoid issues associated with cascading object deletes take care to avoid deleting over 250 of the primary object types at a time. For example, if a contact is to be deleted take care to first analyze how many incidents are associated to the contact, and delete them in 250 record increments before deleting the contact record itself. It can also be beneficial to choose to delete objects during slow times for the site and pod if possible.
For further information specific to deleting objects using public APIs see Answer ID 8887: Optimal efficiency when destroying objects using CWS API.
Additional Info:
To learn about the new orphan sweep feature that started in 21D and newer version please seeAnswer ID 8008: Deleting records with a cascade and the orphan sweep
