patternsqlMinor
Deadlock found when trying to get lock; try restarting transaction in mysql
Viewed 0 times
deadlocktryingfoundgetmysqltransactionwhenrestartingtrylock
Problem
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
Query I am using to do bulk insert is
I ran command
```
------------------------
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
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
Solution
- Instead of IODKU, use
INSERT IGNORE. Both will burn lots ofAUTO_INCREMENTids, 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.
Context
StackExchange Database Administrators Q#154821, answer score: 5
Revisions (0)
No revisions yet.