Connection1:
BEGIN;
SELECT *
FROM world.city
WHERE ID = 130
FOR SHARE;
Connection2:
SELECT engine, thread_id, object_schema,
object_name, lock_type, lock_mode,
lock_status, lock_data
FROM performance_schema.data_locks;
+--------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+
| engine | thread_id | object_schema | object_name | lock_type | lock_mode | lock_status | lock_data |
+--------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+
| INNODB | 130 | world | city | TABLE | IS | GRANTED | NULL |
| INNODB | 130 | world | city | RECORD | S,REC_NOT_GAP | GRANTED | 130 |
+--------+-----------+---------------+-------------+-----------+---------------+-------------+-----------+
I get a table lock and a record lock. I knew InnoDB used ONLY record level blocks.
Innodb sets intention locks (IS) on tables:
Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.