I am seeing occasional errors when webform 4.x is under load (many webform submissions coming in per second, due to a video going viral :)

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '1-121874' for key 'nid_serial': INSERT INTO {webform_submissions} (nid, serial, uid, is_draft, submitted, remote_addr) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5); Array ( [:db_insert_placeholder_0] => 1 [:db_insert_placeholder_1] => 121874 [:db_insert_placeholder_2] => 0 [:db_insert_placeholder_3] => 0 [:db_insert_placeholder_4] => 1405460218 [:db_insert_placeholder_5] => 50f8:947e:ed63:e3d8:f803:7c70:361:36e0 ) in drupal_write_record() (line 7207 of /drupal/docs/includes/common.inc).

The cases I looked at so far involved two different anonymous users trying to use the same serial number.

We have webform preview enabled, in case that is a variable - I noticed that webform preview causes the serial number to go up more quickly than necessary, because both preview and submission call webform_submission_create() which calls _webform_submission_serial_next_value()

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

DanChadwick’s picture

This is bad.

I suspect the problem is in webform.module, function _webform_submission_serial_next_value.

Please change:

// ORIGINAL:
    db_transaction();

// CHANGE TO:

   $transaction = db_transaction();

The transaction is committed when the variable returned by db_transaction() goes out of scope. I suspect that if you don't save the returned transaction, it immediately goes out of scope.

If you try this and it solves the problem, please reply and I'll commit this.

DanChadwick’s picture

Priority: Normal » Major
Status: Active » Needs review

Since this affects every high-load webform installation, I'm bumping the priority.

mfb’s picture

ah yes, I noticed this too before seeing this :) I made the change an hour ago and have not seen any more PDOExceptions.

In another hour I'll have a more statistically significant result..

mfb’s picture

ok this seemed to do the trick :) so I would say commit it.

Just recently we did see multiple cases of a new exception:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: SELECT w.next_serial AS next_serial FROM {webform} w WHERE (nid = :db_condition_placeholder_0) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 1 ) in _webform_submission_serial_next_value() (line 4621 of /drupal/sites/all/modules/webform/webform.module).

The solution could be for me to increase the lock wait timeout. Or webform could catch lock wait timeouts, sleep and retry. Either way this could cause stalled requests to pile up, and an OOM situation...

mfb’s picture

I added a new issue for the serial incrementing on preview: #2304585: Serial number increments when previewing Webform submission

DanChadwick’s picture

@MFB - What is your lock wait timeout? The default is 50 seconds, which I would think would be PLENTY long enough for this simple transaction.

Liam Morland’s picture

FileSize
1.63 KB

Here is a patch that fixes all uses of db_transaction() in Webform.

Liam Morland’s picture

Here is an improved version of patch which only adds FOR UPDATE when incrementing. This would only make a difference if something previous in execution had started a DB transaction. So, it probably makes no difference, but it might help and it can't hurt.

DanChadwick’s picture

Minor suggestion, but it would be helpful to have a comment noting that the transactions is committed when the $txt variable goes out of scope or is unset. That would prevent some future maintainer from deleting what looks like an unused variable.

Also, I don't understand the duplicate id issue unless the lock timeout is set to much, much less than 50 seconds, or unless there is a PHP issue with the transaction not being committed when the function exits (i.e. maybe it doesn't commit until the transaction doesn't commit until garbage collection time. I'd love to hear back @mfb on that.

mfb’s picture

This is the PDO exception a site encounters when a webform has gone viral and the server can't handle the load. Let's say the server is receiving dozens of webform submissions per second; transactions will pile up, the lock wait timeout will be exceeded and an exception thrown. The fix at #2304585: Serial number increments when previewing Webform submission should help a bit because it cuts in half the number of transactions.

I'm not sure that webform needs to do anything here - just pointing out the failure scenario.

Liam Morland’s picture

Updated patch improves the comments as suggested.

The garbage collection suggestion sounds possible. I note the Drupal DatabaseTransaction class does not have a method to commit, only rollback().

DanChadwick’s picture

Status: Needs review » Fixed

I now doubt the garbage collection. If the transactions pile up, I can see the 50 second timeout.

Committed to 7.x-4.x and 8.x. Thank you very much, Liam.

Alas, again I had a typo on the 8.x commit message. It was committed as 1b008a9d29051c93cf11399fadf74d9cc846bc6c

Liam Morland’s picture

Thanks.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.