Does InnoDB behind the scenes ALSO use table locks?-CodePudding

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.

CodePudding user response：

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.

CodePudding user response：

Performance schema pulls data from the buffer pool and not disk. If it can not find the page in the pool it will report the lock_data column as NULL.

See https://dev.mysql.com/blog-archive/innodb-data-locking-part-2-5-locks-deeper-dive/