Mysql – Deadlock found when trying to get lock; try restarting transaction in thesql

deadlockforeign keyinnodbMySQLunique-constraint

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)

Best Answer

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.

Related Solutions

Mysql – Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

Mysql – jdbc+thesql: “Deadlock found when trying to get lock; try restarting transaction”

Looking at the Stored Procedure, I see something rather unnatural.

DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

The thing I see that is unnatural is the DELETE ... JOIN query that is mixing an InnoDB table and a MyISAM table. Such JOINs tend to be rather clumsy to mysqld.

Let's make the pixel_window table. InnoDB. In fact, let's not make it a temporary table. Of course, the hard part is to create a unique pixel_window table for the session. The pixel_window temp table will be the prefix 'pixel_window_' with the Connection ID appended.

DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  SET @pwtable = CONCAT('pixel_window_',CONNECTION_ID());

  #
  # Create the pixel_window Table
  #
  SET @sqlstmt = CONCAT('CREATE TABLE ',pwtable,' (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  #
  # Load the pixel_window Table
  #
  SET @sqlstmt= CONCAT('INSERT INTO ',@pwtable,' ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);

  #
  # Use the pixel_window Table to perform DELETE...JOIN
  #
  SET @sqlstmt = CONCAT('DELETE A.* FROM pixels A LEFT JOIN ',
  @pwtable,' B USING (id) WHERE A.type=GivenType AND B.id IS NULL');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;

  #
  # Drop the pixel_window Table
  #
  SET @sqlstmt = CONCAT('DROP TABLE IF EXISTS ',@pwtable);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

END $$

DELIMITER ;

Give it a Try !!!

CAVEAT : Since I created this monster, I needed to slay it as well.

Best Answer

Related Solutions

Mysql – Cannot update certain rows in innodb tables

Mysql – jdbc+thesql: “Deadlock found when trying to get lock; try restarting transaction”

CAVEAT : Since I created this monster, I needed to slay it as well.

Related Question