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,
    PRIMARY KEY (`primary_id`),
KEY key_id (`key_id`),
UNIQUE (`unique_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

First: Transaction with INSERT command

Session A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (1, 2, 2);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into users (primary_id, key_id, unique_id) values (2, 1, 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (3, 2, 1);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (1, 1, 1);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from users;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          1 |      1 |         1 |
|          2 |      1 |         2 |
+------------+--------+-----------+
2 rows in set (0.00 sec)

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;
Query OK, 0 rows affected (0.00 sec)
mysql> update users set key_id = 5 where primary_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update users set key_id = 7 where unique_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update users set unique_id = 9 where key_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          1 |      7 |         1 |
|          2 |      1 |         9 |
+------------+--------+-----------+
2 rows in set (0.01 sec)

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update users set key_id = 6 where primary_id = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update users set key_id = 8 where unique_id = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> update users set unique_id = 10 where key_id = 1;
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

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;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users for update;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          1 |      7 |         1 |
|          2 |      1 |         9 |
+------------+--------+-----------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users where primary_id = 2 for update;
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          2 |      1 |         9 |
+------------+--------+-----------+
1 row in set (0.00 sec)

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (10, 10, 10);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (10, 10, 10);
Query OK, 1 row affected (0.00 sec)

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;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          1 |      7 |         1 |
|          2 |      1 |         9 |
+------------+--------+-----------+
2 rows in set (0.00 sec)
mysql> select * from users where key_id = 1 for update;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          2 |      1 |         9 |
+------------+--------+-----------+
1 row in set (0.00 sec)

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (3, 2, 10);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into users (primary_id, key_id, unique_id) values (3, 6, 10);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into users (primary_id, key_id, unique_id) values (3, 7, 10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (4, 0, 11);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> insert into users (primary_id, key_id, unique_id) values (4, -10000, 11);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

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;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from users;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          1 |      7 |         1 |
|          2 |      1 |         9 |
+------------+--------+-----------+
2 rows in set (0.01 sec)
mysql> select * from users where unique_id = 1 for update;
+------------+--------+-----------+
| primary_id | key_id | unique_id |
+------------+--------+-----------+
|          1 |      7 |         1 |
+------------+--------+-----------+
1 row in set (0.00 sec)

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (5, 0, 2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (6, 0, 0);
Query OK, 1 row affected (0.00 sec)
mysql> insert into users (primary_id, key_id, unique_id) values (7, 0, 1);
^C^C -- query aborted
ERROR 1317 (70100): Query execution was interrupted

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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ref
possible_keys: key_id
key: key_id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)

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
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: const
possible_keys: unique_id
key: unique_id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
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!