Page MenuHomePhabricator
Create Task
Maniphest T260838

Error 1213: Deadlock found when trying to get lock; try restarting transaction Function: MediaWiki\Block\DatabaseBlockStore::acquireTarget
Open, HighPublic5 Estimated Story PointsPRODUCTION ERROR
Actions

Description

When a retroactive autoblocks are applied to users that are blocked nearly at the same time, the retroactive autoblock code attempts to retroactively autoblock the same IP. This fails due to database deadlocks.

Error

1.43 onwards:

normalised_message
[{reqId}] {exception_url}   RuntimeException: Duplicate block_target rows detected: 1983002,1983009,1983016
normalised_message
[{reqId}] {exception_url}   Wikimedia\Rdbms\DBQueryError: Error 1213: Deadlock found when trying to get lock; try restarting transaction
Function: MediaWiki\Block\DatabaseBlockStore::acquireTarget
Query: INSERT INTO `block_target` (bt_address,bt_user,bt_u
stack trace
from /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1172)
#0 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1045): MediaWiki\Block\DatabaseBlockStore->acquireTarget(MediaWiki\Block\DatabaseBlock, Wikimedia\Rdbms\DBConnRef, integer)
#1 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1000): MediaWiki\Block\DatabaseBlockStore->attemptInsert(MediaWiki\Block\DatabaseBlock, Wikimedia\Rdbms\DBConnRef, integer)
#2 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1627): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#3 /srv/mediawiki/php-1.43.0-wmf.14/extensions/CheckUser/src/HookHandler/PerformRetroactiveAutoblockHandler.php(98): MediaWiki\Block\DatabaseBlockStore->doAutoblock(MediaWiki\Block\DatabaseBlock, string)
#4 /srv/mediawiki/php-1.43.0-wmf.14/includes/HookContainer/HookContainer.php(159): MediaWiki\CheckUser\HookHandler\PerformRetroactiveAutoblockHandler->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#5 /srv/mediawiki/php-1.43.0-wmf.14/includes/HookContainer/HookRunner.php(3075): MediaWiki\HookContainer\HookContainer->run(string, array)
#6 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1486): MediaWiki\HookContainer\HookRunner->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#7 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1012): MediaWiki\Block\DatabaseBlockStore->doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock)
#8 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/BlockUser.php(596): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#9 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/BlockUser.php(533): MediaWiki\Block\BlockUser->placeBlockInternal(boolean)
#10 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/BlockUser.php(459): MediaWiki\Block\BlockUser->placeBlockUnsafe(boolean)
#11 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiBlock.php(178): MediaWiki\Block\BlockUser->placeBlock(boolean)
#12 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiMain.php(1953): ApiBlock->execute()
#13 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiMain.php(929): ApiMain->executeAction()
#14 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiMain.php(900): ApiMain->executeActionWithErrorHandling()
#15 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiEntryPoint.php(158): ApiMain->execute()
#16 /srv/mediawiki/php-1.43.0-wmf.14/includes/MediaWikiEntryPoint.php(200): MediaWiki\Api\ApiEntryPoint->execute()
#17 /srv/mediawiki/php-1.43.0-wmf.14/api.php(44): MediaWiki\MediaWikiEntryPoint->run()
#18 /srv/mediawiki/w/api.php(3): require(string)
#19 {main}
stack trace
from /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/Database.php(1194)
#0 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/Database.php(1178): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/Database.php(1152): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/Database.php(643): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/Database.php(1472): Wikimedia\Rdbms\Database->query(Wikimedia\Rdbms\Query, string)
#4 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/DBConnRef.php(127): Wikimedia\Rdbms\Database->insert(string, array, string, array)
#5 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/database/DBConnRef.php(407): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#6 /srv/mediawiki/php-1.43.0-wmf.14/includes/libs/rdbms/querybuilder/InsertQueryBuilder.php(343): Wikimedia\Rdbms\DBConnRef->insert(string, array, string, array)
#7 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1201): Wikimedia\Rdbms\InsertQueryBuilder->execute()
#8 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1045): MediaWiki\Block\DatabaseBlockStore->acquireTarget(MediaWiki\Block\DatabaseBlock, Wikimedia\Rdbms\DBConnRef, integer)
#9 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1000): MediaWiki\Block\DatabaseBlockStore->attemptInsert(MediaWiki\Block\DatabaseBlock, Wikimedia\Rdbms\DBConnRef, integer)
#10 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1627): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#11 /srv/mediawiki/php-1.43.0-wmf.14/extensions/CheckUser/src/HookHandler/PerformRetroactiveAutoblockHandler.php(98): MediaWiki\Block\DatabaseBlockStore->doAutoblock(MediaWiki\Block\DatabaseBlock, string)
#12 /srv/mediawiki/php-1.43.0-wmf.14/includes/HookContainer/HookContainer.php(159): MediaWiki\CheckUser\HookHandler\PerformRetroactiveAutoblockHandler->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#13 /srv/mediawiki/php-1.43.0-wmf.14/includes/HookContainer/HookRunner.php(3075): MediaWiki\HookContainer\HookContainer->run(string, array)
#14 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1486): MediaWiki\HookContainer\HookRunner->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#15 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/DatabaseBlockStore.php(1012): MediaWiki\Block\DatabaseBlockStore->doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock)
#16 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/BlockUser.php(596): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#17 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/BlockUser.php(533): MediaWiki\Block\BlockUser->placeBlockInternal(boolean)
#18 /srv/mediawiki/php-1.43.0-wmf.14/includes/block/BlockUser.php(459): MediaWiki\Block\BlockUser->placeBlockUnsafe(boolean)
#19 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiBlock.php(178): MediaWiki\Block\BlockUser->placeBlock(boolean)
#20 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiMain.php(1953): ApiBlock->execute()
#21 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiMain.php(929): ApiMain->executeAction()
#22 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiMain.php(900): ApiMain->executeActionWithErrorHandling()
#23 /srv/mediawiki/php-1.43.0-wmf.14/includes/api/ApiEntryPoint.php(158): ApiMain->execute()
#24 /srv/mediawiki/php-1.43.0-wmf.14/includes/MediaWikiEntryPoint.php(200): MediaWiki\Api\ApiEntryPoint->execute()
#25 /srv/mediawiki/php-1.43.0-wmf.14/api.php(44): MediaWiki\MediaWikiEntryPoint->run()
#26 /srv/mediawiki/w/api.php(3): require(string)
#27 {main}

Pre MW 1.43:

message
Error 1213: Deadlock found when trying to get lock; try restarting transaction (10.64.16.101)
Function: MediaWiki\Block\DatabaseBlockStore::insertBlock
Query: INSERT IGNORE INTO `ipblocks` (ipb_address,ipb_user,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_range_start,ipb_range_end,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,ipb_reason_id,ipb_by_actor) VALUES (<redacted>)

Details

Request ID
833540f7-8c5d-4e2e-8c44-f9e6490eb227
Request URL
https://en.wikipedia.org/w/api.php
Stack Trace
exception.trace
#0 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/Database.php(1683): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/Database.php(1658): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/Database.php(1227): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/Database.php(2361): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/Database.php(2321): Wikimedia\Rdbms\Database->doInsertNonConflicting(string, array, string)
#5 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->insert(string, array, string, array)
#6 /srv/mediawiki/php-1.36.0-wmf.4/includes/libs/rdbms/database/DBConnRef.php(369): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#7 /srv/mediawiki/php-1.36.0-wmf.4/includes/block/DatabaseBlockStore.php(164): Wikimedia\Rdbms\DBConnRef->insert(string, array, string, array)
#8 /srv/mediawiki/php-1.36.0-wmf.4/includes/block/DatabaseBlock.php(504): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock, NULL)
#9 /srv/mediawiki/php-1.36.0-wmf.4/includes/block/DatabaseBlock.php(640): MediaWiki\Block\DatabaseBlock->insert()
#10 /srv/mediawiki/php-1.36.0-wmf.4/extensions/CheckUser/includes/CheckUserHooks.php(667): MediaWiki\Block\DatabaseBlock->doAutoblock(string)
#11 /srv/mediawiki/php-1.36.0-wmf.4/includes/HookContainer/HookContainer.php(320): CheckUserHooks::doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#12 /srv/mediawiki/php-1.36.0-wmf.4/includes/HookContainer/HookContainer.php(131): MediaWiki\HookContainer\HookContainer->callLegacyHook(string, array, array, array)
#13 /srv/mediawiki/php-1.36.0-wmf.4/includes/HookContainer/HookRunner.php(3063): MediaWiki\HookContainer\HookContainer->run(string, array)
#14 /srv/mediawiki/php-1.36.0-wmf.4/includes/block/DatabaseBlockStore.php(431): MediaWiki\HookContainer\HookRunner->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#15 /srv/mediawiki/php-1.36.0-wmf.4/includes/block/DatabaseBlockStore.php(283): MediaWiki\Block\DatabaseBlockStore->doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock)
#16 /srv/mediawiki/php-1.36.0-wmf.4/includes/block/DatabaseBlock.php(517): MediaWiki\Block\DatabaseBlockStore->updateBlock(MediaWiki\Block\DatabaseBlock)
#17 /srv/mediawiki/php-1.36.0-wmf.4/includes/specials/SpecialBlock.php(1002): MediaWiki\Block\DatabaseBlock->update()
#18 /srv/mediawiki/php-1.36.0-wmf.4/includes/api/ApiBlock.php(133): SpecialBlock::processForm(array, DerivativeContext)
#19 /srv/mediawiki/php-1.36.0-wmf.4/includes/api/ApiMain.php(1593): ApiBlock->execute()
#20 /srv/mediawiki/php-1.36.0-wmf.4/includes/api/ApiMain.php(529): ApiMain->executeAction()
#21 /srv/mediawiki/php-1.36.0-wmf.4/includes/api/ApiMain.php(500): ApiMain->executeActionWithErrorHandling()
#22 /srv/mediawiki/php-1.36.0-wmf.4/api.php(90): ApiMain->execute()
#23 /srv/mediawiki/php-1.36.0-wmf.4/api.php(45): wfApiMain()
#24 /srv/mediawiki/w/api.php(3): require(string)
#25 {main}
Related Changes in Gerrit:
SubjectRepoBranchLines +/-
Follow-up: Use a lock in DatabaseBlockStore::doAutoblockmediawiki/coremaster+1 -2
Use a lock in DatabaseBlockStore::doAutoblockmediawiki/coremaster+146 -0
Customize query in gerrit

Related Objects

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 19 2020, 6:18 PM
thcipriani moved this task from Untriaged to Aug 2020 on the Wikimedia-production-error board.Aug 19 2020, 6:48 PM
Tchanders subscribed.Aug 28 2020, 9:06 PM
Krinkle triaged this task as High priority.Mar 10 2021, 7:54 PM
Krinkle added a project: CheckUser.
Krinkle subscribed.

Raising priority as this seems to be a source code issue rather than one of those ambiguous/intermittent issues that may be load or network related. A deadlock usually indicates a logical issue in the code. It might stil be dependant on a race happening with another request in order to trigger the issue, but the issue is likely with the query we send.

I notice in Logstash that these all involve CheckUser so tagging that as well. Not sure that's CheckUser exposing a core issue, or whether that's CheckUser using it in a way that is itself the issue.

Tchanders moved this task from Untriaged to Production-error on the Anti-Harassment-Team board.Mar 11 2021, 6:26 PM
Niharika moved this task from Production-error to Triage/To be Estimated on the Anti-Harassment-Team board.Mar 24 2021, 12:14 PM
Niharika set the point value for this task to 5.Mar 31 2021, 4:21 PM
Niharika moved this task from Triage/To be Estimated to Cards ready for development on the Anti-Harassment-Team board.
Tchanders moved this task from Cards ready for development to The Letter Song on the Anti-Harassment-Team board.Apr 1 2021, 11:19 AM
Tchanders edited projects, added Anti-Harassment-Team (The Letter Song); removed Anti-Harassment-Team.
• TThoabala claimed this task.Apr 6 2021, 3:56 PM
• TThoabala moved this task from Ready 🎬 (ONLY IF YOU HAVE NO MORE CODE TO REVIEW) to In Progress 💪 on the Anti-Harassment-Team (The Letter Song) board.
• TThoabala added a comment.Apr 9 2021, 5:50 AM

Followed the query to DatabaseBlockStore::insertBlockQuery (as stated on the ticket)
First insert to ipblocks happens on line 167,
then we have a second insert on line 195, this one happens just after a delete from the same table
There is also a comment on line 178 about doing all this to avoid locking, maybe we can change the first insert and write it in a way that we are sure it has completed before we proceed.
Next step is to find which queries are being issued at the time we are getting this lock, an issue might be in the order of operations on one of those queries.

Krinkle mentioned this in T279807: Unreliable message:"something" search since last Elastic upgrade.Apr 9 2021, 8:30 PM
Tchanders added a comment.Apr 16 2021, 6:27 PM

Thanks @TThoabala.

Looking at the more recent examples from https://logstash.wikimedia.org/goto/64abb2f021d20ed31864d50c43c898d7 (see T279807), there are two different stack traces:

from Checkuser
#0 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/Database.php(1687): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/Database.php(1662): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/Database.php(1231): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/Database.php(2367): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/Database.php(2327): Wikimedia\Rdbms\Database->doInsertNonConflicting(string, array, string)
#5 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->insert(string, array, string, array)
#6 /srv/mediawiki/php-1.36.0-wmf.33/includes/libs/rdbms/database/DBConnRef.php(369): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#7 /srv/mediawiki/php-1.36.0-wmf.33/includes/block/DatabaseBlockStore.php(167): Wikimedia\Rdbms\DBConnRef->insert(string, array, string, array)
#8 /srv/mediawiki/php-1.36.0-wmf.33/includes/block/BlockUser.php(508): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#9 /srv/mediawiki/php-1.36.0-wmf.33/includes/block/BlockUser.php(484): MediaWiki\Block\BlockUser->placeBlockInternal(boolean)
#10 /srv/mediawiki/php-1.36.0-wmf.33/includes/block/BlockUser.php(419): MediaWiki\Block\BlockUser->placeBlockUnsafe(boolean)
#11 /srv/mediawiki/php-1.36.0-wmf.33/includes/specials/SpecialBlock.php(875): MediaWiki\Block\BlockUser->placeBlock(boolean)
#12 /srv/mediawiki/php-1.36.0-wmf.33/includes/specials/SpecialBlock.php(745): SpecialBlock::processFormInternal(array, User, MediaWiki\Block\UserBlockCommandFactory, MediaWiki\Block\BlockUtils)
#13 /srv/mediawiki/php-1.36.0-wmf.33/extensions/CheckUser/src/Specials/SpecialCheckUser.php(425): SpecialBlock::processForm(array, RequestContext)
#14 /srv/mediawiki/php-1.36.0-wmf.33/extensions/CheckUser/src/Specials/SpecialCheckUser.php(356): MediaWiki\CheckUser\Specials\SpecialCheckUser->doMassUserBlockInternal(array, array, string, string)
#15 /srv/mediawiki/php-1.36.0-wmf.33/extensions/CheckUser/src/Specials/SpecialCheckUser.php(172): MediaWiki\CheckUser\Specials\SpecialCheckUser->doMassUserBlock(array, array, string, string)
#16 /srv/mediawiki/php-1.36.0-wmf.33/includes/specialpage/SpecialPage.php(645): MediaWiki\CheckUser\Specials\SpecialCheckUser->execute(NULL)
#17 /srv/mediawiki/php-1.36.0-wmf.33/includes/specialpage/SpecialPageFactory.php(1383): SpecialPage->run(NULL)
#18 /srv/mediawiki/php-1.36.0-wmf.33/includes/MediaWiki.php(309): MediaWiki\SpecialPage\SpecialPageFactory->executePath(Title, RequestContext)
#19 /srv/mediawiki/php-1.36.0-wmf.33/includes/MediaWiki.php(925): MediaWiki->performRequest()
#20 /srv/mediawiki/php-1.36.0-wmf.33/includes/MediaWiki.php(547): MediaWiki->main()
#21 /srv/mediawiki/php-1.36.0-wmf.33/index.php(53): MediaWiki->run()
#22 /srv/mediawiki/php-1.36.0-wmf.33/index.php(46): wfIndexMain()
#23 /srv/mediawiki/w/index.php(3): require(string)
#24 {main}

Here, SpecialCheckUser::doMassUserBlock eventually triggers a call to DatabaseBlockStore::insertBlock, which tries to perform the INSERT query above, and fails due to a deadlock.

from ApiBlock
#0 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/Database.php(1687): Wikimedia\Rdbms\Database->getQueryException(string, integer, string, string)
#1 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/Database.php(1662): Wikimedia\Rdbms\Database->getQueryExceptionAndLog(string, integer, string, string)
#2 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/Database.php(1231): Wikimedia\Rdbms\Database->reportQueryError(string, integer, string, string, boolean)
#3 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/Database.php(2367): Wikimedia\Rdbms\Database->query(string, string, integer)
#4 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/Database.php(2327): Wikimedia\Rdbms\Database->doInsertNonConflicting(string, array, string)
#5 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/DBConnRef.php(68): Wikimedia\Rdbms\Database->insert(string, array, string, array)
#6 /srv/mediawiki/php-1.36.0-wmf.30/includes/libs/rdbms/database/DBConnRef.php(369): Wikimedia\Rdbms\DBConnRef->__call(string, array)
#7 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/DatabaseBlockStore.php(167): Wikimedia\Rdbms\DBConnRef->insert(string, array, string, array)
#8 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/DatabaseBlock.php(637): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#9 /srv/mediawiki/php-1.36.0-wmf.30/extensions/CheckUser/includes/CheckUserHooks.php(689): MediaWiki\Block\DatabaseBlock->doAutoblock(string)
#10 /srv/mediawiki/php-1.36.0-wmf.30/includes/HookContainer/HookContainer.php(333): CheckUserHooks::doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#11 /srv/mediawiki/php-1.36.0-wmf.30/includes/HookContainer/HookContainer.php(140): MediaWiki\HookContainer\HookContainer->callLegacyHook(string, array, array, array)
#12 /srv/mediawiki/php-1.36.0-wmf.30/includes/HookContainer/HookRunner.php(3070): MediaWiki\HookContainer\HookContainer->run(string, array)
#13 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/DatabaseBlockStore.php(434): MediaWiki\HookContainer\HookRunner->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#14 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/DatabaseBlockStore.php(286): MediaWiki\Block\DatabaseBlockStore->doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock)
#15 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/BlockUser.php(526): MediaWiki\Block\DatabaseBlockStore->updateBlock(MediaWiki\Block\DatabaseBlock)
#16 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/BlockUser.php(484): MediaWiki\Block\BlockUser->placeBlockInternal(boolean)
#17 /srv/mediawiki/php-1.36.0-wmf.30/includes/block/BlockUser.php(419): MediaWiki\Block\BlockUser->placeBlockUnsafe(boolean)
#18 /srv/mediawiki/php-1.36.0-wmf.30/includes/api/ApiBlock.php(153): MediaWiki\Block\BlockUser->placeBlock(boolean)
#19 /srv/mediawiki/php-1.36.0-wmf.30/includes/api/ApiMain.php(1612): ApiBlock->execute()
#20 /srv/mediawiki/php-1.36.0-wmf.30/includes/api/ApiMain.php(592): ApiMain->executeAction()
#21 /srv/mediawiki/php-1.36.0-wmf.30/includes/api/ApiMain.php(563): ApiMain->executeActionWithErrorHandling()
#22 /srv/mediawiki/php-1.36.0-wmf.30/api.php(90): ApiMain->execute()
#23 /srv/mediawiki/php-1.36.0-wmf.30/api.php(45): wfApiMain()
#24 /srv/mediawiki/w/api.php(3): require(string)
#25 {main}

Here (as in the task description), ApiBlock triggers a call to DatabaseBlockStore::updateBlock which calls DatabaseBlockStore::doRetroactiveAutoblock, which runs a hook, handled by CheckUser::doRetroactiveAutoblock, which tries to insert an autoblock, eventually triggering DatabaseBlockStore::insertBlock to attempt the same INSERT query, which fails in the same way.

One thing these two code paths have in common is that they insert blocks in a loop. For the first path, SpecialCheckUser::doMassUserBlock iterates over an array of users that the admin wants to block, and tries to insert a block for each one. In the second path, CheckUser::doRetroactiveAutoblock iterates over the IP addresses associated with the user being blocked, and tries to insert a block for each one.

I'm not hugely familiar with different cases that would normally cause a deadlock; perhaps the loops are a problem per se, or they increase the risk of a conflict with some other request because they take longer?

I searched logstash using the filter message:"Error 1213" on the same days as these errors. There were several results, indicating that other deadlocks were happening in other parts of the code, but not at the same time as these requests. (The SpecialCheckUser stack trace came from a single isolated error, and there were two concurrent errors for the API stack trace.)

Here's a dumped example of the queries that can get performed for each iteration of the loop for the SpecialCheckUser::doMassUserBlock path (they differ depending on whether there was already an existing block, whether the target is an IP address or user account, whether we're spawning autoblocks, etc):

Iteration 1: block an IP address

SELECT actor_id,actor_user,actor_name FROM `actor` WHERE actor_name = '0:0:0:0:0:0:0:1' LIMIT 1	0.0000ms	User::load
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = '0:0:0:0:0:0:0:1'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = '0:0:0:0:0:0:0:1'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT comment_id FROM `comment` WHERE comment_hash = 2018365746 AND comment_text = 'Test' AND comment_data IS NULL LIMIT 1	1.0000ms	CommentStore::createComment
INSERT IGNORE INTO `ipblocks` (ipb_address,ipb_user,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_range_start,ipb_range_end,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,ipb_reason_id,ipb_by_actor) VALUES ('0:0:0:0:0:0:0:1',0,'20210416173449',0,1,1,0,'20210423173449','v6-00000000000000000000000000000001','v6-00000000000000000000000000000001',0,0,1,NULL,1,'28',1)	0.0000ms	MediaWiki\Block\DatabaseBlockStore::insertBlock
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = '0:0:0:0:0:0:0:1'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT page_id,page_len,page_is_redirect,page_latest,page_restrictions,page_content_model FROM `page` WHERE page_namespace = 2 AND page_title = '0:0:0:0:0:0:0:1' LIMIT 1	0.0000ms	LinkCache::fetchPageRow
SELECT comment_id FROM `comment` WHERE comment_hash = 2018365746 AND comment_text = 'Test' AND comment_data IS NULL LIMIT 1	0.0000ms	CommentStore::createComment
INSERT INTO `logging` (log_type,log_action,log_timestamp,log_namespace,log_title,log_page,log_params,log_comment_id,log_actor) VALUES ('block','block','20210416173449',2,'0:0:0:0:0:0:0:1',150,'a:3:{s:11:\"5::duration\";s:6:\"1 week\";s:8:\"6::flags\";s:17:\"anononly,nocreate\";s:8:\"sitewide\";b:1;}','28',1)	0.0000ms	ManualLogEntry::insert
INSERT IGNORE INTO `log_search` (ls_field,ls_value,ls_log_id) VALUES ('ipb_id',1126,1984)	0.0000ms	ManualLogEntry::insert

Iteration 2: block a user

SELECT actor_id,actor_user,actor_name FROM `actor` WHERE actor_name = 'Blockable' LIMIT 1	0.0000ms	User::load
SELECT user_id,user_name,user_real_name,user_email,user_touched,user_token,user_email_authenticated,user_email_token,user_email_token_expires,user_registration,user_editcount,user_actor.actor_id FROM `user` JOIN `actor` `user_actor` ON ((user_actor.actor_user = user_id)) WHERE user_id = 4 LIMIT 1	0.0000ms	User::loadFromDatabase
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = 'Blockable'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT actor_id,actor_user,actor_name FROM `actor` WHERE actor_name = 'Blockable' LIMIT 1	0.0000ms	User::load
SELECT user_id,user_name,user_real_name,user_email,user_touched,user_token,user_email_authenticated,user_email_token,user_email_token_expires,user_registration,user_editcount,user_actor.actor_id FROM `user` JOIN `actor` `user_actor` ON ((user_actor.actor_user = user_id)) WHERE user_id = 4 LIMIT 1	0.0000ms	User::loadFromDatabase
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = 'Blockable'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT actor_id,actor_user,actor_name FROM `actor` WHERE actor_name = 'Blockable' LIMIT 1	0.0000ms	User::load
SELECT user_id,user_name,user_real_name,user_email,user_touched,user_token,user_email_authenticated,user_email_token,user_email_token_expires,user_registration,user_editcount,user_actor.actor_id FROM `user` JOIN `actor` `user_actor` ON ((user_actor.actor_user = user_id)) WHERE user_id = 4 LIMIT 1	0.0000ms	User::loadFromDatabase
SELECT comment_id FROM `comment` WHERE comment_hash = 2018365746 AND comment_text = 'Test' AND comment_data IS NULL LIMIT 1	0.0000ms	CommentStore::createComment
INSERT IGNORE INTO `ipblocks` (ipb_address,ipb_user,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_range_start,ipb_range_end,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,ipb_reason_id,ipb_by_actor) VALUES ('Blockable',4,'20210416173449',0,0,1,1,'infinity','','',0,0,1,NULL,1,'28',1)	0.0000ms	MediaWiki\Block\DatabaseBlockStore::insertBlock
SELECT ipb_id AS `value` FROM `ipblocks` WHERE ipb_address = 'Blockable' AND ipb_user = 4 AND (ipb_expiry < '20210416173449')	13.0000ms	MediaWiki\Block\DatabaseBlockStore::insertBlock
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = 'Blockable'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT comment_id FROM `comment` WHERE comment_hash = 2018365746 AND comment_text = 'Test' AND comment_data IS NULL LIMIT 1	0.0000ms	CommentStore::createComment
UPDATE `ipblocks` SET ipb_address = 'Blockable',ipb_user = 4,ipb_timestamp = '20210416160429',ipb_auto = 0,ipb_anon_only = 0,ipb_create_account = 1,ipb_enable_autoblock = 1,ipb_expiry = 'infinity',ipb_range_start = '',ipb_range_end = '',ipb_deleted = 0,ipb_block_email = 0,ipb_allow_usertalk = 1,ipb_parent_block_id = 0,ipb_sitewide = 1,ipb_reason_id = '28',ipb_by_actor = 1 WHERE ipb_id = 1118	0.0000ms	MediaWiki\Block\DatabaseBlockStore::updateBlock
DELETE FROM `ipblocks_restrictions` WHERE ir_ipb_id = 1118	0.0000ms	MediaWiki\Block\BlockRestrictionStore::deleteByBlockId
UPDATE `ipblocks` SET ipb_create_account = 1,ipb_deleted = 0,ipb_allow_usertalk = 1,ipb_sitewide = 1,ipb_reason_id = '28',ipb_by_actor = 1 WHERE ipb_parent_block_id = 1118	0.0000ms	MediaWiki\Block\DatabaseBlockStore::updateBlock
DELETE `ipblocks_restrictions` FROM `ipblocks_restrictions`, `ipblocks` WHERE ir_ipb_id=ipb_id AND ipb_parent_block_id = 1118	0.0000ms	MediaWiki\Block\BlockRestrictionStore::deleteByParentBlockId
SELECT actor_id,actor_user,actor_name FROM `actor` WHERE actor_name = 'Blockable' LIMIT 1	0.0000ms	User::load
SELECT user_id,user_name,user_real_name,user_email,user_touched,user_token,user_email_authenticated,user_email_token,user_email_token_expires,user_registration,user_editcount,user_actor.actor_id FROM `user` JOIN `actor` `user_actor` ON ((user_actor.actor_user = user_id)) WHERE user_id = 4 LIMIT 1	0.0000ms	User::loadFromDatabase
SELECT cuc_ip FROM `cu_changes` WHERE cuc_user = 4 ORDER BY cuc_timestamp DESC LIMIT 1	0.0000ms	CheckUserHooks::doRetroactiveAutoblock
SELECT lc_value FROM `l10n_cache` WHERE lc_lang = 'en' AND lc_key = 'messages:block-autoblock-exemptionlist' LIMIT 1	0.0000ms	LCStoreDB::get
SELECT page_id,page_len,page_is_redirect,page_latest,page_restrictions,page_content_model,page_touched FROM `page` WHERE page_namespace = 8 AND page_title = 'Autoblock_whitelist' LIMIT 1	0.0000ms	LinkCache::fetchPageRow
SELECT lc_value FROM `l10n_cache` WHERE lc_lang = 'en' AND lc_key = 'messages:autoblock_whitelist' LIMIT 1	0.0000ms	LCStoreDB::get
SELECT ipb_id,ipb_address,ipb_timestamp,ipb_auto,ipb_anon_only,ipb_create_account,ipb_enable_autoblock,ipb_expiry,ipb_deleted,ipb_block_email,ipb_allow_usertalk,ipb_parent_block_id,ipb_sitewide,comment_ipb_reason.comment_text AS `ipb_reason_text`,comment_ipb_reason.comment_data AS `ipb_reason_data`,comment_ipb_reason.comment_id AS `ipb_reason_cid`,actor_ipb_by.actor_user AS `ipb_by`,actor_ipb_by.actor_name AS `ipb_by_text`,ipb_by_actor FROM `ipblocks` JOIN `comment` `comment_ipb_reason` ON ((comment_ipb_reason.comment_id = ipb_reason_id)) JOIN `actor` `actor_ipb_by` ON ((actor_ipb_by.actor_id = ipb_by_actor)) WHERE ipb_address = '0:0:0:0:0:0:0:1'	0.0000ms	MediaWiki\Block\DatabaseBlock::newLoad
SELECT page_id,page_len,page_is_redirect,page_latest,page_restrictions,page_content_model FROM `page` WHERE page_namespace = 2 AND page_title = 'Blockable' LIMIT 1	0.0000ms	LinkCache::fetchPageRow
SELECT comment_id FROM `comment` WHERE comment_hash = 2018365746 AND comment_text = 'Test' AND comment_data IS NULL LIMIT 1	0.0000ms	CommentStore::createComment
INSERT INTO `logging` (log_type,log_action,log_timestamp,log_namespace,log_title,log_page,log_params,log_comment_id,log_actor) VALUES ('block','reblock','20210416173449',2,'Blockable',24,'a:3:{s:11:\"5::duration\";s:8:\"infinity\";s:8:\"6::flags\";s:8:\"nocreate\";s:8:\"sitewide\";b:1;}','28',1)	0.0000ms	ManualLogEntry::insert
INSERT IGNORE INTO `log_search` (ls_field,ls_value,ls_log_id) VALUES ('ipb_id',1118,1985)	0.0000ms	ManualLogEntry::insert

@Krinkle Does this help at all?

None of this looks related to AHT's recent work; it looks like a legacy problem that existed before we got here. But we may be well placed to help work out a fix without breaking the behaviour of these features. I think we could do with some outside help in figuring out the precise logical problem, if there is one.

• TThoabala added a comment.Apr 20 2021, 5:22 AM

I tried running the two paths alternating sleeps, but could not recreate an error,
From the two above queries doesn't seem like the order of queries is reversed at any point.
I can't say I see an obvious logical error at the moment.
one thing I got from https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html is
Keep transactions small and short in duration to make them less prone to collision.
Maybe the loops might be causing some issues as @Tchanders mentioned above, because transactions might still be running/holding locks way after the statements executed.
I am still open to ideas and suggestions on how I can try replicate I can still do one more try on this.

Tchanders edited projects, added Anti-Harassment-Team; removed Anti-Harassment-Team (The Letter Song).Apr 20 2021, 8:56 AM

Thanks for the information @TThoabala. Moving this to the backlog for now - we'll pick it up again if/when there's more work for us to do.

Tchanders moved this task from Untriaged to Tracking work by others on the Anti-Harassment-Team board.Apr 20 2021, 8:56 AM
Krinkle added a comment.Jul 29 2022, 1:33 AM

Still seen. The ones during the last 30 days were generally during an ApiBlock POST request, made on-wiki from subpages of https://en.wikipedia.org/wiki/Wikipedia:Sockpuppet_investigations (presumably via JavaScript?).

Example below from query message:"insertBlock" on mediawiki-errors document (Single document link):

Error
DBQueryError: Deadlock found when trying to get lock; try restarting transaction
Function: MediaWiki\Block\DatabaseBlockStore::insertBlock
Query: INSERT IGNORE INTO `ipblocks` (ipb_address,..,ipb_reason_id) VALUES (...)
trace
from ..
#7 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/DatabaseBlockStore.php(209): Wikimedia\Rdbms\DBConnRef->insert(string, array, string, array)
#8 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/DatabaseBlock.php(641): MediaWiki\Block\DatabaseBlockStore->insertBlock(MediaWiki\Block\DatabaseBlock)
#9 /srv/mediawiki/php-1.39.0-wmf.18/extensions/CheckUser/src/Hooks.php(851): MediaWiki\Block\DatabaseBlock->doAutoblock(string)
#10 /srv/mediawiki/php-1.39.0-wmf.18/includes/HookContainer/HookContainer.php(338): MediaWiki\CheckUser\Hooks::doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#11 /srv/mediawiki/php-1.39.0-wmf.18/includes/HookContainer/HookContainer.php(137): MediaWiki\HookContainer\HookContainer->callLegacyHook(string, array, array, array)
#12 /srv/mediawiki/php-1.39.0-wmf.18/includes/HookContainer/HookRunner.php(3024): MediaWiki\HookContainer\HookContainer->run(string, array)
#13 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/DatabaseBlockStore.php(496): MediaWiki\HookContainer\HookRunner->onPerformRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock, array)
#14 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/DatabaseBlockStore.php(344): MediaWiki\Block\DatabaseBlockStore->doRetroactiveAutoblock(MediaWiki\Block\DatabaseBlock)
#15 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/BlockUser.php(585): MediaWiki\Block\DatabaseBlockStore->updateBlock(MediaWiki\Block\DatabaseBlock)
#16 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/BlockUser.php(532): MediaWiki\Block\BlockUser->placeBlockInternal(boolean)
#17 /srv/mediawiki/php-1.39.0-wmf.18/includes/block/BlockUser.php(466): MediaWiki\Block\BlockUser->placeBlockUnsafe(boolean)
#18 /srv/mediawiki/php-1.39.0-wmf.18/includes/api/ApiBlock.php(188): MediaWiki\Block\BlockUser->placeBlock(boolean)
#19 /srv/mediawiki/php-1.39.0-wmf.18/includes/api/ApiMain.php(1901): ApiBlock->execute()
#20 /srv/mediawiki/php-1.39.0-wmf.18/includes/api/ApiMain.php(875): ApiMain->executeAction()
#21 /srv/mediawiki/php-1.39.0-wmf.18/includes/api/ApiMain.php(846): ApiMain->executeActionWithErrorHandling()
#22 /srv/mediawiki/php-1.39.0-wmf.18/api.php(90): ApiMain->execute()
#23 /srv/mediawiki/php-1.39.0-wmf.18/api.php(45): wfApiMain()
#24 /srv/mediawiki/w/api.php(3): require(string)
#25 {main}
ST47 subscribed.Jul 29 2022, 3:24 AM

Checkusers and clerks do use an spihelper script which gives them a tool on the Sockpuppet Investigations subpages to quickly block accounts, tag userpages, and update the SPI subpage itself. If there's a deadlock related to inserting autoblocks, I would guess that multiple users with the same last used IP address are being blocked simultaneously, and so multiple autoblocks for the same target could be trying to be inserted in separate threads.

The JS tool I linked tries to block all the users asynchronously, so it is certainly possible for multiple requests (attempting to block different but related users) to be executing on different PHP threads at the same time.

Zabe subscribed.Aug 2 2022, 11:38 PM
Krinkle moved this task from Aug 2020 to Older on the Wikimedia-production-error board.Sep 8 2022, 4:20 PM
Dreamy_Jazz subscribed.EditedSep 29 2022, 6:42 PM

In summary:

  • spihelper.js makes the calls to block users through the tool asynchronous.
  • When spihelper.js does this multiple accounts could be blocked by different workers at the same time
  • As part of the block, spihelper by default sets the block settings to autoblock the accounts
  • The autoblocking code calls CheckUser's Hooks::onPerformRetroactiveAutoblock
  • Unless the target of the block is a IP, this code attempts to make autoblocks exclusively.
  • CheckUser extracts the last IP associated with the user and calls DatabaseBlock::doAutoblock with that IP

This is where CheckUser stops interacting to make the autoblock. As such, unless doAutoblock needs to be protected behind some kind of throttling system, this is just exposing problematic code in core. This is also the case because CheckUser does not run any insert queries when ::onPerformRetroactiveAutoblock is called and does not read from any table but cu_changes, so all queries to ipblocks (and thus what could be leading to an autoblock) are generated by core after checking for existing blocks. This is almost certainly an issue that's exposed when multiple users which all have used one IP last, and the timing is right that the existing block check says no block for two different workers before running the query.

Dreamy_Jazz added a comment.Aug 16 2023, 6:35 AM

Happened again: https://logstash.wikimedia.org/app/discover#/doc/logstash-*/logstash-deploy-1-7.0.0-1-2023.08.15?id=6hG--YkB6U_kV85ATlAN

Dreamy_Jazz added a comment.Aug 21 2023, 5:01 PM

This has spammed logstash on eswiki by someone using a tool at https://es.wikipedia.org/wiki/Especial:Massblock

Aklapper removed • TThoabala as the assignee of this task.Oct 9 2023, 1:01 AM
Aklapper added a subscriber: • TThoabala.

Removing inactive task assignee (please do so as part of offboarding steps).

• TAdeleye_WMF edited projects, added Trust and Safety Product Team (PM); removed Anti-Harassment-Team.May 14 2024, 4:23 PM
• TAdeleye_WMF moved this task from PM to Tracking work by others on the Trust and Safety Product Team board.May 14 2024, 4:25 PM
• TAdeleye_WMF edited projects, added Trust and Safety Product Team; removed Trust and Safety Product Team (PM).
Umherirrender closed this task as Invalid.Jul 17 2024, 7:07 PM
Umherirrender subscribed.

Table ipblocks is replaced by other tables.

It is possible the deadlock situation is not there. Closing this task and if there are still problems a new task with a fresh stack trace on the new tables should be used.

Dreamy_Jazz reopened this task as Open.Jul 23 2024, 10:32 AM

This is just occurring in a different way. I'll update the task description.

Dreamy_Jazz renamed this task from Error 1213: Deadlock found when trying to get lock; try restarting transaction Function: MediaWiki\Block\DatabaseBlockStore::insertBlockQuery to RuntimeException: Duplicate block_target rows detected on retroactive autoblock.Jul 23 2024, 10:35 AM
Dreamy_Jazz updated the task description. (Show Details)
Dreamy_Jazz renamed this task from RuntimeException: Duplicate block_target rows detected on retroactive autoblock to Error 1213: Deadlock found when trying to get lock; try restarting transaction Function: MediaWiki\Block\DatabaseBlockStore::acquireTarget.Jul 23 2024, 10:38 AM
Dreamy_Jazz updated the task description. (Show Details)
Dreamy_Jazz updated the task description. (Show Details)Jul 23 2024, 10:44 AM
Umherirrender added a subscriber: tstarling.Jul 23 2024, 12:43 PM
Umherirrender unsubscribed.Sep 3 2024, 7:55 PM
Dreamy_Jazz added a comment.Sep 6 2024, 9:48 AM

15 of these in the last week. I think we can solve this using locks.

Lucas_Werkmeister_WMDE mentioned this in T403248: Frequent Wikimedia\Rdbms\DBQueryError on action=block.Aug 29 2025, 10:20 AM
Dreamy_Jazz merged a task: T403248: Frequent Wikimedia\Rdbms\DBQueryError on action=block.Aug 29 2025, 10:22 AM
Dreamy_Jazz added a project: Trust and Safety Product Sprint.
Dreamy_Jazz moved this task from Backlog to Maintenance to triage on the Trust and Safety Product Sprint board.
Dreamy_Jazz added subscribers: Dragoniez, Lucas_Werkmeister_WMDE, A_smart_kitten.
Lucas_Werkmeister_WMDE unsubscribed.Aug 29 2025, 10:37 AM
Dreamy_Jazz moved this task from Tracking work by others to Engineering on the Trust and Safety Product Team board.Aug 29 2025, 10:37 AM
OKryva-WMF edited projects, added Product Safety and Integrity; removed Trust and Safety Product Sprint.Sep 26 2025, 12:56 PM
OKryva-WMF moved this task from Inbox to Next sprint on the Product Safety and Integrity board.Sep 26 2025, 12:57 PM
OKryva-WMF added a project: Essential-Work.Oct 3 2025, 2:21 PM
OKryva-WMF removed a project: Trust and Safety Product Team.Oct 10 2025, 9:54 AM
Dreamy_Jazz moved this task from Next sprint to Essential Work Sprint (Dec 15th - Jan 9th) on the Product Safety and Integrity board.Nov 19 2025, 12:38 PM
Dreamy_Jazz edited projects, added Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)); removed Product Safety and Integrity.
tstarling added a comment.Nov 28 2025, 4:43 AM
In T260838#10124651, @Dreamy_Jazz wrote:

15 of these in the last week. I think we can solve this using locks.

I added a lock in April 2025 1134635 . I still see some deadlocks though.

DatGuy subscribed.Dec 7 2025, 12:10 PM
OKryva-WMF moved this task from Backlog to Ready on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.Dec 15 2025, 2:18 PM
Dreamy_Jazz claimed this task.Dec 16 2025, 1:47 PM
Dreamy_Jazz moved this task from Ready to In progress on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.
gerritbot added a comment.Dec 16 2025, 4:11 PM

Change #1218789 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/core@master] Use a lock in DatabaseBlockStore::doAutoblock

https://gerrit.wikimedia.org/r/1218789

gerritbot added a project: Patch-For-Review.Dec 16 2025, 4:11 PM
Dreamy_Jazz moved this task from In progress to Needs review on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.Dec 16 2025, 4:11 PM
gerritbot added a comment.Dec 17 2025, 3:06 PM

Change #1218789 merged by jenkins-bot:

[mediawiki/core@master] Use a lock in DatabaseBlockStore::doAutoblock

https://gerrit.wikimedia.org/r/1218789

Dreamy_Jazz moved this task from Needs review to Needs QA on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.Dec 17 2025, 3:06 PM

QA for this will be to validate that the errors listed in the task description no longer occur on production, which will be done by an engineer

Maintenance_bot removed a project: Patch-For-Review.Dec 17 2025, 3:31 PM
gerritbot added a comment.Dec 17 2025, 5:56 PM

Change #1219193 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/core@master] Follow-up: Use a lock in DatabaseBlockStore::doAutoblock

https://gerrit.wikimedia.org/r/1219193

gerritbot added a project: Patch-For-Review.Dec 17 2025, 5:56 PM
Dreamy_Jazz moved this task from Needs QA to Needs review on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.Dec 17 2025, 5:59 PM
gerritbot added a comment.Dec 17 2025, 7:17 PM

Change #1219205 had a related patch set uploaded (by Dreamy Jazz; author: Dreamy Jazz):

[mediawiki/core@master] [WIP] Create TransactionProfiler::isSilenced

https://gerrit.wikimedia.org/r/1219205

gerritbot added a comment.Dec 18 2025, 3:28 AM

Change #1219193 merged by jenkins-bot:

[mediawiki/core@master] Follow-up: Use a lock in DatabaseBlockStore::doAutoblock

https://gerrit.wikimedia.org/r/1219193

Dreamy_Jazz moved this task from Needs review to Needs QA on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.Dec 18 2025, 12:49 PM
Dreamy_Jazz added a comment.Jan 9 2026, 10:53 AM

Initial lack of logs for wmf.10 looks promising, but given the log does not appear frequently I would suggest waiting until next week sometime to validate that this has been fixed. The relevant logstash filters that show the logs are:

Izno subscribed.Jan 11 2026, 2:08 AM

You may have fixed a problem, but it hasn't affected spihelper. On https://en.wikipedia.org/wiki/Wikipedia:Sockpuppet_investigations/Kipperfield just now:

image.png (527×626 px, 91 KB)

Dreamy_Jazz added a comment.Jan 14 2026, 1:52 PM
In T260838#11510070, @Izno wrote:

You may have fixed a problem, but it hasn't affected spihelper. On https://en.wikipedia.org/wiki/Wikipedia:Sockpuppet_investigations/Kipperfield just now:

image.png (527×626 px, 91 KB)

Thanks for the screenshot. The logs also show this instance of the issue

Dreamy_Jazz moved this task from Needs QA to Backlog on the Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)) board.Jan 14 2026, 1:54 PM

I'm not sure how else to try to fix this issue. Moving back to Backlog

OKryva-WMF removed a project: Product Safety and Integrity (Essential Work Sprint (Dec 15th - Jan 9th)).Mon, Jan 19, 2:59 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits