MySQL innodb gap lock

mysql_innodb_gap_lock.sql

-- create table
CREATE TABLE `idempotence_keys` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

-- insert 
insert into idempotence_keys (created_at)
value
("2020-05-14 21:33:44"),
("2020-05-14 21:47:55"),
("2020-05-14 21:48:34"),
("2020-05-14 21:52:37"),
("2020-05-14 22:01:56"),
("2020-05-14 22:08:54"),
("2020-05-14 22:11:04"),
("2020-05-14 22:17:27"),
("2020-05-14 22:24:10"),
("2020-05-14 22:28:15"),
("2020-05-14 22:29:44"),
("2020-05-14 22:31:40"),
("2020-05-14 22:34:05"),
("2020-05-15 05:28:47"),
("2020-05-15 07:19:37"),
("2020-05-15 07:26:49"),
("2020-05-15 07:33:28");


-- mysql> SELECT @@TX_ISOLATION;
-- +-----------------+
-- | @@TX_ISOLATION  |
-- +-----------------+
-- | REPEATABLE-READ |
-- +-----------------+

-- CASE 1
-- T1
begin;
delete from idempotence_keys
where created_at <= '2020-05-14 22:11:04';


-- T2 (blocked)
begin;
update idempotence_keys set created_at = '2020-05-15 07:33:29' where created_at = '2020-05-15 07:33:28';


-- CASE 2
-- T1
begin;
delete from idempotence_keys where created_at < '2020-05-14 21:52:37';

-- T2 （not - block）
begin;
update idempotence_keys set created_at = '2020-05-15 07:33:29' where created_at = '2020-05-15 07:33:28';