Deadlock while writing to mysql table

Question

I have following mysql (8.0.26) insert statemement

INSERT INTO ReadModel (ID, Version, ProjectionVersion, Deleted, Type, Model)
SELECT ?, ?, ?, ?, ?, ?
FROM dual
WHERE EXISTS(SELECT *
             FROM ReadModel
             WHERE ID = Values(ID) AND Type = Values(Type) AND Version < Values(Version)
                OR ProjectionVersion < Values(ProjectionVersion))
   OR NOT EXISTS(SELECT * FROM ReadModel WHERE ID = Values(ID) AND Type = Values(Type))
ON DUPLICATE KEY UPDATE ID                = VALUES(ID),
                        Version           = VALUES(Version),
                        ProjectionVersion = VALUES(ProjectionVersion),
                        Deleted           = VALUES(Deleted),
                        Model             = VALUES(Model),
                        Type              = VALUES(Type)
                        

I want to only update a row in ReadModel if the new ProjectionVersion or Version is larger than the current ProjectionVersion or Version, respectively.

I also want to handle the case where I want to insert a row if there is no existing row available in the ReadModel table (see NOT EXISTS(SELECT * FROM ReadModel WHERE ID = Values(ID) AND Type = Values(Type)))

If I update more than two rows at once on an empty table I run into a deadlock pretty quickly.

This is the error that I get from the mysql driver

Error 1213: Deadlock found when trying to get lock; try restarting transaction

An analysis with SHOW ENGINE INNODB STATUS show following deadlock

RECORD LOCKS space id 568 page no 4 n bits 72 index PRIMARY of table ReadModel trx id 76209 lock_mode X insert intention waiting

On a separate run I get following error

RECORD LOCKS space id 568 page no 9 n bits 88 index PRIMARY of table ReadModel trx id 77468 lock_mode X locks gap before rec insert intention waiting

Writing to the table with "ReadCommitted" isolation level solves the problem. Is there anything else that I'm missing?

The primary key of ReadModel is (ID, Type).