MySQL InnoDB not row level locking for all queries

One misconception people have about MyISAM and InnoDB is InnoDB use row level locking and MyISAM use table level locking. It's true that MyIASM has no support row level locking, but InnoDB only support row level locking in particular circumstances.

InnoDB uses Record locks for row level locking, which means it set locks on index record. Only when the query using index, the row level locks are used. Otherwise, the whole table is locked.

This is different from the row lock in Oracle, in Oracle the lock is set on data block of the row.

To illustrate this, I created an example. Let's create an InnoDB table without index defined and start two sessions(you can open two command windows), in first session we start a transaction and update a row without commit, then in the second session we try to update another row, since the whole table is locked, the second session will wait.

Create an InnoDB table and insert two rows

CREATE TABLE testtable (
  id INT ,
  username VARCHAR(100) 
) engine=innodb;
INSERT INTO testtable VALUES(1, 'hello');
INSERT INTO testtable VALUES(2, 'world');

In first session , execute

UPDATE testtable SET username = 'foo' WHERE id = 1;

And go to second session execute

UPDATE testtable SET username = 'foo' WHERE id = 2;

We can see the second session hangs until the timeout.