Ingest fails and errors report deadlock on insert

[jaredgalanis]

Ingests sometimes fail with an error report describing a serialization failure and deadlock when attempting an insert into the database.

This most recently occurred in production on March 25, 2022.

There has been some discussion by the team around this issue and it was considered whether or not we might set isolation levels to READ COMMITTED per this Drupal issue: https://www.drupal.org/project/drupal/issues/1650930. This was never attempted as far as we are aware. If it were to be set it probably would need to be set with a drush command during the PHP.ini script.

It's possible that we never attempted to resolve the issue in part because a simple re-attempt of an ingest does some times alleviate the issue and also because there was some research which indicated that setting isolation levels might only resolve read operation issues and we are hitting these deadlocks on write operations. See:

• https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html
• https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks.html
• https://dev.mysql.com/doc/refman/8.0/en/innodb-deadlocks-handling.html