XF 1.4 Regular deadlock server errors

[Stuart Wright]

Hello,
I'm getting a batch of these types of errors several times a day.
Had 30 of them an hour ago:

Zend_Db_Statement_Mysqli_Exception: Mysqli statement execute error : Deadlock found when trying to get lock; try restarting transaction - library/Zend/Db/Statement/Mysqli.php:214
Generated By: Unknown Account, 58 minutes ago
Stack Trace
#0 /home/sites/avforums/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/sites/avforums/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/sites/avforums/public_html/library/XenDebug/Db/Mysqli.php(13): Zend_Db_Adapter_Abstract->query('DELETE FROM `xf...', Array)
#3 /home/sites/avforums/public_html/library/Zend/Db/Adapter/Abstract.php(661): XenDebug_Db_Mysqli->query('DELETE FROM `xf...')
#4 /home/sites/avforums/public_html/library/XenForo/Model/Deferred.php(168): Zend_Db_Adapter_Abstract->delete('xf_deferred', 'deferred_id = 2...')
#5 /home/sites/avforums/public_html/library/XenForo/Model/Deferred.php(232): XenForo_Model_Deferred->deleteDeferredById(2183847)
#6 /home/sites/avforums/public_html/library/XenForo/Model/Deferred.php(390): XenForo_Model_Deferred->runDeferred(Array, 7.9999969005585, '', false)
#7 /home/sites/avforums/public_html/library/XenForo/Model/Deferred.php(335): XenForo_Model_Deferred->_runInternal(Array, NULL, '', false)
#8 /home/sites/avforums/public_html/deferred.php(23): XenForo_Model_Deferred->run(false)
#9 {main}
Request State
array(3) {
["url"] => string(37) "https://www.avforums.com/deferred.php"
["_GET"] => array(0) {
}
["_POST"] => array(3) {
["_xfRequestUri"] => string(50) "/threads/listening-to-iphone-music-on-ps3.1017659/"
["_xfNoRedirect"] => string(1) "1"
["_xfResponseType"] => string(4) "json"
}
}

Could I have some advice on how to track down the culprit please?

 

[Paul B]

I see reference to XenDebug in the trace.
Is that an add-on?

It's possibly not the reason but it would be useful to know what it is.

 

[HWS]

If you use a Master/Master replication in MySQL (such as Galera) and a load balancer (such as HAProxy) for database access you see such errors frequently. The reason is that Galera is not 100% transaction safe.

The solution would be to install digitalpoints genious master/slave add-on and direct all write queries to just one of the MySQL servers. The read queries can still be distributed over all MySQL servers.

 

[Xon]

If you use a Master/Master replication in MySQL (such as Galera) and a load balancer (such as HAProxy) for database access you see such errors frequently. The reason is that Galera is not 100% transaction safe.

Serializable transactions aren't supported(http://galeracluster.com/documentation-webpages/isolationlevels.html) but MySQL's default transaction isolation level is repeatable read. XenForo doesn't appear to change this.

The solution would be to install digitalpoints genious master/slave add-on and direct all write queries to just one of the MySQL servers. The read queries can still be distributed over all MySQL servers.

MariaDB is working on a new MySQL proxy (MaxScale) which will do this sort of thing but with configurable policy.

 

[HWS]

Serializable transactions aren't supported(http://galeracluster.com/documentation-webpages/isolationlevels.html) but MySQL's default transaction isolation level is repeatable read. XenForo doesn't appear to change this.

Stuarts problem has nothing to do with that.

This is the problem with Galera;

Due to cluster level optimistic concurrency control, transaction issuing COMMIT may still be aborted at that stage. There can be two transactions writing to same rows and committing in separate cluster nodes, and only one of the them can successfully commit. The failing one will be aborted. For cluster level aborts, MySQL/galera cluster gives back deadlock error.
code (Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).

If this happens, XenForo shows the error message from the first post.

See also:
http://www.severalnines.com/blog/av...proxy-single-node-writes-and-multi-node-reads

 

[Mike]

I should note that I asked for feedback about this (it's likely the same error actually) here: https://xenforo.com/community/threa...ry-time-i-rebuild-usergroup-promotions.85898/

 

[HWS]

I should note that I asked for feedback about this (it's likely the same error actually) here: https://xenforo.com/community/threa...ry-time-i-rebuild-usergroup-promotions.85898/

Looks like the same error. Haven't seen that thread.

If Stuart uses Galera (which I assume given his forum size, but do not know), this can be tested easily with disabling the load balancer temporarily.

We've a Galera setup and saw such errors even at our smaller forum several times a day. Since we changed the setup to just use one node for writing at a time, the errors are gone.

 

[Stuart Wright]

Sorry chaps. Could the two threads be merged, perhaps?