The "xf_content_activity_log" table is deadlock-prone

[rdn]

Affected version

2.3.4

I got 2 errors logged today:

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/"
  }
}

 

[MentaL]

Could it not be a case of a lack of optimization on part of mysql? Try Releem.

 

[Xon]

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.

 

[rdn]

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?

 

[TheLaw]

I just hit this issue today - xf_content_activity_log appears to have created over 1 million rows. Quite a number appear empty as well. I'm not sure if this is related to the problem I'm currently encountering as well and am looking into how to safely address and prune.

 

[pegasus]

I think this occurs when you have multiple users replying to the same thread within the same second. The new-post transactions overlap slightly and manage to deadlock on this single-row insert (which, taken alone, is basically as optimized against deadlocks as it can be). The only solutions I imagine to this are: throttling replies per thread (implementing a lock and telling the user to "please try again in a few seconds"), or deferring the activity log inserts so they happen outside the post's transaction.