The "xf_content_activity_log" table is deadlock-prone

R

rdn

Well-known member
Affected version
2.3.4
I got 2 errors logged today:


PHP: 
Server error log
XF\Db\DeadlockException: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction src/XF/Db/AbstractStatement.php:230
Generated by: User2 Dec 12, 2024 at 10:48 PM
Stack trace
INSERT  INTO `xf_content_activity_log` (`log_date`, `content_type`, `content_id`, `content_date`, `content_container_id`, `reply_count`) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE reply_count = reply_count + VALUES(reply_count)
------------

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1213, '40001')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1213, '40001')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(219): XF\Db\AbstractAdapter->query('INSERT  INTO `x...', Array)
#4 src/XF/Repository/ActivityLogRepository.php(230): XF\Db\AbstractAdapter->insert('xf_content_acti...', Array, false, 'reply_count = r...')
#5 src/XF/ActivityLog/AbstractHandler.php(98): XF\Repository\ActivityLogRepository->handleLog(1734014885, 'thread', 1234, Array, true)
#6 src/XF/Repository/ActivityLogRepository.php(40): XF\ActivityLog\AbstractHandler->log(Object(XFES\XF\Entity\Thread), 1734014885, Array, true)
#7 src/XF/Entity/Thread.php(952): XF\Repository\ActivityLogRepository->log(Object(XFES\XF\Entity\Thread), 1734014885, Array)
#8 src/addons/M2N/TrophyEssentials/XF/Entity/Thread.php(61): XF\Entity\Thread->postAdded(Object(XFES\XF\Entity\Post))
#9 src/XF/Entity/Post.php(648): M2N\TrophyEssentials\XF\Entity\Thread->postAdded(Object(XFES\XF\Entity\Post))
#10 src/XF/Entity/Post.php(598): XF\Entity\Post->updateThreadRecord()
#11 src/addons/MMO/LiveThreads/XF/Entity/Post.php(11): XF\Entity\Post->_postSave()
#12 src/addons/DigitalPoint/Cloudflare/XF/Entity/Post.php(9): MMO\LiveThreads\XF\Entity\Post->_postSave()
#13 src/addons/XFES/XF/Entity/Post.php(9): DigitalPoint\Cloudflare\XF\Entity\Post->_postSave()
#14 src/XF/Mvc/Entity/Entity.php(1324): XFES\XF\Entity\Post->_postSave()
#15 src/XF/Service/Thread/ReplierService.php(224): XF\Mvc\Entity\Entity->save(true, false)
#16 src/XF/Service/ValidateAndSavableTrait.php(42): XF\Service\Thread\ReplierService->_save()
#17 src/XF/Pub/Controller/ThreadController.php(662): XF\Service\Thread\ReplierService->save()
#18 src/addons/ThemeHouse/AutoMergeDoublePost/XF/Pub/Controller/Thread.php(53): XF\Pub\Controller\ThreadController->actionAddReply(Object(XF\Mvc\ParameterBag))
#19 src/XF/Mvc/Dispatcher.php(362): ThemeHouse\AutoMergeDoublePost\XF\Pub\Controller\Thread->actionAddReply(Object(XF\Mvc\ParameterBag))
#20 src/XF/Mvc/Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('XF:Thread', 'AddReply', Object(XF\Mvc\RouteMatch), Object(SV\UserActivity\XF\Pub\Controller\Thread), NULL)
#21 src/XF/Mvc/Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\UserActivity\XF\Pub\Controller\Thread), NULL)
#22 src/XF/Mvc/Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#23 src/XF/App.php(2826): XF\Mvc\Dispatcher->run()
#24 src/XF.php(806): XF\App->run()
#25 index.php(23): XF::runApp('XF\\Pub\\App')
#26 {main}
Request state
array(4) {
  ["url"] => string(26) "/threads/1234/add-reply"
  ["referrer"] => string(35) "https://xf23.com/threads/1234/"
  ["_GET"] => array(1) {
    ["/threads/1234/add-reply"] => string(0) ""
  }
  ["_POST"] => array(11) {
    ["_xfToken"] => string(8) "********"
    ["message_html"] => string(26) "<p>post 101</p>"
    ["attachment_hash"] => string(32) "55a53ce51f90bd956786f6fa19a5fe56"
    ["attachment_hash_combined"] => string(89) "{"type":"post","context":{"thread_id":1234},"hash":"55a53ce51f90bd956786f6fa19a5fe56"}"
    ["last_date"] => string(10) "1734014867"
    ["last_known_date"] => string(10) "1734014867"
    ["parent_id"] => string(8) "28696855"
    ["load_extra"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
    ["_xfWithData"] => string(1) "1"
    ["_xfRequestUri"] => string(17) "/threads/1234/"
  }
}


Server error log
XF\Db\DeadlockException: MySQL query error [1213]: Deadlock found when trying to get lock; try restarting transaction src/XF/Db/AbstractStatement.php:230
Generated by: User1 Dec 12, 2024 at 12:05 AM
Stack trace
INSERT  INTO `xf_content_activity_log` (`log_date`, `content_type`, `content_id`, `content_date`, `content_container_id`, `reply_count`) VALUES (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE reply_count = reply_count + VALUES(reply_count)
------------

#0 src/XF/Db/Mysqli/Statement.php(207): XF\Db\AbstractStatement->getException('MySQL query err...', 1213, '40001')
#1 src/XF/Db/Mysqli/Statement.php(83): XF\Db\Mysqli\Statement->getException('MySQL query err...', 1213, '40001')
#2 src/XF/Db/AbstractAdapter.php(96): XF\Db\Mysqli\Statement->execute()
#3 src/XF/Db/AbstractAdapter.php(219): XF\Db\AbstractAdapter->query('INSERT  INTO `x...', Array)
#4 src/XF/Repository/ActivityLogRepository.php(230): XF\Db\AbstractAdapter->insert('xf_content_acti...', Array, false, 'reply_count = r...')
#5 src/XF/ActivityLog/AbstractHandler.php(98): XF\Repository\ActivityLogRepository->handleLog(1733933124, 'thread', 5678, Array, true)
#6 src/XF/Repository/ActivityLogRepository.php(40): XF\ActivityLog\AbstractHandler->log(Object(XFES\XF\Entity\Thread), 1733933124, Array, true)
#7 src/XF/Entity/Thread.php(952): XF\Repository\ActivityLogRepository->log(Object(XFES\XF\Entity\Thread), 1733933124, Array)
#8 src/addons/M2N/TrophyEssentials/XF/Entity/Thread.php(61): XF\Entity\Thread->postAdded(Object(XFES\XF\Entity\Post))
#9 src/XF/Entity/Post.php(648): M2N\TrophyEssentials\XF\Entity\Thread->postAdded(Object(XFES\XF\Entity\Post))
#10 src/XF/Entity/Post.php(598): XF\Entity\Post->updateThreadRecord()
#11 src/addons/MMO/LiveThreads/XF/Entity/Post.php(11): XF\Entity\Post->_postSave()
#12 src/addons/DigitalPoint/Cloudflare/XF/Entity/Post.php(9): MMO\LiveThreads\XF\Entity\Post->_postSave()
#13 src/addons/XFES/XF/Entity/Post.php(9): DigitalPoint\Cloudflare\XF\Entity\Post->_postSave()
#14 src/XF/Mvc/Entity/Entity.php(1324): XFES\XF\Entity\Post->_postSave()
#15 src/XF/Service/Thread/ReplierService.php(224): XF\Mvc\Entity\Entity->save(true, false)
#16 src/XF/Service/ValidateAndSavableTrait.php(42): XF\Service\Thread\ReplierService->_save()
#17 src/XF/Pub/Controller/ThreadController.php(662): XF\Service\Thread\ReplierService->save()
#18 src/addons/ThemeHouse/AutoMergeDoublePost/XF/Pub/Controller/Thread.php(53): XF\Pub\Controller\ThreadController->actionAddReply(Object(XF\Mvc\ParameterBag))
#19 src/XF/Mvc/Dispatcher.php(362): ThemeHouse\AutoMergeDoublePost\XF\Pub\Controller\Thread->actionAddReply(Object(XF\Mvc\ParameterBag))
#20 src/XF/Mvc/Dispatcher.php(264): XF\Mvc\Dispatcher->dispatchClass('XF:Thread', 'AddReply', Object(XF\Mvc\RouteMatch), Object(SV\UserActivity\XF\Pub\Controller\Thread), NULL)
#21 src/XF/Mvc/Dispatcher.php(121): XF\Mvc\Dispatcher->dispatchFromMatch(Object(XF\Mvc\RouteMatch), Object(SV\UserActivity\XF\Pub\Controller\Thread), NULL)
#22 src/XF/Mvc/Dispatcher.php(63): XF\Mvc\Dispatcher->dispatchLoop(Object(XF\Mvc\RouteMatch))
#23 src/XF/App.php(2826): XF\Mvc\Dispatcher->run()
#24 src/XF.php(806): XF\App->run()
#25 index.php(23): XF::runApp('XF\\Pub\\App')
#26 {main}
Request state
array(4) {
  ["url"] => string(26) "/threads/5678/add-reply"
  ["referrer"] => string(35) "https://xf23.com/threads/5678/"
  ["_GET"] => array(1) {
    ["/threads/5678/add-reply"] => string(0) ""
  }
  ["_POST"] => array(11) {
    ["_xfToken"] => string(8) "********"
    ["message_html"] => string(27) "<p>post 102</p>"
    ["attachment_hash"] => string(32) "8b13e71953ebf58455b2ace99c034d01"
    ["attachment_hash_combined"] => string(89) "{"type":"post","context":{"thread_id":5678},"hash":"8b13e71953ebf58455b2ace99c034d01"}"
    ["last_date"] => string(10) "1733932230"
    ["last_known_date"] => string(10) "1733932230"
    ["parent_id"] => string(8) "28685065"
    ["load_extra"] => string(1) "1"
    ["_xfResponseType"] => string(4) "json"
    ["_xfWithData"] => string(1) "1"
    ["_xfRequestUri"] => string(17) "/threads/5678/"
  }
}
 
It is the "insert on duplicate update" logic. This has been updated to a different style for a few other high-frequency updated tables to reduce deadlocks.
 

Similar threads

DJA
  • Question Question
XF 2.2 The table 'xf_session_activity' is full
Replies
2
Views
1K
DJA
DJA
Recep Baltaş
  • Question Question
XF 2.0 The table 'xf_session_activity' is full
Replies
4
Views
2K
hibiskus
hibiskus
Recep Baltaş
Not a bug MySQL query error [1114]: The table 'xf_session_activity' is full
Replies
5
Views
2K
Ozzy47
Ozzy47
Kelso
  • Question Question
XF 2.0 The table 'xf_session_activity' is full (after upgrade to xenforo 2 from 1)
Replies
7
Views
2K
dieg0
dieg0
X
Design issue Soft-deleting posts in active threads is deadlock prone
Replies
4
Views
1K
Mike
Mike
Back
Top Bottom