consider the below query..
select * from Employee where ID>0 and ID <5.
here ID is the primary key,Employee is an Innodb table, transaction isolation is read committed and there are no rows in the table for the range ID>0 and ID<5.
I thought the above query will only lock the id ranges between 0 and 5 but id 6(for id 6 data is there in table) is also getting locked which is preventing other transactions to update.
while doing explain , the row count it is showing as 1.
explain select * from Employee where (ID>0 and ID<5);
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | Employee | range | PRIMARY | PRIMARY | 8 | NULL | 1 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
But there is no actual rows in system.
select * from Employee where (ID>0 and ID<5);
Empty set (0.00 sec)
show engine innodb status output:
---TRANSACTION B3D2D, ACTIVE 2109 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 894932, OS thread handle 0x7f53263e0700, query id 4140397 localhost root
INNODB_LOCKS table output:
+----------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+
| B42F4:1822:3:7 | B42F4 | X | RECORD | `jbossdb`.`Employee` | `PRIMARY` | 1822 | 3 | 7 | 6 |
| B42ED:1822:3:7 | B42ED | X | RECORD | `jbossdb`.`Employee` | `PRIMARY` | 1822 | 3 | 7 | 6 |
+----------------+-------------+-----------+-----------+----------------------+------------+------------+-----------+----------+-----------+
INNODB_LOCK_WAITS output:
+-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| B42F4 | B42F4:1822:3:7 | B42ED | B42ED:1822:3:7 |
+-------------------+-------------------+-----------------+------------------+
PS : Am using mysql version 5.5
Any guess?
Starting with MySQL 8.0.1, you can actually see all the data locks taken in innodb, they are instrumented by the performance schema:
SELECT * from performance_schema.data_locks;
See https://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html
Possibly related, from the 8.0.1 release notes:
InnoDB: A gap lock was taken unnecessarily during foreign key validation while using the READ COMMITTED isolation level. (Bug #25082593)
So, even when a GAP lock is not supposed to be used by MySQL, it is still worth checking what locks are actually used, using the performance schema.