7

I have a table structure as given below, this table is having 1 million records and having FK relationship with two and three tables having records also in millions. I need to do bulk insert into this table but I am getting error Deadlock found when trying to get lock; try restarting transaction, not able to find what thing is creating problem ?

CREATE TABLE tags (
 id int(10) unsigned NOT NULL AUTO_INCREMENT,
  tag varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY hashtag (tag)
 ) ENGINE=InnoDB AUTO_INCREMENT=657163 DEFAULT CHARSET=utf8mb4      COLLATE=utf8mb4_unicode_ci`

Query I am using to do bulk insert is

 INSERT INTO tags (tag) VALUES ('jatin'),('test') ON DUPLICATE KEY UPDATE tag = VALUES(tag);`

I ran command show engine innodb status and result is shown below

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-11-11 06:05:41 7fc9de391700
*** (1) TRANSACTION:
TRANSACTION 407031415, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 12 lock struct(s), heap size 2936, 11 row lock(s), undo log entries 1
MySQL thread id 6539, OS thread handle 0x7fc9de734700, query id 20689564 localhost 127.0.0.1 campaygn3 update
INSERT INTO tags (tag) VALUES ('paris'), ('pfw'), ('streetstyle'), ('trendycrew'), ('ootd'), ('outfit'), ('style'), ('fashion'), ('vsco'), ('vscoparis'), ('vscostyle') ON DUPLICATE KEY UPDATE tag = VALUES(tag)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 569 page no 5 n bits 440 index PRIMARY of table campaygn3.tags trx id 407031415 lock_mode X locks rec but not gap waiting
Record lock, heap no 77 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 0000004c; asc    L;;
1: len 6; hex 000018412a14; asc    A* ;;
2: len 7; hex 0f00002ea721e1; asc    . ! ;;
3: len 4; hex 6f6f7464; asc ootd;;
4: len 4; hex 56bdd515; asc V   ;;
5: len 4; hex 58253588; asc X%5 ;;

*** (2) TRANSACTION:
TRANSACTION 407031425, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
31 lock struct(s), heap size 6544, 49 row lock(s)
MySQL thread id 6521, OS thread handle 0x7fc9de391700, query id 20689580 localhost 127.0.0.1 campaygn3 update
INSERT INTO facebook_hash_tags_posts (tag_id,post_id) VALUES (477682, '1158013510912722'), (16905, '1158013510912722'), (3134, '1158013510912722'), (1136, '1158013510912722'), (13671, '1158013510912722'), (150, '1158013510912722'), (429, '1158013510912722'), (297, '1158013510912722'), (1137, '1158013510912722'), (3130, '1158013510912722'), (1062, '1158013510912722'), (234, '1158013510912722'), (327, '1158013510912722'), (944, '1158013510912722'), (482, '1158013510912722'), (580014, '1158013510912722'), (580013, '1158013510912722'), (1996, '1158013510912722'), (76, '1158013510912722'), (333, '1158013510912722'), (485, '1158013510912722'), (578, '1158013510912722'), (329, '1158013510912722'), (53, '1158013510912722'), (470, '1158013510912722'), (161, '1158013510912722'), (477, '1158013510912722'), (335, '1158013510912722') ON DUPLICATE KEY UPDATE tag_id = VALUES(tag_id), post_id = VALUES(post_id)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 569 page no 5 n bits 440 index PRIMARY of table campaygn3.tags trx id 407031425 lock mode S locks rec but not gap
Record lock, heap no 54 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 00000035; asc    5;;
1: len 6; hex 000017d709c1; asc       ;;
2: len 7; hex 6800001e9111ea; asc h      ;;
3: len 11; hex 7069636f66746865646179; asc picoftheday;;
4: len 4; hex 56bdd514; asc V   ;;
5: len 4; hex 58200b0a; asc X   ;;

Record lock, heap no 77 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 0000004c; asc    L;;
1: len 6; hex 000018412a14; asc    A* ;;
2: len 7; hex 0f00002ea721e1; asc    . ! ;;
3: len 4; hex 6f6f7464; asc ootd;;
4: len 4; hex 56bdd515; asc V   ;;
5: len 4; hex 58253588; asc X%5 ;;

Record lock, heap no 151 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 00000096; asc     ;;
1: len 6; hex 00001842c82c; asc    B ,;;
2: len 7; hex 570000747926e1; asc W  ty& ;;
3: len 7; hex 66617368696f6e; asc fashion;;
4: len 4; hex 56bdd515; asc V   ;;
5: len 4; hex 582551a1; asc X%Q ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 569 page no 6 n bits 440 index PRIMARY of table campaygn3.tags trx id 407031425 lock mode S locks rec but not gap waiting
Record lock, heap no 289 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 000001d6; asc     ;;
1: len 6; hex 00001842c989; asc    B  ;;
2: len 7; hex 4700006bb902cf; asc G  k   ;;
3: len 11; hex 7374726565747374796c65; asc streetstyle;;
4: len 4; hex 56bdd51a; asc V   ;;
5: len 4; hex 582551a2; asc X%Q ;;

*** WE ROLL BACK TRANSACTION (1)
2
  • insertion usually happens fast, it seem some table level issue..ca you do optimize table and check? – Nawaz Sohail Nov 11 '16 at 10:12
  • I tried it, did not work for me – Jatin Seth Nov 11 '16 at 11:05
5
  • Instead of IODKU, use INSERT IGNORE. Both will burn lots of AUTO_INCREMENT ids, but the latter is probably less prone to deadlock.
  • Sort the ids. This, in many cases, turns a deadlock into a delay.
  • Be prepared to replay when you do get a deadlock; it is not the end of the world.
  • Review High speed ingestion to see if it has techniques applicable to your situation.

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.