3

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:

  1. The site issues a "job" for refreshing the smart groups and data in the contact_cache table.
  2. 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.
  3. The user does not know the job is happening in the background, saves a contact, and produces an UPDATE query for the contact.
  4. 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

Thanks!

5

We have seen this on large instances. Things to do:

  • upgrade to 5.13.5 (this also puts you close to the next ESR - a paid for service - which will be released on Nov20).

There have been performance improvements - this will help.

  • cull your smart groups; do you really still need all of them.

  • reschedule the cron job -> ask yourself how up to date your smart groups need to be.

  • I discovered about 500+ smart groups on their install, and am going to recommend culling. We are also looking towards an upgrade anyway in the next few months, so that is good to know. I will also looking at manually scheduling when the cron job is executed. Thanks for your feedback and quick response! – joegl Oct 17 at 20:29
  • 1
    Glad I was able to help you out! – KarinG - Semper IT Oct 17 at 20:31
4

Karin's answer is a good one. However, not all smart groups are created equal! Culling the right smart groups is what will fix your deadlocks.

  • Identify your slow smart groups. Here's a script (rough, could use improvement) that you can run that will show you how long it takes to build each smart group. The latest version is available on my Github:
#!/usr/bin/php
<?php
// Use this code to see how long it takes to generate all of your smart groups to find slow ones.
eval(`cv php:boot`);
//Force a refresh of the cache 
$sql = 'UPDATE civicrm_group SET cache_date = NULL, refresh_date = NULL';
CRM_Core_DAO::executeQuery($sql);
// Get a list of smart group IDs.
$smartGroups = civicrm_api3('Group', 'get', [
  'return' => ["id", "title"],
  'options' => ['limit' => 0],
  'saved_search_id.id' => ['IS NOT NULL' => 1],
  'is_active' => 1,
  'is_hidden' => 0,
])['values'];

foreach ($smartGroups as $group) {
  $time_start = microtime(true);
  echo "{$group['title']} ({$group['id']}): ";
  CRM_Contact_BAO_GroupContactCache::loadAll($group['id']);
  $time_end = microtime(true);
  $executionTime = ($time_end - $time_start);
  echo "$executionTime seconds\n";
}

Running this on a site of mine, the vast majority of smart groups run in under .3 seconds. However, some run for as long as 211 seconds. Those are the ones causing deadlocks!

If you can't cull a particular smart group, here are some strategies you can use instead:

  • Don't nest your smart groups - that is, don't make a smart group "Vermont residents", a smart group "New Hampshire residents", then a smart group "New England Residents" whose members are the folks in the previous 2 (and more) smart groups. Instead, rewrite the "New England residents" group to include Vermont and New Hampshire residents directly, instead of relying on the nested smart groups.
  • Especially don't make a smart group made up of members of at least one smart group and one static group! For reasons too esoteric to discuss, this will completely kill performance.
  • Consider whether you can use an extension like CiviRules or SQL Tasks to replace the smart group. For instance, if you have a "Major Donors" smart group, defined as "Everyone who ever gave $500+ in a single donation", consider replacing it with a static group, and a CiviRule that adds donors to it when their donation exceeds your threshold.
  • The SQL command CiviCRM comes up with to determine a smart group is sometimes inefficient. Previously I wrote custom search extensions to optimize the SQL, but I've largely deprecated this approach in favor of SQL Tasks. Custom searches can still be helpful if end users need to be able to make variations on a particular query on their own.

Hopefully this will give you tools to best find and fix your slow smart groups and kill deadlocks for good!

  • 1
    Nice script John! Thank you for sharing. – KarinG - Semper IT Oct 18 at 16:37
  • Wow Jon this is excellent! I will review these guidelines with the CRM administrators and also try the script! Thanks so much. – joegl Oct 22 at 17:39
  • Hey Jon G! I am not sure where exactly I should be running the smart group profiler script from -- it uses a CiviCRM class that's not included and I am not sure how it knows what database to run the SQL on. Thanks! – joegl Nov 19 at 20:49
  • Jon G -- you can ignore my last comment -- I was able to build your script in a Drupal module that I could run and output the results. It was really helpful! If you're interested in the module code let me know! – joegl Nov 19 at 22:52
  • @joegl - Glad you got that to work, but I would usually run this from the command line: php smartgroupprofile.php. – Jon G - Megaphone Tech Nov 20 at 1:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.