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.
 
Is there any way to avoid this?
Like turning off some new XF 2.3 option.

Is the option that's directly related to this is the "Activity log length" /admin.php?options/groups/logging/ ?
All I need to do is set it to 0?
 

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