Answers.Body: <p>That pretty much sums it up. We have fixed similar problems by changing the Smart Group cache timeout via civicrm/admin/setting/search?reset=1, alongside setting a limit to the Scheduled Job so that only X groups would be considered for each job run.</p>
  <p>In another situation we did the following (pasted from our ticket system).</p>
  <blockquote>
  <ul>
  <li>set cache clearing to deterministic drush cvapi setting.create smart_group_cache_refresh_mode=deterministic</li>
  <li>created a scheduled job to run always job.group_cache_flush</li>
  </ul>
  <p>Outcome was clearing the civicrm_group_contact_cache after the 5 minute time we allow caches to be valid.</p>
  <p>This should resolve deadlocks that occur during cache clearing. It will not resolve deadlocks that may occur during cache rebuilding</p>
  </blockquote>
  <p>Hopefully those give you some ideas of ways to progress</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-11-19T18:33:27.280Z
Answers.Text: What did you set the smart group cache timeout to?
Answers.UserDisplayName: Matt Muchowski
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-23T20:03:03.313Z
Answers.LastActivityDate: 2018-05-23T20:03:03.313Z
Answers.LastEditDate: 2020-06-17T09:00:50.810Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>I have helped out a client with similair deadlock problems and most of the time it turned out that the confifuration of a particilair smart group was causing this. </p>
  
  <p>But finding out which group is causing it is the pain. Sometimes the query causing the deadlock indicates group id. So my suggestion would be try to have a look at group with id 910.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-12-13T02:58:58.850Z
Answers.Text: I have figured out which group is causing the problem but what should I look for as far as "the configuration of that particular smart group"?
Answers.UserDisplayName: Christia
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-05-24T08:01:19.537Z
Answers.LastActivityDate: 2018-05-24T08:01:19.537Z
Answers.UserDisplayName: Jaap Jansma - CiviCooP
Body: <p>Sorry in advance for my lack of familiarity with civi, I'm not the most experienced member of the team.</p>
  
  <p>We seem to be running into a problem with the Rebuild Smart Group Cache job where the cpu will completely max out every time it is run. I looked deeper into the issue in the log and have gotten the following output:</p>
  
  <pre><code>[info] $Fatal Error Details = Array
  (
      [callback] =&gt; Array
          (
              [0] =&gt; CRM_Core_Error
              [1] =&gt; exceptionHandler
          )
  
      [code] =&gt; -1
      [message] =&gt; DB Error: unknown error
      [mode] =&gt; 16
      [debug_info] =&gt;
      DELETE  g
        FROM  civicrm_group_contact_cache g
        WHERE  g.group_id = 120  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
      [type] =&gt; DB_Error
      [user_info] =&gt;
      DELETE  g
        FROM  civicrm_group_contact_cache g
        WHERE  g.group_id = 120  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
      [to_string] =&gt; [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="
      DELETE  g
        FROM  civicrm_group_contact_cache g
        WHERE  g.group_id = 120  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]"]
  )
  </code></pre>
  
  <p>It has also happened on the query</p>
  
  <pre><code>       [user_info] =&gt; INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
          SELECT DISTINCT id, group_id FROM civicrm_temp_group_contact_cache910
         [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
  </code></pre>
  
  <p>Is this at all related to <a href="https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement" rel="noreferrer">https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement</a> ? And would it be alleviated by changing the caching setting mentioned in the blog post?</p>
  
Tags: <smartgroups>
Title: Deadlocks in Rebuild Smart Group Cache
ThreadText: # Deadlocks in Rebuild Smart Group Cache
  
  (Asked by: username_0 on 2018-05-23)
  
  Sorry in advance for my lack of familiarity with civi, I'm not the most experienced member of the team.
  
  We seem to be running into a problem with the Rebuild Smart Group Cache job where the cpu will completely max out every time it is run. I looked deeper into the issue in the log and have gotten the following output:
  
  ```
  [info] $Fatal Error Details = Array
  (
      [callback] => Array
          (
              [0] => CRM_Core_Error
              [1] => exceptionHandler
          )
  
      [code] => -1
      [message] => DB Error: unknown error
      [mode] => 16
      [debug_info] =>
      DELETE  g
        FROM  civicrm_group_contact_cache g
        WHERE  g.group_id = 120  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
      [type] => DB_Error
      [user_info] =>
      DELETE  g
        FROM  civicrm_group_contact_cache g
        WHERE  g.group_id = 120  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
      [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::exceptionHandler prefix="" info="
      DELETE  g
        FROM  civicrm_group_contact_cache g
        WHERE  g.group_id = 120  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]"]
  )
  
  ```
  
  It has also happened on the query
  
  ```
         [user_info] => INSERT IGNORE INTO civicrm_group_contact_cache (contact_id, group_id)
          SELECT DISTINCT id, group_id FROM civicrm_temp_group_contact_cache910
         [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
  
  ```
  
  Is this at all related to <https://civicrm.org/blog/eileen/478-group-contact-cache-deadlocks-improvement> ? And would it be alleviated by changing the caching setting mentioned in the blog post?
  
  **Tags:** smartgroups
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2018-05-23. Score: 1
  
  That pretty much sums it up. We have fixed similar problems by changing the Smart Group cache timeout via civicrm/admin/setting/search?reset=1, alongside setting a limit to the Scheduled Job so that only X groups would be considered for each job run.
  
  In another situation we did the following (pasted from our ticket system).
  
  > * set cache clearing to deterministic drush cvapi setting.create smart\_group\_cache\_refresh\_mode=deterministic
  > * created a scheduled job to run always job.group\_cache\_flush
  >
  > Outcome was clearing the civicrm\_group\_contact\_cache after the 5 minute time we allow caches to be valid.
  >
  > This should resolve deadlocks that occur during cache clearing. It will not resolve deadlocks that may occur during cache rebuilding
  
  Hopefully those give you some ideas of ways to progress
  
  > **Comments:**
  > **username_2**: What did you set the smart group cache timeout to?  
  
  ---
  
  ### Answer by username_3 on 2018-05-24. Score: 0
  
  I have helped out a client with similair deadlock problems and most of the time it turned out that the confifuration of a particilair smart group was causing this.
  
  But finding out which group is causing it is the pain. Sometimes the query causing the deadlock indicates group id. So my suggestion would be try to have a look at group with id 910.
  
  > **Comments:**
  > **username_4**: I have figured out which group is causing the problem but what should I look for as far as "the configuration of that particular smart group"?  
  
  ---
  
  