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

Assigned To

None

Authored By

	BPirkle
	Aug 19 2020, 6:18 PM

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 Objects

Mentioned In: T279807: Unreliable message:"something" search since last Elastic upgrade
Mentioned Here: T279807: Unreliable message:"something" search since last Elastic upgrade

Event Timeline

BPirkle created this task.Aug 19 2020, 6:18 PM

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

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 board.Mar 11 2021, 6:26 PM

Niharika moved this task from Production-error to Triage/To be Estimated on the Anti-Harassment 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 board.

Tchanders moved this task from Cards ready for development to The Letter Song on the Anti-Harassment board.Apr 1 2021, 11:19 AM

Tchanders edited projects, added Anti-Harassment (The Letter Song); removed Anti-Harassment.

• 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 (The Letter Song) board.

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

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.

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.

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 board.Apr 20 2021, 8:56 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}

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

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.

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

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

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.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).

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.

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

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

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

Description

Error

Details

Related Objects

Event Timeline

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