1

Each year we go through an enrollment process, where we have groups of up to 60 users at a time registering for our site simultaneously on our one-CPU, 3GB virtual machine running the latest CiviCRM with Drupal.

We authenticate users using ADFS through SimpleSAMLphp - their user accounts are created in Drupal when, as part of the enrollment process, they click on a link that logs them in and redirects to a webform with the info we want to gather for Civi. i.e. /saml_login?destination=webform

Last year was a laugh-and-a-half when Apache (with mod_php) decided that was all far too much for it to handle, and killed MySQL hard in the process. A few times. Have since switched to Nginx with PHP-FPM and am now sleeping far better at night.

I have noted that about 10% of users are encountering a CiviCRM 'yellow box on white page' error - don't have the exact error message to hand, but to the effect of 'unknown error'.

Looked up the logs, and it's throwing a MySQL error:

[nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]

When attempting to run the query:

INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES .....

My cursory searching shows a few mentions of this kind of situation in JIRA, but there wasn't much to go on. If anyone has any suggestions of how I'd go about avoiding this happening in the future, I'd be glad to hear them!

System stats from today's enrollment process

PHP-FPM, Nginx and MySQL Stats

Edit:

Aug 24 10:12:39  [info] $Fatal Error Details = Array
(
    [callback] => Array
        (
            [0] => CRM_Core_Error
            [1] => handle
        )

    [code] => -1
    [message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 69088 ,  1 , '[email protected]' ,  1 ,  0 ,  0 )  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 69088 ,  1 , '[email protected]' ,  1 ,  0 ,  0 )  [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::handle prefix="" info="INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary , is_billing , on_hold ) VALUES ( 69088 ,  1 , '[email protected]' ,  1 ,  0 ,  0 )  [nativecode=1213 ** Deadlock found when trying to get lock; try restarting transaction]"]
)


Aug 24 10:12:39  [info] $backTrace = #0 /var/www/html/sites/all/modules/civicrm/CRM/Core/Error.php(232): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(921): CRM_Core_Error::handle(Object(DB_Error))
#2 /var/www/html/sites/all/modules/civicrm/packages/DB.php(985): PEAR_Error->__construct("DB Error: unknown error", -1, 16, (Array:2), "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#3 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(575): DB_Error->__construct(-1, 16, (Array:2), "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#4 /var/www/html/sites/all/modules/civicrm/packages/PEAR.php(223): PEAR->_raiseError(Object(DB_mysqli), NULL, -1, 16, (Array:2), "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...", "DB_Error", TRUE)
#5 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1907): PEAR->__call("raiseError", (Array:7))
#6 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(933): DB_common->raiseError(-1, NULL, NULL, "INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...", "1213 ** Deadlock found when trying to get lock; try restarting transaction")
#7 /var/www/html/sites/all/modules/civicrm/packages/DB/mysqli.php(403): DB_mysqli->mysqliRaiseError()
#8 /var/www/html/sites/all/modules/civicrm/packages/DB/common.php(1216): DB_mysqli->simpleQuery("INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#9 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(2415): DB_common->query("INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#10 /var/www/html/sites/all/modules/civicrm/packages/DB/DataObject.php(1040): DB_DataObject->_query("INSERT INTO civicrm_email (contact_id , location_type_id , email , is_primary...")
#11 /var/www/html/sites/all/modules/civicrm/CRM/Core/DAO.php(571): DB_DataObject->insert()
#12 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/Email.php(102): CRM_Core_DAO->save()
#13 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/Block.php(348): CRM_Core_BAO_Email::add((Array:5))
#14 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/Location.php(66): CRM_Core_BAO_Block::create("email", (Array:12), NULL)
#15 /var/www/html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(344): CRM_Core_BAO_Location::create((Array:12), TRUE)
#16 /var/www/html/sites/all/modules/civicrm/CRM/Contact/BAO/Contact.php(2010): CRM_Contact_BAO_Contact::create((Array:12))
#17 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/UFMatch.php(298): CRM_Contact_BAO_Contact::createProfileContact((Array:4), (Array:0))
#18 /var/www/html/sites/all/modules/civicrm/CRM/Core/BAO/UFMatch.php(118): CRM_Core_BAO_UFMatch::synchronizeUFMatch(Object(stdClass), "11667", "[email protected]", "Drupal", NULL, "Individual", FALSE)
#19 /var/www/html/sites/all/modules/civicrm/drupal/civicrm_user.inc(51): CRM_Core_BAO_UFMatch::synchronize(Object(stdClass), FALSE, "Drupal", "Individual")
#20 /var/www/html/modules/user/user.module(101): civicrm_user_login((Array:2), Object(stdClass), NULL)
#21 /var/www/html/modules/user/user.module(2308): user_module_invoke("login", (Array:2), Object(stdClass))
#22 /var/www/html/modules/user/user.module(2321): user_login_finalize((Array:2))
#23 /var/www/html/modules/user/user.module(2351): user_login_submit((Array:0), (Array:2))
#24 /var/www/html/sites/all/modules/contrib/simplesamlphp_auth/simplesamlphp_auth.module(241): user_external_login_register("[email protected]", "simplesamlphp_auth")
#25 /var/www/html/includes/module.inc(957): simplesamlphp_auth_init()
#26 /var/www/html/includes/common.inc(5314): module_invoke_all("init")
#27 /var/www/html/includes/bootstrap.inc(2538): _drupal_bootstrap_full()
#28 /var/www/html/index.php(20): drupal_bootstrap(7)
#29 {main}
Improve this question
12
  • How much memory do have available for CiviCRM and what CMS are you using?
    – Christia
    Aug 22, 2018 at 18:23
  • PHP's memory_limit is set at 256mb (probably a bit generous, but some large views wern't happy with 128mb) and Drupal 7.59. Have also added in some stats from today in the main post.
    – termdark
    Aug 22, 2018 at 18:54
  • Some work has been done related to various deadlock issues recently. Are you on the latest CiviCRM version? If not, do an upgrade and see if it works for you.
    – jitendra - fuzion
    Aug 23, 2018 at 4:18
  • 1
    unrelated question: what do you use for monitoring?
    – Xavier
    Aug 23, 2018 at 6:05
  • Usually deadlocks could be caused by smarty groups. But your query does not indicate a smart group.
    – Jaap Jansma - CiviCooP
    Aug 23, 2018 at 6:10

1 Answer 1

Reset to default
1

Concurrency problems are tricky to reproduce, and therefore solve.

If you have the time and skill, writing a small scrapper that would automate submitting the form would be super helpful to create several users at the same time and hopefully reproduce your problem.

This being said, let's shoot from the hip, see if we can guess:

Are you creating several contacts from your webform? eg. creating both the individual and current employer, and setting emails addresses for both?

Can you get a calltrace of when the deadlock occurs? that would help narrowing down what triggers it

Improve this answer
3
  • When you want to go for the solution of seperate forms. Try to use the form processor extenion in civicrm and the cmrf_webform module in drupal. The cmrf_webform module lets you submit the forms to civicrm in the background. That way you can control when the data is submitted and not all at once. (Spread the load). See civicrm.org/blog/jaapjansma/… for instructions.
    – Jaap Jansma - CiviCooP
    Aug 23, 2018 at 6:12
  • Thanks for your answer Xavier. Should have clarified on the original post that the errors are coming up on Drupal user creation rather than on webform submission. That's no problem as it's only updating one contact record, and usually the users are submitting that at slightly different times. Problem is that when the facilitator tells the group to click on the link to the form, they do so at the one time, creating up to 70 Drupal accounts in just a few seconds! Will add the trace in.
    – termdark
    Aug 23, 2018 at 16:54
  • We have experienced the same deadlock error on a petition. Also deadlock on inserting into email table. According to SHOW ENGINE INNODB STATUS;: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 69 page no 263 n bits 880 index 'FK_civicrm_email_contact_id' of table 'xxxxx'.'civicrm_email' trx table locks 4 total table locks 2 trx id 4923387 lock_mode X insert intention waiting lock hold time 1 wait time before grant 0 which seems to suggest the creation of the email record is waiting for the civicrm_contact table to be available so it can check the fk constraint.
    – Jamie
    Sep 11, 2018 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.