mysql innodb row level locking

We know that MySql InnoDB engine provides row level locking, while MyISAM can only do table level lock. But, InnoDB's row level locking mechanism may not be as what you expect. InnoDB doesn't really lock rows of data, instead, it set locks on on every index record that is scanned in the processing of the SQL statement. This means, only when your SQL statement is using index to query data, InnoDB will use row level lock. Otherwise, InnoDB will use table level lock. If we don't pay attention to this, we may end up with lots of lock conflicts in our application.

Let's do some simple tests.

mysql> create table no_index_lock_test(id int,name varchar(10)) engine=innodb;

Query OK, 0 rows affected (0.15 sec)

mysql> insert into no_index_lock_test values(1,'henry'),(2,'alice'),(3,'bob'),(4,'jack');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

Let's start two sessions

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from no_index_lock_test where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 'henry' | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from no_index_lock_test where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 'alice'  | +------+------+ 1 row in set (0.00 sec)
mysql> select * from no_index_lock_test where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 'henry'    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from no_index_lock_test where id = 2 for update; Will keep waiting...

This example show us InnoDB can only use table level locking if no index is available. In session 1, it looks like we only set a lock on one row (where id=1 for update). But we don't have index on id column, InnoDB is actually locking the whole table, so in session two, when we try to set a lock to a different row(where id=2 for update), we have to wait until the lock in session 1 is released.

Now, let's add index to our id column.

mysql> create table index_lock_test(id int,name varchar(10)) engine=innodb;

Query OK, 0 rows affected (0.15 sec)

mysql> alter table index_lock_test add index id(id);

Query OK, 4 rows affected (0.24 sec)

mysql> insert into index_lock_test values(1,'henry'),(2,'alice'),(3,'bob'),(4,'jack');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

session_1	session_2
mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from index_lock_test where id = 1 ; +------+------+ | id   | name | +------+------+ | 1    | 'henry' | +------+------+ 1 row in set (0.00 sec)	mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select * from index_lock_test where id = 2 ; +------+------+ | id   | name | +------+------+ | 2    | 'alice'  | +------+------+ 1 row in set (0.00 sec)
mysql> select * from index_lock_test where id = 1 for update; +------+------+ | id   | name | +------+------+ | 1    | 'henry'    | +------+------+ 1 row in set (0.00 sec)
	mysql> select * from index_lock_test where id = 2 for update; +------+------+ | id   | name | +------+------+ | 2    | 'alice'  | +------+------+ 1 row in set (0.00 sec)

This time, InnoDB is using row level lock.