Design issue Deadlock found when trying to get lock; try restarting transaction

[Jean-Baptiste]

Hello,

I often have this error in my server error log :

Deadlock found when trying to get lock; try restarting transaction

I read this thead : http://stackoverflow.com/questions/...ck-found-when-trying-to-get-lock-try-restarti and one solution would be to :

The only way to truly handle deadlocks is to write your code to expect them. This generally isn't very difficult if your database code is well written. Often you can just put a try/catch around the query execution logic and look for a deadlock when errors occur. If you catch one, the normal thing to do is just attempt to execute the failed query again.

When this error occur, there isn't any reason, the only thing I know is the more than my board grows, the more often this error occur.

I have a big board with 1K visitors online during the last 15 minutes.

Best regards.

 

[Chris D]

The bug report unfortunately doesn't contain enough information to help debug the issue.

If that error is occurring and is being logged in the Server Error Log in the Admin CP, you will need to include the full stack trace so Mike or others can trace what code is executing to cause it.

 

[Mike]

As they say, dead locks happen. Really, in theory re-submitting wouldn't sort it because it'd be in the same state. You actually need to roll back the entire transaction and re-do it, which is by no means trivial with arbitrary transactions intermixed with logic.

As such, the only recommendation is to attempt a resubmit.

 

[Jean-Baptiste]

The bug report unfortunately doesn't contain enough information to help debug the issue.

If that error is occurring and is being logged in the Server Error Log in the Admin CP, you will need to include the full stack trace so Mike or others can trace what code is executing to cause it.

It can happen anytime, on any action.

I allready tried to debug it, but no way, and it doesn't look like that an addon is causing this.

Here are two stack trace I have, I have like 30/50 per days (that's negligeable due to the amount of visit I have, and since F5 solves it, but that's still a problem).

Maybe @digitalpoint have a similar problem ? Since you have a big board.

Here is one :

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

#0 /home/username/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/username/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/username/public_html/library/Zend/Db/Adapter/Abstract.php(753): Zend_Db_Adapter_Abstract->query('??????SELECT *?...', 527484)
#3 /home/username/public_html/library/XenForo/DataWriter/ConversationMaster.php(189): Zend_Db_Adapter_Abstract->fetchRow('??????SELECT *?...', 527484)
#4 /home/username/public_html/library/XenForo/DataWriter/ConversationMessage.php(150): XenForo_DataWriter_ConversationMaster->addReply(Array)
#5 /home/username/public_html/library/XenForo/DataWriter.php(1397): XenForo_DataWriter_ConversationMessage->_postSave()
#6 /home/username/public_html/library/XenForo/ControllerPublic/Conversation.php(1088): XenForo_DataWriter->save()
#7 /home/username/public_html/library/XenForo/FrontController.php(337): XenForo_ControllerPublic_Conversation->actionInsertReply()
#8 /home/username/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#9 /home/username/public_html/index.php(13): XenForo_FrontController->run()
#10 {main}

array(3) {
  ["url"] => string(67) "http://my-website.url.com/conversations/lobby-mw2.527484/insert-reply"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(8) {
    ["message_html"] => string(16) "<p>Message content :/</p>"
    ["_xfRelativeResolver"] => string(61) "http://my-website.url.com/conversations/lobby-mw2.527484/page-2"
    ["last_date"] => string(10) "1384032385"
    ["last_known_date"] => string(0) ""
    ["_xfToken"] => string(8) "********"
    ["_xfRequestUri"] => string(38) "/conversations/conversation.527484/page-2"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}

Here is an other stack trace :

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

#0 /home/username/public_html/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /home/username/public_html/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /home/username/public_html/library/XenForo/Model/Deferred.php(113): Zend_Db_Adapter_Abstract->query('?????INSERT INT...', Array)
#3 /home/username/public_html/library/XenForo/Application.php(1288): XenForo_Model_Deferred->defer('MailQueue', Array, 'MailQueue', false, NULL)
#4 /home/username/public_html/library/XenForo/Model/MailQueue.php(7): XenForo_Application::defer('MailQueue', Array, 'MailQueue')
#5 /home/username/public_html/library/XenForo/Mail.php(224): XenForo_Model_MailQueue->insertMailQueue(Object(Zend_Mail))
#6 /home/username/public_html/library/XenForo/Model/ThreadWatch.php(249): XenForo_Mail->queue('amegliololo13@g...', 'L0L0')
#7 /home/username/public_html/library/XenForo/DataWriter/DiscussionMessage/Post.php(144): XenForo_Model_ThreadWatch->sendNotificationToWatchUsersOnReply(Array, NULL, Array)
#8 /home/username/public_html/library/XenForo/DataWriter.php(1411): XenForo_DataWriter_DiscussionMessage_Post->_postSaveAfterTransaction()
#9 /home/username/public_html/library/Dark/TaigaChat/DataWriter/DiscussionMessage/Post.php(7): XenForo_DataWriter->save()
#10 /home/username/public_html/library/XenForo/ControllerPublic/Thread.php(548): Dark_TaigaChat_DataWriter_DiscussionMessage_Post->save()
#11 /home/username/public_html/library/XenForo/FrontController.php(337): XenForo_ControllerPublic_Thread->actionAddReply()
#12 /home/username/public_html/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#13 /home/username/public_html/index.php(13): XenForo_FrontController->run()
#14 {main}

array(3) {
  ["url"] => string(105) "http://mysite.com/threads/thread-title.255/add-reply"
  ["_GET"] => array(0) {
  }
  ["_POST"] => array(9) {
    ["message_html"] => string(45) "<p>Message ?</p>"
    ["_xfRelativeResolver"] => string(102) "http://mysite.com/threads/thread-title.255/page-2"
    ["attachment_hash"] => string(32) "e062dda2987dddfb08107703ae039bd8"
    ["last_date"] => string(10) "1384033713"
    ["last_known_date"] => string(10) "1384033713"
    ["_xfToken"] => string(8) "********"
    ["_xfRequestUri"] => string(79) "/threads/thread-title.255/page-2"
    ["_xfNoRedirect"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
  }
}

Each stack is unique.

 

[Jeremy]

P: I have seen a "Deadlock found when trying to get a lock" MySQL error in my error logs.
S: Reload the page to restart whatever process you were attempting. Deadlocks can occur with any transaction or query in an InnoDB MySQL database. They are very difficult to programmatically prevent and shouldn't occur often. If they occur fairly often, it is recommended that you discuss possible solutions with your host. Here is some further reading related to deadlocks:
http://xenforo.com/community/threads/mysql-deadlock.60778/
http://xenforo.com/community/threads/deadlock.60097/
http://xenforo.com/community/thread...to-get-lock-try-restarting-transaction.47820/
http://xenforo.com/community/threads/vb-3-8-xf-via-cli-importer-discrepancies.27794/#post-327157

http://xenforo.com/community/threads/troubleshooting-faq-frequent-problems-solutions.62138/

 

[digitalpoint]

Maybe @digitalpoint have a similar problem ? Since you have a big board.

Nope... I have yet to run into a deadlock issue on my setup. I'm using ndbcluster storage engine for all tables, not InnoDB or MyISAM, so that probably has something to do with it (we do well over 1,000 SQL writes per second).

 

[ehsanix]

Hello,

I often have this error in my server error log :

Deadlock found when trying to get lock; try restarting transaction

I read this thead : http://stackoverflow.com/questions/...ck-found-when-trying-to-get-lock-try-restarti and one solution would be to :


When this error occur, there isn't any reason, the only thing I know is the more than my board grows, the more often this error occur.

I have a big board with 1K visitors online during the last 15 minutes.

Best regards.

What did you do with those deadlock?

 

[Marcel]

I've not come across these yet on our forum.
Until today.
I'm going through and mass deleting spammers that have been spambanned in the past.
Had to do it in such a way that I got all banned users with 'spam' in the reason into a specific usergroup.
Then batch updated those users (A good few hundred).

While this is running, I've had a few deadlocks pop up on the server error log (one I caused as a user too, posting in a different tab). I assume it's just because of the load the database is under while deleting the users.

 

[ehsanix]

I had a few deadlocks per week 6 months ago. Nowadays I have more than thousands deadlocks per day. It may come from a plugin update, but is this normal? (I don't think so)
Most of them are on xf_deferred.

 

[Mike]

Thousands per day is not normal. I should note that there are tweaks in 1.4 (and the latest version of 1.3, IIRC) that are designed to reduce some of the common deadlock causes.

 

[denam]

A deadlock also happened to our fresh migrated board on xenforo version 1.4.6 this week.

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

#0 /var/www/clients/client0/web3/web/library/Zend/Db/Statement.php(297): Zend_Db_Statement_Mysqli->_execute(Array)
#1 /var/www/clients/client0/web3/web/library/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array)
#2 /var/www/clients/client0/web3/web/library/Zend/Db/Adapter/Abstract.php(574): Zend_Db_Adapter_Abstract->query('INSERT INTO `xf...', Array)
#3 /var/www/clients/client0/web3/web/library/XenForo/DataWriter.php(1624): Zend_Db_Adapter_Abstract->insert('xf_user_alert', Array)
#4 /var/www/clients/client0/web3/web/library/XenForo/DataWriter.php(1613): XenForo_DataWriter->_insert()
#5 /var/www/clients/client0/web3/web/library/XenForo/DataWriter.php(1405): XenForo_DataWriter->_save()
#6 /var/www/clients/client0/web3/web/library/XenForo/Model/Alert.php(452): XenForo_DataWriter->save()
#7 /var/www/clients/client0/web3/web/library/XenForo/Model/Alert.php(426): XenForo_Model_Alert->alertUser(9068, 4082, 'Giftgr\xC3\xBCn', 'post', 1683000, 'like', NULL)
#8 /var/www/clients/client0/web3/web/library/XenForo/Model/Like.php(245): XenForo_Model_Alert::alert(9068, 4082, 'Giftgr\xC3\xBCn', 'post', 1683000, 'like')
#9 /var/www/clients/client0/web3/web/library/Audentio/Credit/Core/Model/Like.php(8): XenForo_Model_Like->likeContent('post', 1683000, 9068, NULL, NULL)
#10 /var/www/clients/client0/web3/web/library/XenForo/ControllerPublic/Post.php(420): Audentio_Credit_Core_Model_Like->likeContent('post', 1683000, 9068)
#11 /var/www/clients/client0/web3/web/library/XenForo/FrontController.php(347): XenForo_ControllerPublic_Post->actionLike()
#12 /var/www/clients/client0/web3/web/library/XenForo/FrontController.php(134): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#13 /var/www/clients/client0/web3/web/index.php(13): XenForo_FrontController->run()
#14 {main}

array(3) {
["url"] => string(39) "http://www.domain.com/posts/1683000/like"
["_GET"] => array(0) {
}
["_POST"] => array(4) {
["_xfRequestUri"] => string(44) "/threads/56488/page-4"
["_xfNoRedirect"] => string(1) "1"
["_xfToken"] => string(8) "********"
["_xfResponseType"] => string(4) "json"
}
}