We have a relative simple InnoDB table with a primary key (integer; sequence number is generated by our client application) and a unique key (across several fields), that looks something like this:

CREATE TABLE `test` (

  `id` int(10) unsigned NOT NULL,

  `fieldA` char(3) NOT NULL,

  `fieldB` smallint(5) unsigned NOT NULL,

  `fieldC` smallint(5) unsigned NOT NULL,

  `fieldD` int(10) unsigned NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `idx_unique` (`fieldA`,`fieldB`,`fieldC`)

) ENGINE=InnoDB DEFAULT;

A client then inserts some records into the table:

START TRANSACTION;

INSERT INTO test VALUES (1, 'abc', 10, 10, 0), (2, 'def', 20, 20, 0), (3, 'ghi', 30, 30, 0);

COMMIT;

Some time later (between 0 and 30 seconds) the client performs a select, using the same still open MariaDB-connection (with no active transaction):

SELECT id FROM test WHERE 

    (fieldA = 'abc' AND fieldB = 10 AND fieldC = 10)

  OR

    (fieldA = 'def' AND fieldB = 20 AND fieldC = 20)

  OR

    (fieldA = 'ghi' AND fieldB = 30 AND fieldC = 30)

And sometimes gets only 2 rows returned, instead of the expected 3.

Sometimes, the SELECT returns the correct number of rows, but the exact same SELECT immediately after returns the incorrect number of rows.

Rows are never updated and are only deleted once a day during a nightly batch run.
The table contains around 60 million rows with anywhere between 300.000 and 1.000.000 new rows (spread across the day) and deleted rows (at night) each day.

The problem happens a couple of times a week, for almost two years now (ever since we migrated the table from MyISAM to InnoDB). We believe it happens more frequent when the table is under high load (which seems plausible, because it appears to be a race condition of some sorts)

We're currently running MariaDB 10.4.22, but the problem also occurred with older 10.4.x versions.

Unfortunately we haven't been able to create a reproducible test case. This probably isn't the most helpful issue description. We're happy gather and provide more details if needed.