Это баг или фича?

Оракл и постгре в этом случае сразу ругаются, что нарушен primary key constraint, что на мой взгляд более логично.

MySql 8.0.25

Вот когда первая сессия удалит эту запись, тогда ожидание будет вполне логично - кстати, именно так работает в оракле.
А если запись не удалена и нода всё ещё присутствует в индексе - чего висеть то? Почему нельзя сразу отдать dupliacte entry - я же не зря выставляю read committed.

Что значит не committed?
в первой сессии после инсерта идёт коммит.

А зачем что-то предугадывать?

insert в другой сессии ни физически, ни теоретически не может тронуть эту запись.

Ещё раз, оракл (и постгре) в этой ситуации действуют ровно настолько, насколько требуется в данный конкретный момент времени, безо всяких гаданий и предсказаний, а именно:

1) select for update в первой сессии не блокирует попытку инсерта во второй. У нас есть первичный ключ, который в данный момент уже содержит значение, и это значение никто пока даже ещё не попытался удалить или изменить. Вторая сессия получит primary key violation сразу, безо всяких ожиданий;

2) а вот если в первой сессии последует удаление, или изменение первичного ключа (причём не важно, был ли вообще перед этим select for update), тогда, конечно, попытка инсерта будет заблокирована до окочания транзакции в первой сессии - чтобы не нарушить primary key constraint.

Мой вопрос был насчёт п. 1 - MySql зачем-то накладывает блокировку там, где она не нужна.

уточнение - блокировка нужна, но она не должна блокировать заведомо невалидный инсерт

а почему вы уверены что это блокировка именно данных?

Я не в курсе природы этой блокировки, так как с MySql никогда не работал и с внутренностями не знаком.

Мне понадобилось перенести одну функциональность с оракла, обнаружил разницу в поведении.

После изменения id он не должен пройти как по маслу. В этом случае он должен подвиснуть в ожидании окончания транзакции, с этим сценарием вопросов нет.

А вот до изменения id смысла висеть нету - логичнее сразу отдать primary key violation.

Оракл и постгре умеют отличать эти два состояния, а My Sql видимо нет.