By Oliver Nybroe


2019-04-15 09:50:24 8 Comments

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` := IF(@job_id = yjob_id, @row_number+1, 1) AS `row_number`,
           @job_id := yjob_id AS job_id
    FROM (
             SELECT yjob_userrank.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 (1716, 1667)
         ) as userranks
    ORDER BY yjob_id, is_match DESC, combined_rank DESC
  ) source on target.id = source.id
SET relative_rank = `row_number`

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` := IF(@job_id = yjob_id, @row_number+1, 1) AS `row_number`,
                       @job_id := yjob_id AS job_id
                FROM (
                         SELECT yjob_userrank.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`
        ", $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.

By running SHOW ENGINE INNODB STATUS; the block with LATEST DETECTED DEADLOCK shows the following gist

Explain log of the query
+----+-------------+---------------+------------+--------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+-----------------------+
| id | select_type |     table     | partitions |  type  |                                                   possible_keys                                                    |                 key                  | key_len |              ref               | rows | filtered |         Extra         |
+----+-------------+---------------+------------+--------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+-----------------------+
|  1 | PRIMARY     | <derived2>    |            | system |                                                                                                                    |                                      |         |                                |    1 |      100 | Using filesort        |
|  1 | PRIMARY     | <derived3>    |            | ALL    |                                                                                                                    |                                      |         |                                |  600 |      100 |                       |
|  3 | DERIVED     | yjob_userrank |            | range  | yjob_userrank_yjob_id_user_id_unique,yjob_userrank_user_id_index,yjob_userrank_yjob_id_is_match_rank_wo_date_index | yjob_userrank_yjob_id_user_id_unique |       4 |                                |  600 |      100 | Using index condition |
|  3 | DERIVED     | users         |            | eq_ref | PRIMARY                                                                                                            | PRIMARY                              |       4 | worksome.yjob_userrank.user_id |    1 |      100 |                       |
|  2 | DERIVED     |               |            |        |                                                                                                                    |                                      |         |                                |      |          | No tables used        |
+----+-------------+---------------+------------+--------+--------------------------------------------------------------------------------------------------------------------+--------------------------------------+---------+--------------------------------+------+----------+-----------------------+

As the database is MySql 5.7, I do not have Window functionality, so it is basically mirroring that functionality. If I had Window support, the query would look like this:

UPDATE yjob_userrank target
 JOIN (
     SELECT
        yjob_userrank.id,
        yjob_id,
        ROW_NUMBER() over (w) as `relative_rank`
    FROM yjob_userrank
         JOIN users ON users.id = yjob_userrank.user_id
    WHERE yjob_id IN (1716, 1667)
    WINDOW w AS (PARTITION BY yjob_id ORDER BY yjob_id, is_match DESC, rank_wo_date + users.`rank` DESC)
     ) source on target.id = source.id
SET target.relative_rank = source.relative_rank
WHERE target.yjob_id IN (1716, 1667)

UPDATE 1: I reduced the query to the following by suggestions from @Kondybas, this fixed my deadlock.

UPDATE yjob_userrank AS yjur
SET
    relative_rank = @`row_number` := IF(@job_id = yjob_id, @row_number+1, 1),
    yjob_id = @job_id := yjur.yjob_id
WHERE yjur.yjob_id IN (1716, 1667)
ORDER BY
 yjur.yjob_id
 , yjur.is_match DESC
 , yjur.rank_wo_date + (SELECT users.`rank` FROM users WHERE users.id = yjur.user_id) DESC;

1 comments

@Kondybas 2019-04-15 14:43:12

Your query is too complicated and involves too many nested loops for join proceeding. Also there are some portions of code having no effect at all. I think it can be simplified like that:

UPDATE yjob_userrank AS target
  JOIN ( SELECT yjob_userrank.id AS id
              , @job_id := yjob_id AS job_id
              , IF(@job_id = yjob_id, @row_number := @row_number+1, 1) AS row_number
              , rank_wo_date + users.rank AS combined_rank

           FROM yjob_userrank AS yjur
           JOIN users AS u ON u.id = yjur.user_id

          WHERE yjob_id IN (29,30 ...) -- only one list required
            AND @job_id = 0            -- not a NULL for correct comparison 
            AND @row_number = 0        -- not a NULL for correct comparison 

          ORDER BY yjob_id ASC
                 , is_match DESC
       ) AS source ON target.id = source.id

 ORDER BY source.yjob_id ASC
        , source.combined_rank DESC 

   SET relative_rank = source.row_number

I'm in doubt is that query is completely equivalent to your one but you can check if it produce the same set of rows if UPDATE is replaced by SELECT and final SET is commented out (just to transform a destructive query into non-destructive form). And you have to use the smaller set of yjob_ids for testing, indeed.

@Oliver Nybroe 2019-04-15 15:43:39

Something looks weird with your query. Your last ORDER BY is part of your UPDATE statement.

@Oliver Nybroe 2019-04-15 15:58:22

So I used some of your methods and simplified the query a bit. However it did not fix my deadlocks. I was not able to remove the inner select join part, as the row number get's messed up then. I Updated my questions to show the new query I created.

@Kondybas 2019-04-15 16:48:21

@OliverNybroe MySQL allows the syntax UPDATE .. ORDER BY in opposite say to the PostgreSQL. We can update rows in the certain order by dynamically calculated values of the user-defined variables.

@Oliver Nybroe 2019-04-16 07:57:14

Ah did not know that. However your query results in a [HY000][1221] Incorrect usage of UPDATE and ORDER BY exception.

@Oliver Nybroe 2019-04-16 08:22:10

In the documentation it says For multiple-table syntax, ORDER BY and LIMIT cannot be used.

@Oliver Nybroe 2019-04-16 08:56:01

I just made a select statement in the order by to solve that error, and it seems like it fixed everything. Do you have a better way of fixing it then an inner query in the order by?

Related Questions

Sponsored Content

2 Answered Questions

Mysql join not working

  • 2015-01-28 00:42:40
  • Rob D
  • 99 View
  • 0 Score
  • 2 Answer
  • Tags:   mysql join

2 Answered Questions

How to improve query count execution with mySql replicate?

1 Answered Questions

Deadlocks appeared after moving to MariaDB10.2 from MySQL5.5

5 Answered Questions

1 Answered Questions

[SOLVED] Replace Subquery with JOIN - MYSQL

2 Answered Questions

[SOLVED] select MAX() from MySQL view (2x INNER JOIN) is slow

  • 2018-02-14 22:34:15
  • matt
  • 386 View
  • 3 Score
  • 2 Answer
  • Tags:   mysql join view max

2 Answered Questions

[SOLVED] #1093 - You can't specify target table 'R' for update in FROM clause

1 Answered Questions

[SOLVED] Need to find the employees who have switched jobs atleast twice

3 Answered Questions

[SOLVED] Inserting and updating and selecting at thousands of times per second

1 Answered Questions

[SOLVED] Unexplained InnoDB timeouts

Sponsored Content