Mysql – Differences of MySQL locking for update with WHERE and IN

lockingMySQL

So I have a table with id as INT NOT NULL as the PK. When I do query 1 (Trans1) in a transaction I get 3 rows locked, and when I do query 2 (Trans2) also in a transaction, I do also get 3 rows locked.

Example Table:

+----+--------------------------------------+-------------------------+
| id | some_val                             | created_at              |
+----+--------------------------------------+-------------------------+
|  1 | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21.130 |
|  2 | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31.292 |
|  3 | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01.396 |
+----+--------------------------------------+-------------------------+

Trans1: 

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

INNODB Status

---TRANSACTION 10750, ACTIVE 4 sec
3 lock struct(s), heap size 360, 4 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11504 172.19.0.1 root

Trans2: 

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

INNODB Status

---TRANSACTION 10854, ACTIVE 11 sec
2 lock struct(s), heap size 360, 3 row lock(s)
MySQL thread id 23, OS thread handle 0x7f41f5153700, query id 11652 172.19.0.1 root

Now, even though the results seem similar, they are quite different in regarding of what's actually locked. For starter, no inserts are possible while Trans1 is in progress, while inserts are possible during Trans2. Also, if table has only 3 rows, how is INNODB showing 4 locks (I guess is the next auto increment, thus no inserts possible).

I would like to understand why does this happen, as I've read about gap locking and next-key lock, but none makes sense about what's happening on Trans1.

Best Answer

If we use the performance_schema.data_locks table we get a better idea which locks are different.

Here we can see the supremum, or gap lock is the additional lock occurs with the RANGE query. While the is possible to avoid because because id 3 exists as a primary key, the MySQL developers haven't implemented this as it complicates locking and adds checks in a rather critical and time sensitive execution path.

Note, setting SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; will make locks equivalent as gap locks are only REPEATABLE READ.

Schema (MySQL v8.0)

CREATE TABLE example (
  `id` INTEGER NOT NULL PRIMARY KEY,
  `some_val` VARCHAR(36),
  `created_at` datetime
);

INSERT INTO example
  (`id`, `some_val`, `created_at`)
VALUES
  ('1', '33aa186d-e2de-4c41-aa50-000000000001', '2019-03-17 15:15:21.130'),
  ('2', '33aa186d-e2de-4c41-aa50-000000000002', '2019-03-17 15:15:31.292'),
  ('3', '33aa186d-e2de-4c41-aa50-000000000003', '2019-03-17 15:16:01.396');

Query #1

BEGIN;

Query #2

SELECT * FROM example WHERE id >= 1 AND id <= 3 FOR UPDATE;

| id  | some_val                             | created_at          |
| --- | ------------------------------------ | ------------------- |
| 1   | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21 |
| 2   | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31 |
| 3   | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01 |

Query #3

SELECT * FROM performance_schema.data_locks;

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA              |
| ------ | -------------- | --------------------- | --------- | -------- | ------------- | ----------- | -------------- | ----------------- | ---------- | --------------------- | --------- | --------- | ----------- | ---------------------- |
| INNODB | 2070:1063      | 2070                  | 48        | 4        | test          | example     |                |                   |            | 140108678336728       | TABLE     | IX        | GRANTED     |                        |
| INNODB | 2070:2:4:2     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 1                      |
| INNODB | 2070:2:4:1     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | supremum pseudo-record |
| INNODB | 2070:2:4:3     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | 2                      |
| INNODB | 2070:2:4:4     | 2070                  | 48        | 4        | test          | example     |                |                   | PRIMARY    | 140108678334032       | RECORD    | X         | GRANTED     | 3                      |

Query #4

ROLLBACK;

Query #5

BEGIN;

Query #6

SELECT * FROM example WHERE id IN (1,2,3) FOR UPDATE;

| id  | some_val                             | created_at          |
| --- | ------------------------------------ | ------------------- |
| 1   | 33aa186d-e2de-4c41-aa50-000000000001 | 2019-03-17 15:15:21 |
| 2   | 33aa186d-e2de-4c41-aa50-000000000002 | 2019-03-17 15:15:31 |
| 3   | 33aa186d-e2de-4c41-aa50-000000000003 | 2019-03-17 15:16:01 |

Query #7

SELECT * FROM performance_schema.data_locks;

| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
| ------ | -------------- | --------------------- | --------- | -------- | ------------- | ----------- | -------------- | ----------------- | ---------- | --------------------- | --------- | --------- | ----------- | --------- |
| INNODB | 2071:1063      | 2071                  | 48        | 9        | test          | example     |                |                   |            | 140108678336728       | TABLE     | IX        | GRANTED     |           |
| INNODB | 2071:2:4:2     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 1         |
| INNODB | 2071:2:4:3     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 2         |
| INNODB | 2071:2:4:4     | 2071                  | 48        | 9        | test          | example     |                |                   | PRIMARY    | 140108678333688       | RECORD    | X         | GRANTED     | 3         |

Query #8

ROLLBACK;

View on DB Fiddle

Related Question