Differences of MySQL locking for update with WHERE and IN
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 &gt;= 1 AND id &lt;= 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