There is a request that is executed in parallel by multiple threads in its own transactions:

SELECT `account_id` FROM `accounts` WHERE `request_mts` < :request_mts ORDER BY `request_mts` ASC LIMIT 1 FOR UPDATE

... where "account_id" is the primary key, and ": request_mts" is the number of milliseconds.

This query often leads to deadlocks:

Trying to get lock; try restarting transaction

QMYSQL3: Unable to store statement results

The problem, as we found out, is related to the sorting of the sample. If the sorting is removed from the query, then the deadlocks no longer occur:

 SELECT `account_id` FROM `accounts` WHERE `request_mts` < :request_mts LIMIT 1 FOR UPDATE

Is there a way to save the sort, but avoid the deadlocks?

Updated

After the selection (if a suitable account_id was found), there is a request to update the request_mts column in the same transaction as the sample. After that, the transaction is closed.

 UPDATE `accounts` SET `request_mts` = :request_mts WHERE `account_id` = :account_id
  • And the processes then change this request_mts? or how they manage not to get hung up on processing the same record? In general, you need to think about whether it is possible to eliminate the need for locks, for example, distributing records for processing threads for the remainder of dividing mts by the number of threads or by one stream - the dispatcher - Mike
  • This is at what level of isolation? try dropping to READ COMMITTED . The presence of a covering index can also help. - Akina
  • @Mike, added the answer. Next comes the usual update , which updates the request_mts if the previous account_id was found in the previous step. Can you tell a little more about this: "distributing records for processing streams by the remainder of dividing mts by the number of streams"? - alexis031182
  • @Akina, yes, READ COMMITTED . What is a covering index? - alexis031182
  • @ alexis031182 if you specify the request_mts < :request_mts and request_mts % ΠΊΠΎΠ»-Π²ΠΎ-ΠΏΠΎΡ‚ΠΎΠΊΠΎΠ² = Π½ΠΎΠΌΠ΅Ρ€-ΠΏΠΎΡ‚ΠΎΠΊΠ° condition request_mts < :request_mts and request_mts % ΠΊΠΎΠ»-Π²ΠΎ-ΠΏΠΎΡ‚ΠΎΠΊΠΎΠ² = Π½ΠΎΠΌΠ΅Ρ€-ΠΏΠΎΡ‚ΠΎΠΊΠ° then two threads will not physically be able to receive the same record, because their numbers are different. at the same time, the records for the streams will be distributed more or less evenly (unless of course you have the same number in milliseconds constantly) - Mike

0