Task #13354

Avoid nested set update on descriptions CSV import (reduce DB deadlocks)

Added by José Raddaoui Marín 4 months ago. Updated 4 months ago.

Status:VerifiedStart date:06/15/2020
Priority:MediumDue date:
Assignee:-% Done:

0%

Category:CSV import
Target version:Release 2.6.0
Google Code Legacy ID: Tested version:2.5, 2.6
Sponsored:No Requires documentation:No

Description

Problem:

In some cases, running a descriptions CSV import (either from the GUI with the worker or from the CLI) results in a database deadlock error, not finishing the import and leaving a damaged database.

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

While the CSV import is not executed within a transaction per se, it performs multiple nested set updates and those operations are similar to transactions as they create locks on the scanned records (more info). Depending on the amount of descriptions and where the import is targeted in the tree, this locks may take a long time and affect multiple rows. When the lock is active, any update to the affected rows like creating/deleting a description under a blocked one will cause the described error. It could happen in one process or the other depending on which one gets the lock first.

To reproduce:

The most direct way I found to reproduce the issue was running two CSV import tasks targeting the same description:

The CSV contained a few hundred rows and the target description was located around the middle of a 40K descriptions tree.

Mitigations/solutions/notes:

- We recently addressed the deadlock issue for web requests with retries (#12709)
- We're avoiding the nested set updates on hierarchy deletions until the end of the process (#13211 and #13239)
- A different transaction isolation level may improve the situation, although I don't think so (#13360)
- Removing the indexes for the lft and rgt columns may improve the nested set updates speed (#13359)
- Fully removing the nested set could be the closest to a final solution in this particular case (#13240)

The CSV import for descriptions already has an option to disable the nested set update entirely (--skip-nested-set-build), which is in there because nested set updates can take a long time compared with the build nested set task. So, apart from the performance improvements removing the individual nested set updates and including the nested set build within the CSV import task at the end, we will reduce the likelihood of deadlocks and, if something goes wrong, the situation won't be worst than it is right now.

See also #13352 to improve indexing and #13355 to improve memory management on the descriptions CSV import.

csv_import_deadlock.gif (551 KB) José Raddaoui Marín, 06/15/2020 09:19 PM


Related issues

Related to Access to Memory (AtoM) - Bug #13414: Siblings order is not maintained in archival descriptions... New 09/04/2020
Related to Access to Memory (AtoM) - Task #13385: Improve performance and reduce deadlocks on import processes New 07/16/2020

History

#1 Updated by José Raddaoui Marín 4 months ago

  • Description updated (diff)

#2 Updated by José Raddaoui Marín 4 months ago

  • Status changed from In progress to Code Review
  • Target version set to Release 2.6.0

#3 Updated by José Raddaoui Marín 4 months ago

  • Status changed from Code Review to QA/Review
  • Assignee deleted (José Raddaoui Marín)

Merged in qa/2.6.x.

Now, the nested set update is always disabled in a per row basis and, if the --skip-nested-set-build option is not used, it will be fully rebuilt right before the end of the task. To be tested alongside #13355 and #13352.

I'll follow up in this ticket with more details about the deadlock and performance improvements in the IOs CSV import after this changes.

#4 Updated by Dan Gillean 4 months ago

  • Status changed from QA/Review to Verified
  • Requires documentation set to No

Wow, this is actually pretty amazing - i'm not surprised there were previously errors, but being able to run two giant imports, each targeting a descendant in a large hierarchy, is impressive!

#5 Updated by José Raddaoui Marín 22 days ago

  • Related to Bug #13414: Siblings order is not maintained in archival descriptions CSV import added

#6 Updated by José Raddaoui Marín 22 days ago

  • Related to Task #13385: Improve performance and reduce deadlocks on import processes added

Also available in: Atom PDF