Say, I have a table
id int(10) unsigned NOT NULL,
f1 int(11) NOT NULL,
f2 int(11) NOT NULL,
PRIMARY KEY (
) 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
–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