The exact query and issue is below.
UPDATE civicrm_contact SET contact_type = 'Individual' , contact_sub_type = NULL , sort_name = 'XXXXXX XXXXXX' , display_name = 'XXXXXX XXXXXX' , first_name = 'XXXXXX' , last_name = 'XXXXXX' , email_greeting_id = 6 , postal_greeting_id = 1 , addressee_id = 1 WHERE ( civicrm_contact.id = 27636 ) [nativecode=1205 ** Lock wait timeout exceeded; try restarting transaction]
There are also a number of other "database deadlock" errors cropping up in the CiviCRM log.
Essentially, the person using the CRM is complaining that randomly they'll be editing a contact and it will fail to save, instead simply timing out (I haven't reproduced the behavior but I trust it).
After some research/reading, it appears CiviCRM has a contact_cache table in regards to Smart Groups it refreshes periodically via CRON. This specific CiviCRM install has thousands of contacts, and many large Smart Groups. So what I assume is happening is:
- The site issues a "job" for refreshing the smart groups and data in the contact_cache table.
- While the "job" is running, every single contact record affected by the smart group is "locked" in the database, and cannot be altered until the "job" finishes.
- The user does not know the job is happening in the background, saves a contact, and produces an UPDATE query for the contact.
- If the contact record is locked, it waits a certain specified amount of time (innodb_wait_timeout I believe) for the row to be unlocked, before failing the query altogether.
My problem is, if the above is true, I don't know how I can resolve the problem. Upping the timeout from 50 seconds to 120 seconds for example, would only increase the amount of time they wait for the request to fail, because the Smart Groups take over 120 seconds to be refreshed.
Other relevant info: Drupal 7, CiviCRM 5.9.1, PHP 7.0.33