Why does MySQL UPDATE with <= lock insert intention?
I believe the issue is due to the UPDATE using "next-key locking" as evidenced by "asc supremum" in the engine status conflicting with "insert intention locking" of the INSERT as evidenced by "lock_mode X insert intention waiting" in the engine status. It seems the only way around this is to effectively disable gap locking entirely by using READ COMMITTED transaction isolation level for Transaction 1 or innodb_locks_unsafe_for_binlog.

Transaction 1

set transaction isolation level read committed;
start transaction;
update foo set value = NOW();


Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected


Since using READ COMMITTED disables all gap locking (including next-key locking) in favor of index record locking it is not necessary to use where id &lt;= @max so this can now be removed. I do not believe it is possible to accomplish this update without blocking the insert using REPEATABLE READ.