Deadlock caused by insert statements in MySQL 8.0

Question

I have a table A which has a schema like this,

CREATE TABLE `A` (
  `id` char(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `t_id` char(14) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `p_id` char(14) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `percentage` int DEFAULT NULL,
  `amount` bigint NOT NULL,
  `created_at` int NOT NULL,
  `updated_at` int NOT NULL,
  PRIMARY KEY (`id`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

We also have partitions on this table based on created_at column every 24 hours.

When I run this script in production, it gives me deadlocks while inserting records into A. Also, the underlying ID generation logic while insertion guarantees no collision whatsoever.

Script

BEGIN TRANSACTION;

SAVEPOINT X1

insert into `A` (`name`, `percentage`, `amount`, `p_id`, `t_id`, `id`, `updated_at`, `created_at`) values ('sample', NULL, 500, 'Bbg7cl6t6I3XA6', 'Mhrg36yTo8XdTz', 'MhDXr5xlF4a9a1', 1695786632, 1695786632)

insert into `A` (`name`, `percentage`, `amount`, `p_id`, `t_id`, `id`, `updated_at`, `created_at`) values ('sample', NULL, 500, 'Bbg7cl6t6I3XA6', 'Mhrg36yTo8XdTz', 'MhDXr62GtPB6hH', 1695786632, 1695786632)

ROLLBACK TO SAVEPOINT X1

insert into `A` (`name`, `percentage`, `amount`, `p_id`, `t_id`, `id`, `updated_at`, `created_at`) values ('sample', NULL, 200, 'LqdV13UvdfWnpm', 'MhDXqFoc06MfE0', 'MhDXr7Xw7C64dM', 1695786632, 1695786632)

DEADLOCK OCCURS!

DB Logs

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-09-27 03:50:32 22209703704320
*** (1) TRANSACTION:
TRANSACTION 210357795926, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 22 lock struct(s), heap size 3488, 11 row lock(s), undo log entries 4
MySQL thread id 1038135164, OS thread handle 22524200150784, query id 1029362918033 X.X.X.X XXXXXX update
insert into `A` (`name`, `percentage`, `amount`, `p_id`, `t_id`, `id`, `updated_at`, `created_at`) values ('sample', NULL, 200, 'LqdV13UvdfWnpm', 'MhDXqFoc06MfE0', 'MhDXr7Xw7C64dM', 1695786632, 1695786632)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10143 page no 10498 n bits 216 index PRIMARY of table `A` /* Partition `p_A_27sep2023` */ trx id 210357795926 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10143 page no 10498 n bits 216 index PRIMARY of table `A` /* Partition `p_A_27sep2023` */ trx id 210357795926 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 210357795882, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 21 lock struct(s), heap size 3488, 11 row lock(s), undo log entries 4
MySQL thread id 1038135915, OS thread handle 22768112797440, query id 1029362920270 X.X.X.X XXXXXX update
insert into `A` (`name`, `percentage`, `amount`, `p_id`, `t_id`, `id`, `updated_at`, `created_at`) values ('sample', NULL, 120, 'KdVhdkqk9P2IO3', 'MhDXqN9Kj9u7Ww', 'MhDXr9iyC5xnUy', 1695786632, 1695786632)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10143 page no 10498 n bits 216 index PRIMARY of table `A` /* Partition `p_A_27sep2023` */ trx id 210357795882 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10143 page no 10498 n bits 216 index PRIMARY of table `A` /* Partition `p_A_27sep2023` */ trx id 210357795882 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)
------------

I am assuming its because of gap locks but I am not able to figure out why. Can someone please help with this query and explain why this is happening?

I tried to replicate this in local system with prod configs of mysql but could not. Tried going through MySql docs and various other threads, but everywhere deadlocks caused by insert statements are either preceded by a SELECT FOR UPDATE query or a DELETE query.