MySQL Bugs: #116430: Concurrent DELETE statements of different rows causing deadlocking

Bug #116430	Concurrent DELETE statements of different rows causing deadlocking
Submitted:	21 Oct 2024 3:32	Modified:	22 Oct 2024 2:32
Reporter:	Kir Shatrov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.34, 8.0.40	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	deadlock select for update

Description:
This is InnoDB-specific issue.

I'm running transactions that are specifically optimized for concurrency.

Those transactions pull N rows from a table using `FOR UPDATE SKIP LOCKED` and then DELETE those rows that it has set the lock on.

This works fine when N is <100, but as I start to increase the batch size up to 2000 and above, the chance of getting a deadlock gets very high.

I understand that decreasing batch size of rows processed per transaction is the suggested fix; however for the business logic of my schema it's required that we operate with 1000+ rows.

Since this shape of transaction is specifically designed to not lock on the same rows, I strongly believe it should be possible to handle that at the InnoDB level to avoid the deadlock.

How to repeat:
Steps to reproduce:

```
CREATE TABLE `pool_of_tickets` (
 `id` bigint NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
```

A concurrently running transaction:

```
BEGIN;

SELECT id
FROM pool_of_tickets
ORDER BY id
LIMIT 2000
FOR UPDATE SKIP LOCKED;

DELETE FROM pool_of_tickets
WHERE id IN (<set of ids from above>);

COMMIT;
```

Run the transaction with concurrency of at least 20.

full deadlock dump

Attachment: pool_of_tickets_deadlock.txt (text/plain), 7.39 KiB.

Deadlock example:

```
TRANSACTION 37751313, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 11 lock struct(s), heap size 1128, 3001 row lock(s), undo log entries 3000
MySQL thread id 265, OS thread handle 140177333868096, query id 16853 172.17.0.1 root updating
DELETE FROM pool_of_tickets
      WHERE id IN (24054089,24054090,24054091,24054092,24054093,24054094,24054095,24054096,24054097,24054098,24054099,24054100,24054101,24054102,24054103,24054104,24054105,24054106,24054107,24054108,24054109,24054110,24054111,24054112,24054113,24054114,24054115,24054116,24054117,24054118,24054119,24054120,24054121,24054122,24054123,24054124,24054125,24054126,24054127,24054128,24054129,24054130,24054131,24054132,24054133,24054134,24054135,24054136,24054137,24054138,24054139,24054140,24054141,24054142,24054143,24054144,24054145,24054146,24054147,24054148,24054149,24054150,24054151,24054152,24054153,24054154,24054155,24054156,24054157,24054158,24054159,24054160,24054161,24054162,24054163,24054164,24054165,24054166,24054167,24054168,24054169,24054170,24054171,24054172,24054173,24054174,24054175,24054176,24054177,24054178,24054179,24054180,24054181,24054182,24054183,24054184,24054185,24054186,24054187,24054188,24054189,24054190,24054191,24054192,24054193,24054194,24054195,24054196,24054197,24054198,24054199,24054200,24054201,24054202,24054203,24054204,24054205,24054206,24054207,24054208,24054209,24054210,24054211,24054212,24054213,24054214,24054215,24054216,24054217,24054218,24054219,24054220,24054221,24054222,24054223,24054224,24054225,24054226,24054227,24054228,24054229,24054230,24054231,24054232,24054233,24054234,24054235,24054236,24054237,24054238,24054239,24054240,24054241,24054242,24054243,24054244,24054245,24054246,24054247,24054248,24054249,24054250,24054251,24054252,24054253,24054254,24054255,24054256,24054257,24054258,24054259,24054260,24054261,24054262,24054263,24054264,24054265,24054266,24054267,24054268,24054269,24054270,24054271,24054272,24054273,24054274,24054275,24054276,24054277,24054278,24054279,24054280,24054281,24054282,24054283,24054284,24054285,24054286,24054287,24054288,24054289,24054290,24054291,24054292,24054293,24054294,24054295,24054296,24054297,24054298,24054299,24054300,24054301,24054302,24054303,24054304,24054305,24054306,24054307,24054308,24054309,24054310,24054311,24054312,24054313,24054314,24054315,24054316,24054317,24054318,24054319,24054320,24054321,24054322,24054323,24054324,24054325,24054326,24054327,24054328,24054329,24054330,24054331,24054332,24054333,24054334,24054335,24054336,24054337,24054338,24054339,24054340,24054341,24054342,24054343,24054344,24054345,24054346,24054347,24054348,24054349,24054350,24054351,24054352,24054353,24054354,24054355,24054356,24054357,24054358,24054359,24054360,24054361,24054362,24054363,24054364,24054365,24054366,24054367,24054368,24054369,24054370,24054371,24054372,24054373,24054374,24054375,24054376,24054377,24054378,24054379,24054380,24054381,24054382,24054383,24054384,24054385,24054386,24054387,24054388,24054389,24054390,24054391,24054392,24054393,24054394,24054395,24054396,24054397,24054398,24054399,24054400,24054401,24054402,24054403,24054404,24054405,24054406,24054407,24054408,24054409,24054410,24054411,24054412,2405
2024-10-20T02:00:27.415259Z 0 [Note] [MY-012469] [InnoDB]  *** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 20 page no 6 n bits 432 index PRIMARY of table `testdb`.`pool_of_tickets` trx id 37751313 lock_mode X locks rec but not gap

<...>

2024-10-20T02:00:27.427319Z 0 [Note] [MY-012469] [InnoDB]  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 20 page no 19 n bits 432 index PRIMARY of table `testdb`.`pool_of_tickets` trx id 37751313 lock_mode X locks rec but not gap waiting
Record lock, heap no 317 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 8; hex 80000000016f1501; asc      o  ;;
 1: len 6; hex 0000024009f1; asc    @  ;;
 2: len 7; hex 8100001f63149c; asc     c  ;;
 3: len 8; hex 8000000000000001; asc         ;;
 4: len 8; hex 8000000000f7c362; asc        b;;

2024-10-20T02:00:27.427423Z 0 [Note] [MY-012469] [InnoDB]  *** (2) TRANSACTION:
TRANSACTION 37751314, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 29 lock struct(s), heap size 3488, 3001 row lock(s)
MySQL thread id 266, OS thread handle 140177065248320, query id 16877 172.17.0.1 root updating
DELETE FROM pool_of_tickets
      WHERE id IN (24057089,24057090,24057091,24057092,24057093,24057094,24057095,24057096,24057097,24057098,24057099,24057100,24057101,24057102,24057103,24057104,24057105,24057106,24057107,24057108,24057109,24057110,24057111,24057112,24057113,24057115,24057116,24057117,24057118,24057119,24057151,24057153,24057156,24057165,24057166,24057167,24057187,24057188,24057189,24057191,24057193,24057194,24057195,24057199,24057200,24057201,24057202,24057203,24057204,24057205,24057206,24057207,24057208,24057209,24057210,24057211,24057212,24057213,24057214,24057215,24057216,24057217,24057218,24057219,24057220,24057221,24057222,24057224,24057226,24057227,24057228,24057229,24057230,24057231,24057233,24057234,24057235,24057242,24057244,24057246,24057247,24057248,24057249,24057250,24057251,24057252,24057253,24057254,24057258,24057266,24057268,24057269,24057274,24057275,24057276,24057277,24057278,24057279,24057280,24057290,24057292,24057293,24057294,24057295,24057296,24057297,24057308,24057310,24057311,24057312,24057314,24057315,24057316,24057324,24057326,24057327,24057328,24057330,24057341,24057343,24057345,24057346,24057347,24057384,24057386,24057388,24057390,24057391,24057393,24057458,24057460,24057462,24057463,24057465,24057468,24057469,24057470,24057471,24057472,24057473,24057474,24057475,24057476,24057477,24057478,24057479,24057484,24057486,24057487,24057488,24057489,24057490,24057491,24057492,24057493,24057494,24057495,24057496,24057497,24057498,24057499,24057500,24057501,24057502,24057503,24057504,24057505,24057506,24057507,24057508,24057509,24057510,24057511,24057512,24057513,24057514,24057515,24057516,24057517,24057518,24057519,24057520,24057521,24057522,24057523,24057524,24057525,24057526,24057527,24057528,24057529,24057530,24057531,24057532,24057533,24057534,24057535,24057536,24057537,24057538,24057539,24057540,24057541,24057542,24057563,24057565,24057567,24057596,24057605,24057607,24057608,24057610,24057613,24057615,24057617,24057619,24057620,24057622,24057624,24057625,24057626,24057627,24057628,24057629,24057637,24057641,24057642,24057643,24057644,24057645,24057646,24057647,24057649,24057650,24057651,24057652,24057653,24057654,24057655,24057657,24057659,24057661,24057663,24057664,24057665,24057666,24057667,24057668,24057669,24057670,24057671,24057673,24057674,24057676,24057678,24057682,24057684,24057686,24057688,24057690,24057692,24057694,24057696,24057698,24057700,24057702,24057704,24057706,24057708,24057710,24057712,24057714,24057716,24057718,24057719,24057720,24057721,24057722,24057723,24057724,24057725,24057726,24057727,24057728,24057729,24057730,24057731,24057732,24057733,24057734,24057735,24057736,24057737,24057738,24057739,24057740,24057741,24057742,24057743,24057744,24057746,24057747,24057748,24057749,24057750,24057751,24057752,24057753,24057755,24057757,24057758,24057759,24057760,24057761,24057762,24057763,24057764,24057765,24057766,24057767,24057768,24057769,24057770,24057771,2405
2024-10-20T02:00:27.427470Z 0 [Note] [MY-012469] [InnoDB]  *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 20 page no 19 n bits 432 index PRIMARY of table `testdb`.`pool_of_tickets` trx id 37751314 lock_mode X locks rec but not gap
<...>
```

Hi Mr. Shatrov,

Thank you for your bug report.

However, this is not a bug. Deadlocks are  a proof that InnoDB Storage Engine works correctly.

One of the great features of properly functioning transactional engine is to discover a deadlock. This is all explained in our Reference Manual, chapter on InnoDB SE.

Not a bug.

Thanks for your reply.

Given these transactions operate on completely different sets of rows, shouldn't there be a way to optimize InnoDB to better handle this condition?

Thanks to Richard Burnison who pointed me out into the right direction, it seems like the cause of the issue is https://bugs.mysql.com/bug.php?id=112523

Experiment with the following variables and check what impact it has?

set global eq_range_index_dive_limit=10000;
set global range_optimizer_max_mem_size=1024*1024*1024;

-- 
Shane, MySQL Senior Principal Technical Support Engineer
Oracle Corporation
http://dev.mysql.com/

Hi Mr. Shatrov,

The bug record that you are mentioning is not a bug. That is expected behaviour.

There are many queries and sizes of tables where it is much faster to scan rows than to search by index. You could try using FORCE INDEX, as explained in Reference Manual, but that does not guarantee that deadlocks will not occur.