MySQL InnoDB Row Level Locking Mechanism

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 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.

Let's do some simple tests.

create table called innodb_lock_test as below.  alter table innodb_lock_test add index id(id);

mysql> create table innodb_lock_test(id int,name varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.15 sec)
mysql> insert into innodb_lock_test values(1,'Trimurthy'),(2,'Anand'),(3,'Krishna'),(4,'Prasad');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0


Now Lets open 2 sessions.

SESSION #1 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)

SESSION #2 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =2;
+------+-------+
| id   | name  |
+------+-------+
|    2 | Anand |
+------+-------+
1 row in set (0.00 sec)


SESSION #1 :

mysql> select * from innodb_lock_test where id =1 for update;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)


SESSION #2 :
mysql> select * from innodb_lock_test where id =2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

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 and repeat the same test.

mysql> alter table innodb_lock_test add index id(id);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

SESSION #1 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =1;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)

SESSION #2 :

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock_test where id =2;
+------+-------+
| id   | name  |
+------+-------+
|    2 | Anand |
+------+-------+
1 row in set (0.00 sec)


SESSION #1 :

mysql> select * from innodb_lock_test where id =1 for update;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | Trimurthy |
+------+-----------+
1 row in set (0.00 sec)


SESSION #2 :

mysql> select * from innodb_lock_test where id =2 for update;
+------+-------+
| id   | name  |
+------+-------+
|    2 | Anand |
+------+-------+
1 row in set (0.00 sec)

If we observer session1 and session2 both the queries got executed(means both the queries got locks on respective rows) because of index.

CONCLUSION : InnoDB row level locks works only when your SQL statement is using index to query data.


MySQL Internals, MySQL HighAvailability, MySQL Performance Tuning, MySQL Query Optimization, MySQL performance, MySQL Fabric, MySQL HA, MySQL Installation, MySQL Upgrade, InnoDB Performance Tuning, InnoDB Buffer Pool Size, MySQL Performance Tuning, MySQL ClusterMySQL Latest News, News and Events, MySQL Customers