Multi-site nested groups & smart groups can create circular logic

We recently found that users affected by the multi-site ACL hook were suffering from unacceptable performance ending in timeouts with the error below:

The issue turned out to be that a mailing group had been created based on the multi-site group that was also a child of it - ie. group 156 was a smartgroup based on membership in group 116 which was a parent of group 156. This logic seemed to not affect those with 'view all contacts' but those affected by the ACL hook were unable to view any contacts in group 156.

I think it would be worth doing something to prevent someone from creating this sort of circular group being created (although I realise it is a low-priority it might save future effort in support)

Error

DB Error: a515ac9c2796ca0e23adbe92c68fc9fc

Database Error Code: Deadlock found when trying to get lock; try restarting
transaction, 1213

Return to home page.

Error Details:

Array
(
[callback] => Array
(
[0] => CRM_Core_Error
[1] => handle
)

[code] => a515ac9c2796ca0e23adbe92c68fc9fc
[message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
[mode] => 16
[debug_info] => REPLACE INTO civicrm_group_contact_cache
(group_id,contact_id) VALUES
(116,71906),(116,73586),(116,71118),(116,68034),(116,71923),(116,75379),(116
,73633),(116,71889),(116,71880),(116,71597),(116,73614),(116,69719),(116,718
93),(116,75076),(116,73916),(116,73752),(116,73758),(116,71753),(116,71106),
(116,71933),(116,71762),(116,71885),(116,74552),(116,71725),(116,85891),(116
,73779),(116,75984),(116,71730),(116,72291),(116,82279),(116,71728),(116,728
55),(116,70360),(116,67458),(116,70826),(116,73704),(116,75271),(116,73693),
(116,73700),(116,71825),(116,74228),(116,71787),(116,71790),(116,71783),(116
,74439),(116,73719),(116,70347),(116,75971),(116,71795),(116,82362),(116,712
58),(116,72041),(116,73447),(116,72060),(116,71147),(116,73432),(116,74302),
(116,72874),(116,75317),(116,71265),(116,73468),(116,73465),(116,73455),(116
,73452),(116,73460),(116,72032),(116,70977),(116,75393),(116,73380),(116,733
84),(116,72094),(116,72097),(116,73373),(116,72760),(116,72919),(116,73420),
(116,72075),(116,92500),(116,75117),(116,70315),(116,73393),(116,73392),(116
,74561),(116,73399),(116,73557),(116,73896),(116,73562),(116,71948),(116,719
68),(116,72267),(116,82634),(116,82066),(116,82633),(116,73374),(116,71046),
(116,75916),(116,73578),(116,71637),(116,70452),(116,71850),(116,71938),(116
,72724),(116,73479),(116,73477),(116,82167),(116,73482),(116,73471),(116,727
95),(116,74855),(116,72946),(116,71274),(116,73172),(116,71925),(116,73524),
(116,91232),(116,72003),(116,73495),(116,74664),(116,75303),(116,71543),(116
,75599),(116,82146),(116,71054),(116,74010),(116,71555),(116,69701),(116,715
51),(116,71554),(116,82706),(116,72911),(116,72443),(116,75565),(116,71517),
(116,71535),(116,71537),(116,72205),(116,71532),(116,72478),(116,73962),(116
,71586),(116,86028),(116,72860),(116,71588),(116,74766),(116,73956),(116,739
94),(116,73991),(116,73989),(116,74610),(116,87860),(116,75897),(116,71568),
(116,75615),(116,71564),(116,74125),(116,75894),(116,73189),(116,71448),(116
,74113),(116,75960),(116,69859),(116,71454),(116,74154),(116,71101),(116,714
36),(116,74150),(116,71434),(116,74136),(116,71897),(116,74144),(116,72421),
(116,71861),(116,74068),(116,74066),(116,74076),(116,74073),(116,71504),(116
,71508),(116,72824),(116,75231),(116,73934),(116,71475),(116,71483),(116,741
08),(116,71466),(116,73634),(116,71493),(116,74087),(116,71936),(116,71655),
(116,71659),(116,73873),(116,71648),(116,71668),(116,75488),(116,73861),(116
,73859),(116,71599),(116,74389),(116,71644); [nativecode=1213 ** Deadlock
found when trying to get lock; try restarting transaction]
[type] => DB_Error
[user_info] => REPLACE INTO civicrm_group_contact_cache
(group_id,contact_id) VALUES
(116,71906),(116,73586),(116,71118),(116,68034),(116,71923),(116,75379),(116
,73633),(116,71889),(116,71880),(116,71597),(116,73614),(116,69719),(116,718
93),(116,75076),(116,73916),(116,73752),(116,73758),(116,71753),(116,71106),
(116,71933),(116,71762),(116,71885),(116,74552),(116,71725),(116,85891),(116
,73779),(116,75984),(116,71730),(116,72291),(116,82279),(116,71728),(116,728
55),(116,70360),(116,67458),(116,70826),(116,73704),(116,75271),(116,73693),
(116,73700),(116,71825),(116,74228),(116,71787),(116,71790),(116,71783),(116
,74439),(116,73719),(116,70347),(116,75971),(116,71795),(116,82362),(116,712
58),(116,72041),(116,73447),(116,72060),(116,71147),(116,73432),(116,74302),
(116,72874),(116,75317),(116,71265),(116,73468),(116,73465),(116,73455),(116
,73452),(116,73460),(116,72032),(116,70977),(116,75393),(116,73380),(116,733
84),(116,72094),(116,72097),(116,73373),(116,72760),(116,72919),(116,73420),
(116,72075),(116,92500),(116,75117),(116,70315),(116,73393),(116,73392),(116
,74561),(116,73399),(116,73557),(116,73896),(116,73562),(116,71948),(116,719
68),(116,72267),(116,82634),(116,82066),(116,82633),(116,73374),(116,71046),
(116,75916),(116,73578),(116,71637),(116,70452),(116,71850),(116,71938),(116
,72724),(116,73479),(116,73477),(116,82167),(116,73482),(116,73471),(116,727
95),(116,74855),(116,72946),(116,71274),(116,73172),(116,71925),(116,73524),
(116,91232),(116,72003),(116,73495),(116,74664),(116,75303),(116,71543),(116
,75599),(116,82146),(116,71054),(116,74010),(116,71555),(116,69701),(116,715
51),(116,71554),(116,82706),(116,72911),(116,72443),(116,75565),(116,71517),
(116,71535),(116,71537),(116,72205),(116,71532),(116,72478),(116,73962),(116
,71586),(116,86028),(116,72860),(116,71588),(116,74766),(116,73956),(116,739
94),(116,73991),(116,73989),(116,74610),(116,87860),(116,75897),(116,71568),
(116,75615),(116,71564),(116,74125),(116,75894),(116,73189),(116,71448),(116
,74113),(116,75960),(116,69859),(116,71454),(116,74154),(116,71101),(116,714
36),(116,74150),(116,71434),(116,74136),(116,71897),(116,74144),(116,72421),
(116,71861),(116,74068),(116,74066),(116,74076),(116,74073),(116,71504),(116
,71508),(116,72824),(116,75231),(116,73934),(116,71475),(116,71483),(116,741
08),(116,71466),(116,73634),(116,71493),(116,74087),(116,71936),(116,71655),
(116,71659),(116,73873),(116,71648),(116,71668),(116,75488),(116,73861),(116
,73859),(116,71599),(116,74389),(116,71644); [nativecode=1213 ** Deadlock
found when trying to get lock; try restarting transaction]
[to_string] => [db_error: message="DB Error:
a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback