1

Recently we migrated our production DB to Amazon RDS with version upgrade from 5.5 to 5.7 using AWS DMS service. After that, we are frequently getting deadlock issues for our insert...on duplicate key update queries and update queries. Whereas in MySQL 5.5 it was very minimal.

For example, say one of our table structure is as follows.

CREATE TABLE `job_notification` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `created_time` int(11) NOT NULL,
  `updated_time` int(11) NOT NULL,
  `notify_status` tinyint(3) DEFAULT '0'
  PRIMARY KEY (`id`),
  UNIQUE KEY `uid` (`uid`,`job_id`),
) ENGINE=InnoDB AUTO_INCREMENT=58303732 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

Our insert query is as follows...

    INSERT INTO job_notification (uid, notify_status, updated_time, created_time, job_id) VALUES
('24832194',1,1571900253,1571900253,'734749'),
('24832194',1,1571900254,1571900254,'729161'),
('24832194',1,1571900255,1571900255,'713225'),
('24832194',1,1571900256,1571900256,'701897'),
('24832194',1,1571900257,1571900257,'682155'),
('24832194',1,1571900258,1571900258,'730817'),
('24832194',1,1571900259,1571900259,'717162'),
('24832194',1,1571900260,1571900260,'712884'),
('24832194',1,1571900261,1571900261,'708267'),
('24832194',1,1571900262,1571900262,'701855'),
('24832194',1,1571900263,1571900263,'702129'),
('24832194',1,1571900264,1571900264,'726738'),
('24832194',1,1571900265,1571900265,'725105'),
('24832194',1,1571900266,1571900266,'709306'),
('24832194',1,1571900267,1571900267,'702218'),
('24832194',1,1571900268,1571900268,'700966'),
('24832194',1,1571900269,1571900269,'693848'),
('24832194',1,1571900270,1571900270,'730793'),
('24832194',1,1571900271,1571900271,'729352'),
('24832194',1,1571900272,1571900272,'729043'),
('24832194',1,1571900273,1571900273,'724631'),
('24832194',1,1571900274,1571900274,'718394'),
('24832194',1,1571900275,1571900275,'711702'),
('24832194',1,1571900276,1571900276,'707765'),
('24832194',1,1571900277,1571900277,'692288'),
('24832194',1,1571900278,1571900278,'735549'),
('24832194',1,1571900279,1571900279,'730786'),
('24832194',1,1571900280,1571900280,'706814'),
('24832194',1,1571900281,1571900281,'688999'),
('24832194',1,1571900282,1571900282,'685079'),
('24832194',1,1571900283,1571900283,'686661'),
('24832194',1,1571900284,1571900284,'722110'),
('24832194',1,1571900285,1571900285,'715277'),
('24832194',1,1571900286,1571900286,'701846'),
('24832194',1,1571900287,1571900287,'730105'),
('24832194',1,1571900288,1571900288,'725579')
 ON DUPLICATE KEY UPDATE notify_status=VALUES(notify_status), updated_time=VALUES(updated_time)

Our update query is as follows...

update job_notification set notify_status = 3 where uid = 51032194 and job_id in (616661, 656221, 386760, 189461, 944509, 591552, 154153, 538703, 971923, 125080, 722110, 715277, 701846, 725579, 686661, 685079)

These queries were working fine in MySQL 5.5 with the same packet size of data and index, but after the migration deadlocks are frequently coming for this type of queries...

NB: Ours is a high-level concurrent system. innodb_deadlock_detect is disabled. innodb_lock_wait_timeout is 50.

innodb_buffer_pool_size is 50465865728

When we explained the queries it gave a better execution plan. Still, we are getting frequent deadlocks and because of this other queries also getting slowed.

Both queries are executed as different API threads (Different connections) using pythons Mysqldb package with autocommit enabled in the MySQL DB.

Explain Output

explain update job_notification SET notify_status = 3 where uid = 51032194 and job_id in (616661, 656221, 386760, 189461, 944509, 591552, 154153, 538703, 971923, 125080, 722110, 715277, 701846, 725579, 686661, 685079);
+----+--------+------------+------------+-------+---------------+------+-----+-------+------+----------+--------+
| id | select_type | table                    | partitions | type  | possible_keys | key  | key_len | ref         | rows | filtered |Extra       |
+----+----------+------------+------------+-------+---------------+------+---------+-------------+------+----------+----------+
|  1 | UPDATE      | job_notification | NULL       | range | uid           | uid  | 8       | const,const |   27 |   100.00 | Using where |
+----+-------------+--------------------------+------------+-------+---------------+------+---------+-------------+--------+-------------+
Akhil Mathew
  • 1,571
  • 3
  • 34
  • 69
  • Did you try to add transaction to every update/insert sequence? It not only may solve your problem, but also is highly recommended for those operations to keep data consistent. – kmnowak Oct 30 '19 at 09:29
  • 1
    @KrzysztofNowak. We are using python's MySQLdb package for query execution and not using any transaction – Akhil Mathew Oct 30 '19 at 10:40
  • I see, but did you try to add them? – kmnowak Oct 30 '19 at 14:03
  • 1
    @KrzysztofNowak. I didn't try it. I will try it and let you know. – Akhil Mathew Oct 30 '19 at 14:13
  • @AKHILMATHEW What is the reason for the quotes around the data for uid and job_id ? INSERT INTO job_notification (uid, notify_status, updated_time, created_time, job_id) VALUES ('24832194',1,1571900253,1571900253,'734749'), ('24832194',1,1571900254,1571900254,'729161'), ? This would seem to cause unnecessary string to int conversion prior to each insert. – Wilson Hauck Oct 30 '19 at 19:13
  • 1
    @KrzysztofNowak. If the above queries are of different connections from different API threads, adding transaction will work? – Akhil Mathew Oct 31 '19 at 12:09
  • Yes, the transaction mechanism (when it's isolation lvl is at least repeatable read) keep your data in DB consistent and blocks access to row when is being modified preventing deadlocks. More info about transactions can be found here: http://zetcode.com/databases/mysqltutorial/transactions/ I assume that by python you are using some ORM (if not it's strongly recommended) and it should have transaction implementation. – kmnowak Nov 01 '19 at 13:20
  • And be sure to use `FOR UPDATE` if you have a supporting `SELECT` in a transaction. – Rick James Nov 04 '19 at 21:03
  • @AKHILMATHEW Additional information request. Post on pastebin.com and share the links. RAM size, # cores of your MySQL Host server From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report F) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. – Wilson Hauck Nov 05 '19 at 16:47
  • Please post the TEXT results of EXPLAIN UPDATE job_notification SET .... so we can see what the optimizer decided to do with the query. – Wilson Hauck Nov 05 '19 at 16:54
  • @KrzysztoNowak I tried implementing the transaction, but the queries are still coming to deadlock. – Akhil Mathew Nov 06 '19 at 05:46
  • @WilsonHauck Updated the question with explain output. Kindly check – Akhil Mathew Nov 06 '19 at 06:27

1 Answers1

0

If this is primarily a many-to-many mapping table, get rid if id and follow the rest of the advice in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

That will make queries run faster on both systems. Faster = less chance of deadlock.

Let's see the deadlock; there could be other things going on. Use SHOW ENGINE INNODB STATUS; promptly after a deadlock occurs.

Rick James
  • 135,179
  • 13
  • 127
  • 222