Uploaded image for project: 'CiviCRM'
  1. CiviCRM
  2. CRM-17463

Deadlocks on group_cache clearing


    • Type: Bug
    • Status: Done/Fixed
    • Priority: Major
    • Resolution: Fixed/Completed
    • Affects Version/s: 4.6.9
    • Fix Version/s: 4.7.9
    • Component/s: None


      We have been experiencing significant issues with Deadlocks on the group contact cache table since upgrading from 4.4 to 4.6.

      I don't think the issue I'm reporting here is new to 4.6 - I think the issue is the previous work-around is broken & hence I'm looking at the next issue along the chain.

      On 4.4 our heaviest group user effectively disabled smart group caching because it caused more performance problems than it solved. However, in 4.6 this doesn't seem to work because using TRUNCATE breaks civimail in scary & nasty ways - so setting smart group timeout to 0 can trigger this :

      if (!isset($groupID)) {
      if ($smartGroupCacheTimeout == 0) {
      $query = "
      TRUNCATE civicrm_group_contact_cache

      However, looking at why the query causes such severe outcome there are 2 issues

      1) the degree of locking
      2) the speed of the query.

      The locking issue appears to be that when any of the queries intended to update the group_contact cache table or relevant fields in the group table run they lock the entire table (s) rather than the rows due to lack of indexes.

      This query

      DELETE gc
      FROM civicrm_group_contact_cache gc
      INNER JOIN civicrm_group g ON g.id = gc.group_id
      WHERE TIMESTAMPDIFF(MINUTE, g.cache_date, $now) >= $smartGroupCacheTimeout

      Does a 'WHERE' on the unindexed field 'cache_date'

      According to this link http://dev.mysql.com/doc/refman/5.6/en/innodb-lock-modes.html

      " Rows are locked during updates using indexes. For example,

      update tab set col1=3 where col2=17;

      will lock the entire table unless col2 is indexed (in which case, only rows where col2=17 will be locked)."

      (the comment is old but someone has commented to the same effect later on)

      The columns that I believe should be indexed (and converted to timestamp) are


      Given that we are experiencing considerable instability around group caching on 4.6 I would want this indexes to be included in the 4.6 LTS. However, we'll do some live testing of adding the indexes first.

      I'll post the sql we are using to this ticket.


          Issue Links



              • Assignee:
                eileen Eileen McNaughton
                eileen Eileen McNaughton
              • Votes:
                0 Vote for this issue
                7 Start watching this issue


                • Created: