Search code examples
mysqllockinginnodb

Does InnoDB behind the scenes ALSO use table locks?


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.


Solution

  • 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.