Resource icon

Deadlock Avoidance by Xon 1.0.21

No permission to download
How would we see if deadlocks are happening to us like this?

Our forum falls into the "busy" category so it sounds like we might benefit from this but we also have a pretty powerful server so not sure...
Nearly all the changes have been done to supress rare deadlocks/race conditions which generate false positives in the error log.

Generally, if you are getting deadlock reported in the error log this add-on seeks to prevent them. My two sites run very leanly and honestly have very low speced databases and a configuration which tends to trip the deadlock errors fairly often.
 
Thanks for releasing this. Hopefully it will stop our frequent deadlock errors.
Not a problem, I developed this because I was sick & tired of frequent deadlocks which didn't need to be happening.

Please feel free to provide feedback if my workarounds cause performance issues* or if there are deadlocks I'm not providing workarounds for :)


* It uses FOR UPDATE in a couple places to ensure correct lock ordering to prevent deadlocks. This may not play well with a galera cluster.
 
Hi Xon! I'm not sure if it's something to do with my database, but ever since I upgraded to 1.0.10, I get about four of these "like" deadlocks per day:

Code:
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: kjones, Today at 8:10 AM
Stack Trace
#0 /home/talkbass/html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/talkbass/html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/talkbass/html/library/XenForo/Model/Like.php(226): Zend_Db_Adapter_Abstract->query('\r\n\t\t\tINSERT IGN...', Array)
#3 /home/talkbass/html/library/SV/DeadlockAvoidance/XenForo/Model/Like.php(27): XenForo_Model_Like->likeContent('post', 19185210, 106575, NULL, NULL)
#4 /home/talkbass/html/library/XenForo/ControllerPublic/Post.php(442): SV_DeadlockAvoidance_XenForo_Model_Like->likeContent('post', 19185210, 106575)
#5 /home/talkbass/html/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_Post->actionLike()
#6 /home/talkbass/html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /home/talkbass/html/index.php(13): XenForo_FrontController->run()
#8 {main}
Request State
array(3) {
  ["url"] => string(44) "https://www.talkbass.com/posts/19185210/like"
  ["_GET"] => array(1) {
    ["/posts/19185210/like"] => string(0) ""
  }
  ["_POST"] => array(4) {
    ["_xfRequestUri"] => string(68) "/threads/the-praise-and-worship-band-bassists-club.1068274/page-1510"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfToken"] => string(8) "********"
    ["_xfResponseType"] => string(4) "json"
  }
}

Again, not sure if it's something on my end and just a coincidence. :)
 
Hi Xon! I'm not sure if it's something to do with my database, but ever since I upgraded to 1.0.10, I get about four of these "like" deadlocks per day:

Code:
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: kjones, Today at 8:10 AM
Stack Trace
#0 /home/talkbass/html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/talkbass/html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/talkbass/html/library/XenForo/Model/Like.php(226): Zend_Db_Adapter_Abstract->query('\r\n\t\t\tINSERT IGN...', Array)
#3 /home/talkbass/html/library/SV/DeadlockAvoidance/XenForo/Model/Like.php(27): XenForo_Model_Like->likeContent('post', 19185210, 106575, NULL, NULL)
#4 /home/talkbass/html/library/XenForo/ControllerPublic/Post.php(442): SV_DeadlockAvoidance_XenForo_Model_Like->likeContent('post', 19185210, 106575)
#5 /home/talkbass/html/library/XenForo/FrontController.php(351): XenForo_ControllerPublic_Post->actionLike()
#6 /home/talkbass/html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#7 /home/talkbass/html/index.php(13): XenForo_FrontController->run()
#8 {main}
Request State
array(3) {
  ["url"] => string(44) "https://www.talkbass.com/posts/19185210/like"
  ["_GET"] => array(1) {
    ["/posts/19185210/like"] => string(0) ""
  }
  ["_POST"] => array(4) {
    ["_xfRequestUri"] => string(68) "/threads/the-praise-and-worship-band-bassists-club.1068274/page-1510"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfToken"] => string(8) "********"
    ["_xfResponseType"] => string(4) "json"
  }
}

Again, not sure if it's something on my end and just a coincidence. :)
This would be because there is 1.0.11 which fixes that, that I forgot to push up here :P
 
This does really avoid Dead lock for me but it causes double post/threads most of the time.
 
This does really avoid Dead lock for me but it causes double post/threads most of the time.
Do you have a different default transaction isolation level set by default in your my.cnf file?

It shouldn't cause double-posting, as all it does it move some slow stuff out of the transaction. Your users would still have to be double click submit or similar.

I definitely recommending flood control applies to all users, even if it is only of 1 second!
 
I'm not so sure which one cause it really.
Between this 2 addon: https://xenforo.com/community/resources/redis-flood-check.5463/

Do you have a different default transaction isolation level set by default in your my.cnf file?
I'm not so sure what config is that.

Your users would still have to be double click submit or similar.
During high peak times, posting new thread doesn't show thread was posted successfully even if it's already posted, so the user click submit button multiple time that's why causing duplicates.

I definitely recommending flood control applies to all users, even if it is only of 1 second!
Yes all of the users has 30 seconds minimum.
 
I'm not so sure which one cause it really.
Between this 2 addon: https://xenforo.com/community/resources/redis-flood-check.5463/
This shouldn't make a difference.

I'm not so sure what config is that.
Do you have "tx_isolation" set anywhere?

You can run the following to see what it currently is:
Code:
select @@session.tx_isolation


During high peak times, posting new thread doesn't show thread was posted successfully even if it's already posted, so the user click submit button multiple time that's why causing duplicates.


Yes all of the users has 30 seconds minimum.
Can you please install/update Slow query logger, set it to ~10 seconds and see what is reported during peak time?

Posting a thread should be fast. This add-on is just stopping deadlocks, so if something is causing a significant delay the posting could take longer than expected. Where before it would deadlock and fail.
 
  • Like
Reactions: Xon
Xon updated Deadlock Avoidance by Xon with a new update entry:

Maintenance update

  • Preserve original ordering of closures when pushing functions out of a transaction
  • Hoist updating forum counters out of the new post/add reply transaction. Relaxes updates;
    1. removing a thread will not re-populate the last updated post
    2. moving a thread out of an empty forum has a race condition between the thread saving and the forum counters updating
    3. Forum counters are atomically incremented outside of a large transaction, and atomically conditionally updating the last post via SQL instead of read-mutate-update...

Read the rest of this update entry...
 
Last edited:
Top Bottom