How to avoid deadlock for this query?

by Oliver Nybroe   Last Updated April 15, 2019 10:06 AM

I am running a MySql 5.7 database and I have a query which results in a deadlock as it is running in multiple threads.

I am trying to figure out how to avoid this deadlock, but so far I haven't been able to do it. I even tried with:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

The query running looks like this

 UPDATE yjob_userrank target
              JOIN
              (
                SELECT userranks.id,
                       @`row_number` := CASE
                                          WHEN @job_id = yjob_id THEN @`row_number` + 1
                                          ELSE 1
                         END AS `row_number`,
                       @job_id := yjob_id
                FROM (SELECT @job_id := NULL, @`row_number` := NULL) vars,
                     (
                         SELECT yjob_userrank.id,
                                user_id,
                                yjob_id,
                                rank_wo_date + users.`rank` as combined_rank,
                                is_match
                         FROM yjob_userrank
                             JOIN users ON users.id = yjob_userrank.user_id
                         WHERE yjob_id IN (29,30,31,39,40,67,69,70,77,78,83,86,87,96,97,98,116,119,121,137,143,154,155,158,162,163,178,179,193,202,220,228,236,314,326,329,348,355,387,394,406,409,421,438,453,457,465,475,477,483,488,501,506,514,516,533,535,543,546,549,566,579,583,591,592,597,599,609,611,617,618,622,627,670,697,716,732,733,761,767,804,821,846,854,866,884,890,896,900,910,916,921,929,942,946,947,954,963,966,970,975,978,987,988,996,998,1000,1011,1012,1017,1020,1029,1034,1044,1050,1076,1082,1107,1119,1136,1154,1162,1175,1184,1186,1194,1196,1197,1210,1214,1215,1216,1217,1223,1241,1249,1281,1283,1286,1289,1298,1305,1316,1336,1342,1362,1366,1390,1420,1422,1427,1428,1429,1434,1436,1439,1444,1446,1447,1448,1449,1451,1460,1467,1476,1483,1485,1494,1500,1507,1510,1518,1520,1529,1535,1539,1557,1583,1584)
                         ORDER BY yjob_id, is_match DESC
                     ) as userranks
                ORDER BY combined_rank DESC
              ) source on target.id = source.id
            SET relative_rank = `row_number`
            WHERE yjob_id IN (29,30,31,39,40,67,69,70,77,78,83,86,87,96,97,98,116,119,121,137,143,154,155,158,162,163,178,179,193,202,220,228,236,314,326,329,348,355,387,394,406,409,421,438,453,457,465,475,477,483,488,501,506,514,516,533,535,543,546,549,566,579,583,591,592,597,599,609,611,617,618,622,627,670,697,716,732,733,761,767,804,821,846,854,866,884,890,896,900,910,916,921,929,942,946,947,954,963,966,970,975,978,987,988,996,998,1000,1011,1012,1017,1020,1029,1034,1044,1050,1076,1082,1107,1119,1136,1154,1162,1175,1184,1186,1194,1196,1197,1210,1214,1215,1216,1217,1223,1241,1249,1281,1283,1286,1289,1298,1305,1316,1336,1342,1362,1366,1390,1420,1422,1427,1428,1429,1434,1436,1439,1444,1446,1447,1448,1449,1451,1460,1467,1476,1483,1485,1494,1500,1507,1510,1518,1520,1529,1535,1539,1557,1583,1584)

And the schema is

create table yjob_userrank
(
    id int unsigned auto_increment
        primary key,
    user_id int unsigned not null,
    yjob_id int unsigned not null,
    rank_w_date decimal(5,2) default 0.00 not null,
    rank_wo_date decimal(5,2) default 0.00 not null,
    is_match tinyint unsigned not null,
    relative_rank smallint(6) null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint yjob_userrank_yjob_id_user_id_unique
        unique (yjob_id, user_id)
)
collate=utf8_unicode_ci;

create index yjob_userrank_user_id_index
    on yjob_userrank (user_id);

create index yjob_userrank_yjob_id_is_match_rank_wo_date_index
    on yjob_userrank (yjob_id, is_match, rank_wo_date);

This code is running in a laravel application and the method looks like this

    private function calculateRelativeRanks(array $jobIds)
    {
        $jobIdsWhere = rtrim(str_repeat("?,", count($jobIds)), ',');

        $this->db->statement('SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED');
        $this->db->transaction(function (ConnectionInterface $db) use ($jobIds, $jobIdsWhere) {
            $db->statement("
            UPDATE yjob_userrank target
              JOIN
              (
                SELECT userranks.id,
                       @`row_number` := CASE
                                          WHEN @job_id = yjob_id THEN @`row_number` + 1
                                          ELSE 1
                         END AS `row_number`,
                       @job_id := yjob_id
                FROM (SELECT @job_id := NULL, @`row_number` := NULL) vars,
                     (
                         SELECT yjob_userrank.id,
                                user_id,
                                yjob_id,
                                rank_wo_date + users.`rank` as combined_rank,
                                is_match
                         FROM yjob_userrank
                             JOIN users ON users.id = yjob_userrank.user_id
                         WHERE yjob_id IN ($jobIdsWhere)
                     ) as userranks
                ORDER BY yjob_id, is_match DESC, combined_rank DESC
              ) source on target.id = source.id
            SET relative_rank = `row_number`
            WHERE yjob_id IN ($jobIdsWhere)
        ", array_merge($jobIds, $jobIds));
        }, 3);
    }

The query only updates the field relative_rank, but it does not look at this field in the select part of it. I am okay with some data inconsistencies by reading uncommitted data, but even changing the isolation level to READ UNCOMMITTED, did not fix the deadlock.



Related Questions


Updated April 09, 2015 02:02 AM

Updated June 29, 2018 10:06 AM

Updated October 24, 2018 14:06 PM

Updated July 12, 2016 08:02 AM

Updated June 24, 2017 00:06 AM