1

Percona Server 5.6.39-83.1-log

Table structure:

CREATE TABLE `myTable` (
    `column_1` VARCHAR(50) NOT NULL DEFAULT '',
    `column_2` VARCHAR(50) NOT NULL DEFAULT '',
    `column_3` VARCHAR(50) NOT NULL DEFAULT '',
    `column_4` VARCHAR(50) NOT NULL DEFAULT '',
    `column_5` VARCHAR(50) NOT NULL DEFAULT '',
    PRIMARY KEY (`column_1`, `column_2`, `column_3`) USING BTREE,
    INDEX `column_3` (`column_3`) USING BTREE,
    INDEX `column_4_5` (`column_4`, `column_5`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

There are two or more concurrent insert leads to deadlock:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-03-23 18:12:45 7f1c08eb8700
*** (1) TRANSACTION:
TRANSACTION 55665288601, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 10275055, OS thread handle 0x7f1c08c6c700, query id 56392293629 localhost user update
INSERT IGNORE INTO myDB.myTable
(`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
('a1', 'a2', 'a3', 'a4', 'a5'),
('b1', 'b2', 'b3', 'b4', 'b5'),
('c1', 'c2', 'c3', 'c4', 'c5'),
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288601 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 55665288598, ACTIVE 0 sec inserting, thread declared inside InnoDB 4965
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 35
MySQL thread id 10275057, OS thread handle 0x7f1c08eb8700, query id 56392293627 localhost user update
INSERT IGNORE INTO myDB.myTable
(`column_1`, `column_2`, `column_3`, `column_4`, `column_5`)
VALUES
('a1', 'a2', 'a3', 'a4', 'a5'),
('b1', 'b2', 'b3', 'b4', 'b5'),
('c1', 'c2', 'c3', 'c4', 'c5')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 337207190 page no 7370 n bits 200 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 337207190 page no 83133 n bits 144 index `PRIMARY` of table `myDB`.`myTable` trx id 55665288598 lock_mode X insert intention waiting
*** WE ROLL BACK TRANSACTION (1)

Why is this happening?
Despite the fact that I do not use auto-increment in the primary key and use IGNORE in query.
How should I avoid this problem?

3
  • 5.6.39 is over 2 years old. Might be worth jumping forward a few releases to gain the benefit of the bug fixes.
    – danblack
    Mar 24, 2020 at 5:08
  • @danblack, Is it bug mysql or not? I think it is not. This looks like described case from the documentaion: If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.
    – faraf27838
    Mar 24, 2020 at 6:56
  • I don't consider this deadlock a bug and it probably still exists in later versions. Just saying there's a number of other fixes. I don't see your documentation reference however .
    – danblack
    Mar 24, 2020 at 7:00

1 Answer 1

0

Both insert statements have the same values for the primary key which is why the deadlock occurred.

At the point of the deadlock, the other transaction could rollback. If the INSERT IGNORE did ignore, and the other transaction rolled back, then the definition of 'INSERT IGNORE' inserting if a record doesn't exist is violated.

This is why the deadlock is returned, because its a business logic problem that needs to be resolved by the application. The action to be taken in a deadlock depends on the situation that cannot be known upfront.

2
  • I understand you. And how I chould resolve this by application? A good option to seem to ignore such errors If MySQL itself will resolve such situations. Or I should use LOCK TABLES WRITE / GET_LOCK global but it can lead to other problems?
    – faraf27838
    Mar 24, 2020 at 7:04
  • You should catch the exception and try again just to be sure. Its only resolvable if the other thread has auto_commit=1 which is the usual case. You should never revert to explicit locking like LOCK TABLES / GET_LOCK because this will remove all concurrency on this table.
    – danblack
    Mar 24, 2020 at 7:10

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.