Lately we had some amount of contention due to fast edits by a user (around 1 per second) while executing the following sql:
UPDATE `user` SET user_editcount=user_editcount+1 WHERE user_id = '<user_id>' AND (user_editcount IS NOT NULL)
For example:
{
"_index": "logstash-2018.08.24",
"_type": "mediawiki",
"_id": "AWVrBGaToOODFPKvBW2D",
"_version": 1,
"_score": null,
"_source": {
"server": "commons.wikimedia.org",
"db_server": "10.64.48.23",
"wiki": "commonswiki",
"channel": "DBQuery",
"type": "mediawiki",
"error": "Lock wait timeout exceeded; try restarting transaction (10.64.48.23)",
"http_method": "POST",
"@version": 1,
"host": "mw1221",
"shard": "s4",
"sql1line": "UPDATE `user` SET user_editcount=user_editcount+1 WHERE user_id = '1779721' AND (user_editcount IS NOT NULL)",
"fname": "User::incEditCountImmediate",
"errno": 1205,
"unique_id": "W3@-gwpAMDgAAH1gJiIAAACQ",
"method": "Wikimedia\\Rdbms\\Database::makeQueryException",
"level": "ERROR",
"ip": "10.64.32.69",
"mwversion": "1.32.0-wmf.18",
"message": "User::incEditCountImmediate\t10.64.48.23\t1205\tLock wait timeout exceeded; try restarting transaction (10.64.48.23)\tUPDATE `user` SET user_editcount=user_editcount+1 WHERE user_id = '1779721' AND (user_editcount IS NOT NULL)",
"normalized_message": "{fname}\t{db_server}\t{errno}\t{error}\t{sql1line}",
"url": "/w/api.php",
"tags": [
"syslog",
"es",
"es"
],
"reqId": "W3@-gwpAMDgAAH1gJiIAAACQ",
"referrer": "https://commons.wikimedia.org/w/index.php?title=Special:Search&limit=500&offset=0&profile=default&search=Krishna+Tirath&searchToken=5its02a4fpzskfv4z7pga8smd",
"@timestamp": "2018-08-24T08:19:35.000Z",
"db_name": "commonswiki",
"db_user": "wikiuser"
},
"fields": {
"@timestamp": [
1535098775000
]
},
"sort": [
1535098775000
]
}While I don't see a lot of problems with this- UPDATE inside the transaction needs to lock the user row to update consistently the row count on write, the rate of edits is around 1 per second, and we have seen faster bots in the past doing a larger amount of edits, so I am worried about the limits this causes on edit rate in general. Couldn't the count be done asynchronously?
