Deadlocked queries cause an instant error for end-users, but are retried in other circumstances

Question

We have a lot of smart groups, and consequently a lot of deadlocks. There's already code in Civi 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?

Answer 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+