2

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.

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.

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 |
+----+-------------+--------------------------+------------+-------+---------------+------+---------+-------------+--------+-------------+
2

The only way to avoid such deadlocks is to make sure that all data modifying statements process the rows in the same order, e.g. in ORDER BY uid, job_id order.

With the INSERT statements that can be easily done, but with the UPDATE statements it depends on the execution plan the database uses. Perhaps you can do something by using certain indexes and ordering the IN list; you will have to experiment.

If there is no way to control the update order, your only hope is to reduce the batch size.

  • Thanks for the quick reply. We will try this and get back to you soon – AKHIL MATHEW Oct 25 '19 at 7:53
  • We tried ordering our input data for the insert as well as the update query, unfortunately we didn't got any performance improvement from it. Queries are frequently coming to deadlock. – AKHIL MATHEW Oct 28 '19 at 13:58
  • Did you use EXPLAIN to verify that the execution plans process the rows in the correct order? – Laurenz Albe Oct 28 '19 at 14:07
  • Explain doesn't give that information I guess. Moreover insert query can't be explained properly. I just used explain at the starting of the query. If I am missing out something, kindly guide me – AKHIL MATHEW Oct 28 '19 at 14:22
2

This nice post explains it well.

The deadlocks occur due to gap locking done by mysql. There are several reasons for gap locking, and in this particular case, it has to do with preserving a unique key constraint on an index. The situation presents itself to us this way: There is a unique key constraint on a column and we are doing an insert. Mysql has to make sure that the lock it takes is sufficient to prevent another concurrent insert from adding a record with the same key, thus breaking the unique key constraint.

More about the gap lock here.

Gap locking is not needed for statements that lock rows using a unique index to search for a unique row

These Q&As may help you,

https://stackoverflow.com/a/32502736

https://dba.stackexchange.com/a/87004/194233

https://dba.stackexchange.com/a/203133/194233

You may can try to use single statement vs batch insert. Also removing the auto-increment and use the concatenated value as primary.

  • INSERT with ON DUPLICATE KEY lock works differently than the usual INSERT statement. (dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html) You may also want to change that logic, if unsure about the data, may can try REPLACE INTO. – j-bin Nov 4 '19 at 20:48
  • We can't do REPLACE INTO on a direct way as partial update involved. If we wanna do then we need to get the existing data in the form of a select query and then proceed. – AKHIL MATHEW Nov 6 '19 at 6:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.