Answers.Body: <p>Instead of trying to "hide" the problem when it happens, it might be safer/wiser to try to prevent it.</p>
  
  <p>I like smartgroups and how powerful they are, but I found that it works better if I cache the results for a while (eg. a day) and rebuild the smart groups via the jobs.</p>
  
  <p>Would it be acceptable to keep a "stalled" version of your groups for a while and avoid rebuilding them all the time in your case?</p>
  
  <p>Also, you mention having a lot of smartgroups, it might be worthwhile creating a custom search that would run the queries you want when you need to use them instead of creating lots of smartgroups. We found it more performant and easier to maintain.</p>
  
  <p>X+</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-28T20:00:29.550Z
Answers.Text: We've done this to an extent, but we're heavy users of smart groups (well over 1000) and the staff make new ones every day. They would be quite confused / annoyed if their groups didn't update right away. We did some analysis of the most time consuming groups - as well as the 'base' groups that many others rely on - and turned these into regular groups that populate overnight. This helped a lot. To be honest I'm quite happy with the setup as it is: the deadlocks get caught and retried, and very rarely fail. It's just the UI deadlocks that are a difficulty.
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-31T07:18:13.237Z
Answers.Text: I would be interested in how you analyzed the slowest smartgroups. We also experience deadlock problems with too many and too complex smartgroups
Answers.UserDisplayName: Richard van Oosterhout
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-03T09:22:26.743Z
Answers.Text: @RichardvanOosterhout we used a combination of two things. First we use New Relic monitoring, which lets us drill down into slow queries. Secondly I added basic time logging to Contact/BAO/GroupContactCache::load() - basically just starting a timer at the start of the function, stopping it at the end, and logging it along with the group ID. Quite revealing!
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-04T10:06:42.983Z
Answers.Text: nice! would it be possible you can show me the code you inserted for that?
Answers.UserDisplayName: Richard van Oosterhout
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-09-14T19:13:08.387Z
Answers.Text: oops sorry - missed this. Email me at awest@humanism.org.uk and I'll send it over (just because it's not easy to put code in these comments)
Answers.UserDisplayName: Andrew West
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2018-08-28T14:04:37.163Z
Answers.LastActivityDate: 2018-08-28T14:04:37.163Z
Answers.UserDisplayName: Xavier
Body: <p>We have a lot of smart groups, and consequently a lot of deadlocks. There's already <a href="https://github.com/civicrm/civicrm-packages/pull/197/files" rel="nofollow noreferrer">code in Civi</a> that detects deadlock Exceptions, and then retries the query a few times. This is really helpful - I have extra logging on this, and I can see that it prevents a lot of errors. But this code doesn't kick in for people directly using the UI - they get the 'unknown error' yellow-screen-of-death immediately. This can happen on exports, or when opening complex groups. I dug into this and sort-of know why it's happening:</p>
  
  <ul>
  <li>When a deadlock happens, an exception is thrown if <code>$GLOBALS['_PEAR_default_error_options'][1]</code> is set to 'exceptionHandler'. This exception triggers the code which retries the query.</li>
  <li>An exception is <strong>not</strong> thrown if <code>$GLOBALS['_PEAR_default_error_options'][1]</code> is set to 'handle'.</li>
  <li><code>$GLOBALS['_PEAR_default_error_options'][1]</code> is set to 'exceptionHandler' when you generate smart groups via the API, and in most other circumstances</li>
  <li>But <code>$GLOBALS['_PEAR_default_error_options'][1]</code> is set to 'handle' in the UI in general (?) - at least, it is on exports and when using 'Manage Groups'. So in this case no exception is raised. And if there's no exception, the try-catch blocks have nothing to work with and front-end users get 'unknown error' immediately.</li>
  </ul>
  
  <p>I am happy to fund a fix for this, but I'm not sure what I'm getting myself into. Is this a bug, or is it an unfortunate consequence of how error handling needs to work for end users?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-27T13:37:38.683Z
Comments.Text: Can you share which CMS and which version of CiviCRM you are running?
Comments.UserDisplayName: Peter Petrik
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2018-08-28T19:46:26.243Z
Comments.Text: WordPress and Civi 5.3.1
Comments.UserDisplayName: Andrew West
Tags: <smartgroups><deadlocks><errorhandling>
Title: Deadlocked queries cause an instant error for end-users, but are retried in other circumstances
ThreadText: # Deadlocked queries cause an instant error for end-users, but are retried in other circumstances
  
  (Asked by: username_0 on 2018-08-23)
  
  We have a lot of smart groups, and consequently a lot of deadlocks. There's already [code in Civi](https://github.com/civicrm/civicrm-packages/pull/197/files) that detects deadlock Exceptions, and then retries the query a few times. This is really helpful - I have extra logging on this, and I can see that it prevents a lot of errors. But this code doesn't kick in for people directly using the UI - they get the 'unknown error' yellow-screen-of-death immediately. This can happen on exports, or when opening complex groups. I dug into this and sort-of know why it's happening:
  
  * When a deadlock happens, an exception is thrown if `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'exceptionHandler'. This exception triggers the code which retries the query.
  * An exception is **not** thrown if `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'handle'.
  * `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'exceptionHandler' when you generate smart groups via the API, and in most other circumstances
  * But `$GLOBALS['_PEAR_default_error_options'][1]` is set to 'handle' in the UI in general (?) - at least, it is on exports and when using 'Manage Groups'. So in this case no exception is raised. And if there's no exception, the try-catch blocks have nothing to work with and front-end users get 'unknown error' immediately.
  
  I am happy to fund a fix for this, but I'm not sure what I'm getting myself into. Is this a bug, or is it an unfortunate consequence of how error handling needs to work for end users?
  
  **Tags:** smartgroups, deadlocks, errorhandling
  
  > **Comments:**
  > **username_1**: Can you share which CMS and which version of CiviCRM you are running?  
  > **username_0**: WordPress and Civi 5.3.1  
  
  ---
  
  ## 1 answers
  
  ### Answer by username_2 on 2018-08-28. Score: 1
  
  Instead of trying to "hide" the problem when it happens, it might be safer/wiser to try to prevent it.
  
  I like smartgroups and how powerful they are, but I found that it works better if I cache the results for a while (eg. a day) and rebuild the smart groups via the jobs.
  
  Would it be acceptable to keep a "stalled" version of your groups for a while and avoid rebuilding them all the time in your case?
  
  Also, you mention having a lot of smartgroups, it might be worthwhile creating a custom search that would run the queries you want when you need to use them instead of creating lots of smartgroups. We found it more performant and easier to maintain.
  
  X+
  
  > **Comments:**
  > **username_0**: We've done this to an extent, but we're heavy users of smart groups (well over 1000) and the staff make new ones every day. They would be quite confused / annoyed if their groups didn't update right away. We did some analysis of the most time consuming groups - as well as the 'base' groups that many others rely on - and turned these into regular groups that populate overnight. This helped a lot. To be honest I'm quite happy with the setup as it is: the deadlocks get caught and retried, and very rarely fail. It's just the UI deadlocks that are a difficulty.  
  > **username_3**: I would be interested in how you analyzed the slowest smartgroups. We also experience deadlock problems with too many and too complex smartgroups  
  > **username_0**: @RichardvanOosterhout we used a combination of two things. First we use New Relic monitoring, which lets us drill down into slow queries. Secondly I added basic time logging to Contact/BAO/GroupContactCache::load() - basically just starting a timer at the start of the function, stopping it at the end, and logging it along with the group ID. Quite revealing!  
  > **username_3**: nice! would it be possible you can show me the code you inserted for that?  
  > **username_0**: oops sorry - missed this. Email me at awest@humanism.org.uk and I'll send it over (just because it's not easy to put code in these comments)  
  
  ---
  
  