throw light on Innodb locking scenario

MySQL & MariaDB Other MySQL® Questions
1

Hello,

Say, I have a table

CREATE TABLE numbers (
id int(10) unsigned NOT NULL,
f1 int(11) NOT NULL,
f2 int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And populate this table with 2097152 or so entries.

Start Transaction T1 in one terminal, >> delete from numbers where id < 2097119;

do not commit yet.
Start Transaction T2 in another terminal >> insert into numbers (f1, f2) values (100,200);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

The second transaction times out.

The question is why is the second transaction needing to wait for a lock ?

Both transactions are in repeatable-read isolation level.

In another terminal, running

show engine innodb status

reveals
–TRANSACTION 11DF9B, ACTIVE 931 sec
4205 lock struct(s), heap size 620984, 2101322 row lock(s), undo log entries 2097118

Noticing that undo log entries match the number of rows, but there are more row locks.

Hoping some can help with this

2

Noting that

  1. id is not auto increment
  2. when you specify id in the insert cmd, the row gets inserted right away
    >> insert into numbers (id, f1, f2) values (2097153, 100,200);