[MDEV-10962] Deadlock with 3 concurrent DELETEs by unique key - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4.31, 10.5.22, 10.6.15, 10.9.8, 10.10.6, 10.11.5, 11.0.3, 11.1.2, 11.2.1
Component/s: Storage Engine - InnoDB
Labels:
- upstream

Description

As explained in upstream bug reports (that has all the details on the test case):

http://bugs.mysql.com/bug.php?id=82127
https://bugs.launchpad.net/percona-server/+bug/1598822

there is a deadlock scenario with 3 concurrent DELETEs by UNIQUE key that can not be explained by the manual:

CREATE TABLE `tu`(`id` int(11), `a` int(11) DEFAULT NULL, `b` varchar(10) DEFAULT NULL, `c` varchar(10) DEFAULT NULL, PRIMARY KEY(`id`), UNIQUE KEY `u`(`a`,`b`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 STATS_PERSISTENT=0;

insert into tu values(1,1,'a','a'),(2,9999,'xxxx','x'),(3,10000,'b','b'),(4,4,'c','c');

mysqlslap -uroot --concurrency=3 --create-schema=test --no-drop --number-of-queries=1000 --query="delete from tu where a = 9999 and b = 'xxxx'"

mysqlslap: Cannot run query delete from tu where a = 9999 and b = 'xxxx' ERROR : Deadlock found when trying to get lock; try restarting transaction

Deadlock happens both with triggers mentioned in that bug reports and without them (just less often).

The problem was originally noted by customer on MariaDB 5.5.24, but affects all released versions up to those based on InnoDB from 5.7.x for sure.

As there is no visible progress on upstream bugs, I create this bug report for MariaDB to decide if there is anything to fix here or to document clearly in the knowledge base.

Attachments

Issue Links

is blocked by

MDEV-27025 insert-intention lock conflicts with waiting ORDINARY lock

Closed

relates to

MDEV-14589 InnoDB should not lock a delete-marked record

Closed

MDEV-16232 Use fewer mini-transactions

Stalled

MDEV-16402 Support Index Condition Pushdown for clustered PK scans

Confirmed

MDEV-16406 Refactor the InnoDB record locks

Open

MDEV-18706 ER_LOCK_DEADLOCK on concurrent read and insert into already locked gap

In Review

MDEV-31833 replication breaks when using optimistic replication and replica is a galera node

Closed

MDEV-20605 Awaken transaction can miss inserted by other transaction records due to wrong persistent cursor restoration

Closed

MDEV-22698 Deadlock on concurrent acquisition from multiple indexes

Open

MDEV-23560 Deadlock detected on SELECT when only one record being processed

Open

MDEV-28800 SIGABRT due to running out of memory for InnoDB locks

Closed

links to

Bug #82127 Deadlock with 3 concurrent DELETEs by UNIQUE key

lp:1598822 Deadlock with 3 concurrent DELETEs by unique key and triggers reading the same row

mentioned in: Page Loading...

(6 relates to, 2 links to, 1 mentioned in)

Activity

Ascending order - Click to sort in descending order

Valerii Kravchuk created issue - 2016-10-06 07:10

Elena Stepanova made changes - 2016-10-06 13:08

Field	Original Value	New Value
Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]

Sergei Golubchik made changes - 2016-10-10 18:11

Remote Link

This issue links to "Bug #82127 Deadlock with 3 concurrent DELETEs by UNIQUE key (Web Link)" [ 27622 ]

Sergei Golubchik made changes - 2016-10-10 18:12

Remote Link

This issue links to "lp:1598822 Deadlock with 3 concurrent DELETEs by unique key and triggers reading the same row (Web Link)" [ 27623 ]

Jan Lindström (Inactive) made changes - 2016-11-01 09:44

Assignee

Jan Lindström [ jplindst ]

Sergei Golubchik made changes - 2016-11-01 19:37

Fix Version/s		5.5 [ 15800 ]
Fix Version/s		10.0 [ 16000 ]

Sergei Golubchik made changes - 2016-11-01 19:37

Fix Version/s

10.1 [ 16100 ]

Sergei Golubchik made changes - 2016-11-01 19:46

Priority

Major [ 3 ]

Critical [ 2 ]

Jan Lindström (Inactive) made changes - 2016-12-01 10:58

Fix Version/s		N/A [ 14700 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Not a Bug [ 6 ]
Status	Open [ 1 ]	Closed [ 6 ]

Marko Mäkelä made changes - 2018-06-05 10:33

Link

This issue relates to MDEV-16232 [ MDEV-16232 ]

Marko Mäkelä made changes - 2018-06-05 10:33

Link

This issue relates to ~~MDEV-14589~~ [ ~~MDEV-14589~~ ]

Marko Mäkelä made changes - 2018-06-05 10:33

Link

This issue relates to MDEV-16402 [ MDEV-16402 ]

Marko Mäkelä made changes - 2018-06-05 10:34

Assignee	Jan Lindström [ jplindst ]	Marko Mäkelä [ marko ]
Resolution	Not a Bug [ 6 ]
Status	Closed [ 6 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2018-06-05 12:21

Link

This issue relates to MDEV-16406 [ MDEV-16406 ]

Marko Mäkelä made changes - 2018-06-13 14:39

Affects Version/s		10.2 [ 14601 ]
Affects Version/s		10.3 [ 22126 ]

Marko Mäkelä made changes - 2018-06-13 14:39

Fix Version/s		10.4 [ 22408 ]
Fix Version/s	N/A [ 14700 ]

Marko Mäkelä made changes - 2019-03-28 11:29

Fix Version/s	10.4 [ 22408 ]
NRE Projects		RM_long_term
Affects Version/s		10.4 [ 22408 ]
Priority	Critical [ 2 ]	Major [ 3 ]

Sergei Golubchik made changes - 2019-04-01 18:59

Fix Version/s

10.4 [ 22408 ]

Marko Mäkelä made changes - 2019-09-11 06:35

Link

This issue relates to MDEV-18706 [ MDEV-18706 ]

Marko Mäkelä made changes - 2020-04-21 07:37

Link

This issue relates to ~~MDEV-20605~~ [ ~~MDEV-20605~~ ]

Aleksey Midenkov made changes - 2020-05-10 10:00

Assignee

Marko Mäkelä [ marko ]

Aleksey Midenkov [ midenok ]

Aleksey Midenkov made changes - 2020-05-20 13:52

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2020-05-25 14:46

Link

This issue relates to MDEV-22698 [ MDEV-22698 ]

Aleksey Midenkov made changes - 2020-05-25 18:16

Assignee	Aleksey Midenkov [ midenok ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2020-08-12 12:17

Assignee	Marko Mäkelä [ marko ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Marko Mäkelä made changes - 2020-08-24 14:43

Link

This issue relates to MDEV-23560 [ MDEV-23560 ]

Aleksey Midenkov made changes - 2020-08-25 09:50

Assignee	Aleksey Midenkov [ midenok ]	Marko Mäkelä [ marko ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2020-11-05 15:59

Assignee	Marko Mäkelä [ marko ]	Aleksey Midenkov [ midenok ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Aleksey Midenkov made changes - 2021-06-22 10:36

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Aleksey Midenkov made changes - 2021-08-27 08:01

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2021-12-06 21:35

Workflow

MariaDB v3 [ 77682 ]

MariaDB v4 [ 143490 ]

Marko Mäkelä made changes - 2022-01-04 09:54

Link

This issue is blocked by ~~MDEV-27025~~ [ ~~MDEV-27025~~ ]

Aleksey Midenkov made changes - 2023-04-14 15:34

Assignee

Aleksey Midenkov [ midenok ]

Marko Mäkelä [ marko ]

Marko Mäkelä made changes - 2023-05-09 13:41

Assignee

Marko Mäkelä [ marko ]

Vladislav Lesin [ vlad.lesin ]

Julien Fritsch made changes - 2023-05-17 07:25

Priority

Major [ 3 ]

Critical [ 2 ]

Vladislav Lesin made changes - 2023-06-26 10:28

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Julius Goryavsky made changes - 2023-06-27 09:59

Link

This issue relates to ~~MENT-1815~~ [ ~~MENT-1815~~ ]

Vladislav Lesin made changes - 2023-07-05 08:00

Assignee	Vladislav Lesin [ vlad.lesin ]	Marko Mäkelä [ marko ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Marko Mäkelä made changes - 2023-07-05 10:34

Link

This issue relates to ~~MDEV-28800~~ [ ~~MDEV-28800~~ ]

Marko Mäkelä made changes - 2023-07-05 14:23

Assignee	Marko Mäkelä [ marko ]	Vladislav Lesin [ vlad.lesin ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Vladislav Lesin made changes - 2023-07-06 16:04

Fix Version/s		10.4.31 [ 29010 ]
Fix Version/s		10.5.22 [ 29011 ]
Fix Version/s		10.6.15 [ 29013 ]
Fix Version/s		10.9.8 [ 29015 ]
Fix Version/s		10.10.6 [ 29017 ]
Fix Version/s		10.11.5 [ 29019 ]
Fix Version/s		11.0.3 [ 28920 ]
Fix Version/s		11.1.2 [ 28921 ]
Fix Version/s		11.2.1 [ 29034 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Rob Schwyzer (Inactive) made changes - 2023-07-20 18:33

Remote Link

This issue links to "Page (MariaDB Confluence)" [ 35637 ]

Jira Automation (IT) made changes - 2024-07-04 08:35

Zendesk Related Tickets		201658
Zendesk active tickets		201658

People

Assignee:: Vladislav Lesin

Reporter:: Valerii Kravchuk

Votes:: 0 Vote for this issue

Watchers:: 12 Start watching this issue

Dates

Created:: 2016-10-06 07:10

Updated:: 2024-09-06 09:59

Resolved:: 2023-07-06 16:04

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration