CiviCRM Community Forums (archive)

I´m trying to make a simple search by a group of contacts and I´m getting the following error:


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

"Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

[code1] => a515ac9c2796ca0e23adbe92c68fc9fc
    [message] => DB Error: a515ac9c2796ca0e23adbe92c68fc9fc
    [mode] => 16
    [debug_info] => REPLACE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES (13,4856),(13,4857),.....(13,4860); [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
    [to_string] => [db_error: message="DB Error: a515ac9c2796ca0e23adbe92c68fc9fc" code=0 mode=callback callback=CRM_Core_Error::handle prefix="" info="REPLACE INTO civicrm_group_contact_cache (group_id,contact_id) VALUES (13,4856),........(13,4860); [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]"]
)"

It looks that there is some kind of problem with the civicrm_group_contact_cache table, but I´m nor sure how to solve it. Is there a way to empty this table with drush or something??

Any suggestions would be helpful..

Regards,

Ok, I could try to TRUNCATE this table, but I´m just concerned about what I´m going to loose here. Is this table used for smart groups??

Thanks in advance..

Are you using hierarchical groups. This situation arises if you have a group that is a subgroup of another group but also has that other group in it's criteria.

There was a minor fix to the way the cache is truncated that went in at one point - I can't remember if it was before or after 3.4.2 though. It didn't fix the circular group problem which is where a group has it's parent group in the criteria (which produces the symptoms you describe but which you seem to have dealt to now..

ok.  I deleted the smart group and recreated.  Seemed to have fixed the issue..... for now.

Where I have seen this the problem is that it is dealing with a hierarchical group

e.g parent group is "NSW State"
child group is "All pedestrians in NSW state"

People use the parent group as criteria in the child group - to narrow it down - but when the cache is rebuilt it has to define the parent to define the child to define the parent to define the child.

I think a good enhancement would be to allow child groups to restrict parent groups.

DaveJ - this problem is especially prevalent in multisites

Dave - can you describe the criteria of the groups in question?

BTW - am at the sprint starting tomorrow so hope to pick people's brains :-)

For the deadlock-error-in-Edit-Search-Criteria issue...

I've made progress with this. It was occurring on a 4.0.8 site and I was going to try a patch of Tim Otten's for CRM/Contact/BAO/GroupContactCache.php but found that there had been other changes to that file between 4.0.8 and the base version of Tim's patch. One of these was changeset 38384 for CRM-9493 . I tried applying that change first and it fixed the problem - well, I saw the problem once in tens of trials with the patch, compared to seeing it nearly every time without.

I tested each of the two parts of the patch separately, to see which was making a difference. I found that this was the one:

+        // sort the values so we put group IDs in front and hence optimize
+        // mysql storage (or so we think) CRM-9493
+        sort( $values );

Thinking of further optimisation, I was wondering whether the pattern currently used:

DELETE g
FROM civicrm_group_contact_cache g
WHERE g.group_id IN ( '7' )

then

INSERT IGNORE INTO civicrm_group_contact_cache (group_id,contact_id) <thousands of values>

is maybe a bit wasteful, given that writes are expensive. Wondered whether it might be possible to insert/delete only the values that differ between what's already there and what we want. That could involve holding more stuff in memory, though.

Cheers,

Dave J