Not the answer you need?
Register and ask your own question!

throw light on Innodb locking scenario

madhuramannamadhuramanna EntrantCurrent User Role Beginner
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

Comments

  • madhuramannamadhuramanna Entrant Current User Role Beginner
    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);
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.