Mysql InnoDB Deadlock on INSERT IGNORE

29 views Asked by At

I have a table with the following structure -

CREATE TABLE `some_table` (
  `id` varchar(38) NOT NULL,
  `name` varchar(256) NOT NULL,
  `creation_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `update_time` timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In my application, I first open a transaction before inserting to this table because after the insert to this table I have to insert to another one. When I have 2 threads, 2 transactions are created and sometime they try to insert the same set of names to this table (however with different ids cause it's GUID). Each one of the transactions tries to insert its set of values one by one. Also, each transaction sorts the values to be inserted by name, so basically the 2 transaction should try to insert the same names with the same order

In that situation I want 1 of the transactions to be successful and the other one to not do anything so I used INSERT IGNORE -

INSERT IGNORE INTO some_table
           (
             id,
             name
           )
           values (
             :id,
             :name
           )

Unfortunately, I get deadlocks when 2 threads try to insert the same set of names to this table.

Anyone has an idea how to solve this or why this happens? In InnoDB logs I saw it's related to gap locks but I couldn't figure out more.

0

There are 0 answers