About MySQL InnoDB’s Lock

In the article, I will talk about MySQL InnoDB’s lock between transactions.

What is InnoDB?

InnoDB is a storage engine for the MySQL, and InnoDB is used by MySQL 5.5 and later versions. It replace MyISAM, another storage engine, as default engine. You can see more details on Wikipedia.

Let’s start to talk about the lock

The following table is used as an example for the topic.

CREATE TABLE users (
primary_id int NOT NULL,
key_id int NOT NULL,
unique_id int NOT NULL,

First: Transaction with INSERT command

Session A

mysql> begin;

Session B

mysql> begin;

Operation Order

  1. Session A, B start transaction
  2. Session B INSERT entry with primary_id = 1 and key_id = 1 and unique_id = 1 (Succeed)
  3. Session A INSERT entry with primary_id = 1 and key_id = 2 and unique_id = 2 (Session A is locked due to ROWLOCK with same primary_id)
  4. Session A INSERT entry with primary_id = 2 and key_id = 1 and unique_id = 2 (Succeed)
  5. Session A INSERT entry with primary_id = 3 and key_id = 2 and unique_id = 1 (Session A is locked due to ROWLOCK with same unique_id)

Conclusion For This Block

  1. In transaction, when two sessions insert entry with the same primary_id, one of the sessions will be locked until another session finishes its transaction due to ROWLOCK.
  2. In transaction, when two users insert entry with the same key_id, no sessions will be locked.
  3. In transaction, when two sessions insert entry with the same unique_id, one of the sessions will be locked until another session finishes its transaction due to ROWLOCK.

Second: Transaction with UPDATE command

Session A

mysql> begin;

Session B

mysql> begin;

Operation Order

  1. Session A, B start transaction
  2. Session A UPDATE entry with primary_id = 1 (Succeed)
  3. Session B UPDATE entry with primary_id = 1 (Session B is locked due to ROWLOCK with same primary_id)
  4. Session A UPDATE entry with unique_id = 1 (Succeed)
  5. Session B UPDATE entry with unique_id = 1 (Session B is locked due to ROWLOCK with same unique_id)
  6. Session A UPDATE entry with key_id = 1 (Succeed)
  7. Session B UPDATE entry with key_id = 1 (Session B is locked due to ROWLOCK with same key_id)

Conclusion For This Block

  1. UPDATE is the same as INSERT, it will operate ROWLOCK.

Third: Transaction with SELECT FOR UPDATE command with PRIMARY KEY

Session A

mysql> begin;

Session B

mysql> begin;

Operation Order

  1. Session A, B start transaction
  2. Session A SELECT FOR UPDATE for the table (Succeed)
  3. Session B INSERT new entry (Session B is locked due to TABLE LOCK)
  4. Session A, B rollback and start new transaction
  5. Session A SELECT FOR UPDATE with primary_id = 2 (Succeed)
  6. Session B INSERT new entry with primary_id = 10 and key_id = 10 and unique_id = 10 (Succeed because Session A will only lock row with primary_key = 2)

Conclusion For This Block

  1. SELECT FOR UPDATE with the table will operate TABLE LOCK
  2. SELECT FOR UPDATE with PRIMARY KEY will operate ROWLOCK

Forth: Transaction with SELECT FOR UPDATE command with INDEX KEY

Session A

mysql> begin;

Session B

mysql> begin;

Operation Order

  1. Session A, B start transaction
  2. Session A SELECT for the table (Succeed)
  3. Session A SELECT FOR UPDATE with key_id = 1 (Succeed)
  4. Session B INSERT new entry with key_id = 2 (Locked)
  5. Session B INSERT new entry with key_id = 6 (Locked)
  6. Session B INSERT new entry with key_id = 7 (Succeed)
  7. Session B INSERT new entry with key_id = 0 (Locked)
  8. Session B INSERT new entry with key_id = -10000 (Locked)

Conclusion For This Block

  1. SELECT FOR UPDATE with INDEX KEY will operate GAP LOCK
  2. In the table, there are two entries with key_id = 1 and 7. When session A SELECT FOR UPDATE with key_id = 1, key_id in (-∞, 6] will be locked. It calls GAP LOCK.
  3. [NOTE] (-∞, 6]: key_id1 || key_id1 || key_id < 7

Fifth: Transaction with SELECT FOR UPDATE command with UNIQUE KEY

Session A

mysql> begin;

Session B

mysql> begin;

Operation Order

  1. Session A, B start transaction
  2. Session A SELECT for the table (Succeed)
  3. Session A SELECT FOR UPDATE with unique_id = 1 (Succeed)
  4. Session B INSERT new entry with unique_id = 2 (Succeed)
  5. Session B INSERT new entry with unique_id = 0(Succeed)
  6. Session B INSERT new entry with unique_id = 1(Locked)

Conclusion For This Block

  1. SELECT FOR UPDATE with UNIQUE KEY will operate ROWLOCK, not GAP LOCK
  2. When session A SELECT FOR UPDATE with unique_id = 1, row with unique_id = 1 will be locked.

The following explanation is my guess, I am not sure if it is correct or not. If I misunderstand, please tell me!

What is the difference between SELECT FOR UPDATE with PRIMARY KEY, INDEX KEY and UNIQUE KEY

Let’s use the EXPLAIN command to observe the difference. The EXPLAIN statement provides information about how MySQL executes statements. For more detail, please see EXPLAIN. And, we will use type returned by EXPLAIN command. For more detail about the type, please see TYPE.

When we EXPLAIN with primary_id, the type is const which means that the table has at most one matching row. So, I think when we SELECT FOR UPDATE with PRIMARY KEY, it operates ROW LOCK.

mysql> explain select * from users where primary_id = 1\G

When we EXPLAIN with key_id, the type is ref which means all rows with matching index values are read from this table. Although the type is ref which is different from EXPLAIN with primary_id, I don’t know why it will operate GAP LOCK… (I will study it later…)

mysql> explain select * from users where key_id = 1\G

Same as EXPLAIN with primary_id, when we EXPLAIN with unique_id, the type is const which means that the table has at most one matching row.

mysql> explain select * from users where unique_id = 1\G

That’s all I want to share. Thanks for reading.

If you are interested in reading the Japanese version, you can click the following link. It is written by my friend. We studied this together!

My name is 黃基城, you can just call me Tony. I am from Taiwan. Now, I am working in Tokyo, Japan.