Inno DB / MySQL deadlock with two UPDATE statements to the same table but different rows

Executing show engine innodb status shows deadlocks between the following two queries:

update `transparency_codes` set `shipment_id` = 1265, `transparency_codes`.`updated_at` = '2024-05-08 17:45:30' where `shipment_id` is null and `item_id` = 508 order by `id` asc limit 24
update `transparency_codes` set `shipment_id` = 1264, `transparency_codes`.`updated_at` = '2024-05-08 17:45:30' where `shipment_id` is null and `item_id` = 428 order by `id` asc limit 24

The item_id in these two queries are different, and it has a foreign key to the items table. I thought the DB engine would make use of this foreign key and only lock relevant rows in this table instead of locking the whole table, but it seems I was wrong.

BTW, there are some values like AZ:M3YKJFFBMJEUTO7Q754CE4XDQI and CG6178033486903931309 in the logs below, they are two other fields in the transparency_codes table.

Here is the output of show engine innodb status:

=====================================
2024-05-09 13:46:10 23306471180032 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 10 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 69121 srv_active, 0 srv_shutdown, 6948657 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 18423
OS WAIT ARRAY INFO: signal count 20265
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-05-08 17:45:30 23306709235456
*** (1) TRANSACTION:
TRANSACTION 1126949, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1128, 3 row lock(s)
MySQL thread id 495318, OS thread handle 23306479826688, query id 15628991 System lock
update `transparency_codes` set `shipment_id` = 1265, `transparency_codes`.`updated_at` = '2024-05-08 17:45:30' where `shipment_id` is null and `item_id` = 508 order by `id` asc limit 24

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 234 page no 29 n bits 232 index PRIMARY of table `transparency_codes` trx id 1126949 lock_mode X locks rec but not gap
Record lock, heap no 13 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 000000000000000d; asc         ;;
 1: len 6; hex 0000001131fb; asc     1 ;;
 2: len 7; hex 01000001d0349e; asc      4 ;;
 3: len 29; hex 415a3a4d33594b4a4646424d4a4555544f375137353443453458445149; asc AZ:M3YKJFFBMJEUTO7Q754CE4XDQI;;
 4: len 21; hex 434736313738303333343836393033393331333039; asc CG6178033486903931309;;
 5: len 8; hex 00000000000001fc; asc         ;;
 6: SQL NULL;
 7: len 4; hex 663a9ae5; asc f:  ;;
 8: len 4; hex 663bb28e; asc f;  ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 9 n bits 848 index transparency_codes_shipment_id_foreign of table `transparency_codes` trx id 1126949 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000001; asc         ;;


*** (2) TRANSACTION:
TRANSACTION 1126948, ACTIVE 0 sec
mysql tables in use 3, locked 3
LOCK WAIT 3 lock struct(s), heap size 1128, 14 row lock(s)
MySQL thread id 495317, OS thread handle 23306682726144, query id 15628986 System lock
update `transparency_codes` set `shipment_id` = 1264, `transparency_codes`.`updated_at` = '2024-05-08 17:45:30' where `shipment_id` is null and `item_id` = 428 order by `id` asc limit 24

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 234 page no 9 n bits 848 index transparency_codes_shipment_id_foreign of table `transparency_codes` trx id 1126948 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000001; asc         ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000002; asc         ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000003; asc         ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000004; asc         ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000005; asc         ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000006; asc         ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000007; asc         ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000008; asc         ;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 0000000000000009; asc         ;;

Record lock, heap no 11 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 000000000000000a; asc         ;;

Record lock, heap no 12 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 000000000000000b; asc         ;;

Record lock, heap no 13 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: SQL NULL;
 1: len 8; hex 000000000000000c; asc         ;;

Record lock, heap no 14 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 8; hex 000000000000000d; asc         ;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 234 page no 29 n bits 232 index PRIMARY of table `transparency_codes` trx id 1126948 lock_mode X locks rec but not gap waiting
Record lock, heap no 13 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 000000000000000d; asc         ;;
 1: len 6; hex 0000001131fb; asc     1 ;;
 2: len 7; hex 01000001d0349e; asc      4 ;;
 3: len 29; hex 415a3a4d33594b4a4646424d4a4555544f375137353443453458445149; asc AZ:M3YKJFFBMJEUTO7Q754CE4XDQI;;
 4: len 21; hex 434736313738303333343836393033393331333039; asc CG6178033486903931309;;
 5: len 8; hex 00000000000001fc; asc         ;;
 6: SQL NULL;
 7: len 4; hex 663a9ae5; asc f:  ;;
 8: len 4; hex 663bb28e; asc f;  ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 1133023
Purge done for trx's n:o < 1133023 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 304782168300440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304782168299632, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304782168298824, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304782168298016, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 304782168297208, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests ((null))
I/O thread 1 state: waiting for completed aio requests (insert buffer thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:
Pending flushes (fsync) log: 0; buffer pool: 0
2591 OS file reads, 1651311 OS file writes, 957855 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.71 writes/s, 0.55 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 23 merges
merged operations:
 insert 80, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 69257, node heap has 3 buffer(s)
Hash table size 69257, node heap has 20 buffer(s)
Hash table size 69257, node heap has 1 buffer(s)
Hash table size 69257, node heap has 4 buffer(s)
Hash table size 69257, node heap has 9 buffer(s)
Hash table size 69257, node heap has 7 buffer(s)
Hash table size 69257, node heap has 8 buffer(s)
Hash table size 69257, node heap has 2 buffer(s)
0.40 hash searches/s, 0.10 non-hash searches/s
---
LOG
---
Log sequence number          541239194
Log buffer assigned up to    541239194
Log buffer completed up to   541239194
Log written up to            541239194
Log flushed up to            541239194
Added dirty pages up to      541239194
Pages flushed up to          541239194
Last checkpoint at           541239194
Log minimum file id is       24
Log maximum file id is       27
657011 log i/o's done, 0.18 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 0
Dictionary memory allocated 1160642
Buffer pool size   16383
Free buffers       12650
Database pages     3679
Old database pages 1338
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 163559, not young 1550
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2475, created 1233, written 782792
0.00 reads/s, 0.00 creates/s, 4.24 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 3679, unzip_LRU len: 0
I/O sum[96]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=817, Main thread ID=23306690352896 , state=sleeping
Number of rows inserted 1138757, updated 244335, deleted 1987, read 502299791
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 854, updated 24770, deleted 712, read 5511582
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 1.60 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Here is the definition of the transparency_codes table:

CREATE TABLE `transparency_codes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `uid` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `job_id` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_id` bigint unsigned NOT NULL,
  `shipment_id` bigint unsigned DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transparency_codes_uid_unique` (`uid`),
  KEY `transparency_codes_item_id_foreign` (`item_id`),
  KEY `transparency_codes_shipment_id_foreign` (`shipment_id`),
  KEY `transparency_codes_job_id_index` (`job_id`),
  CONSTRAINT `transparency_codes_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `transparency_codes_shipment_id_foreign` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=781 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The items and shipments tables both have a id auto incremental primary key, their other fields are not involved in this question.

Any hints or help are appreciated.