[SOLVED] INSERT ... ON DUPLICATE KEY UPDATE causes Deadlock Exception in Mysql 5.7

INSERT ... ON DUPLICATE KEY UPDATE causes Deadlock Exception in Mysql 5.7

We are having a mysql table like below

CREATE TABLE student_subject (
  student_subject_id int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  subject_id int(11) NOT NULL,
  version int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (student_subject_id),
  UNIQUE KEY student_subject_uniq (student_id, subject_id),
  CONSTRAINT student_subject_fk1 FOREIGN KEY (student_id) REFERENCES student (student_id) ,
  CONSTRAINT student_subject_fk2 FOREIGN KEY (subject_id) REFERENCES subject (subject_id)
)

And using the insert query insert into student_subject (student_id, subject_id) values (101, 201) ON DUPLICATE KEY UPDATE student_id = values(student_id), subject_id = values(subject_id), version = version + 1;

At some point when large number of insert is happening, we are getting deadlock exception. Deadlock log is below.

mysql tables in use 1, locked 1
LOCK WAIT 31 lock struct(s), heap size 3520, 17 row lock(s), undo log entries 16
MySQL thread id 2690963, OS thread handle 47277357205248, query id 147266531743 10.8.83.115 sub update
insert into student_subject (student_id, subject_id) values (101, 201) ON DUPLICATE KEY UPDATE student_id = values(student_id), subject_id = values(subject_id), version = version + 1
2022-06-01T20:57:19.434319Z 2691930 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 8263 page no 28625 n bits 232 index PRIMARY of table `test`.`student_subject` trx id 22794090198 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2022-06-01T20:57:19.434455Z 2691930 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 22794089938, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
28 lock struct(s), heap size 3520, 16 row lock(s), undo log entries 16
MySQL thread id 2691930, OS thread handle 47273083741952, query id 147266531747 10.8.84.91 sub update
insert into student_subject (student_id, subject_id) values (102, 201) ON DUPLICATE KEY UPDATE student_id = values(student_id), subject_id = values(subject_id), version = version + 1
2022-06-01T20:57:19.434505Z 2691930 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 8263 page no 28625 n bits 232 index PRIMARY of table `test`.`student_subject` trx id 22794089938 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2022-06-01T20:57:19.434616Z 2691930 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 8263 page no 28625 n bits 232 index PRIMARY of table `test`.`student_subject` trx id 22794089938 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

2022-06-01T20:57:19.434731Z 2691930 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

We set our isolation mode in mysql to - READ COMMITTED

I am little bit confused because the 2 queries are trying to insert different records, and the second query is having a lock on PRIMARY key of student_subject table and requesting for insert intention lock. I am not sure on why it is happening, any idea ?

Solution