Question 113144 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search