As an instructor with Percona I’m sometimes asked about the
differences between the READ COMMITTED and REPEATABLE READ
transaction isolation levels. There are a few differences
between READ-COMMITTED and REPEATABLE-READ, and they are all
related to locking.
Extra locking (not gap locking)
It is important to remember that InnoDB actually locks index entries, not rows. During the execution of a statement InnoDB must lock every entry in the index that it traverses to find the rows it is modifying. It must do this to prevent deadlocks and maintain the isolation level.
If you run an UPDATE that is not well indexed you will lock many rows:
update employees set store_id = 0 where store_id = 1; ---TRANSACTION 1EAB04, ACTIVE 7 sec 633 lock struct(s), heap size 96696, 218786 row lock(s), undo log entries 1 MySQL thread id 4, OS thread handle 0x7f8dfc35d700, query id 47 localhost root …[Read more]