Description:
data_locks record error
How to repeat:
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
`k2` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `i_k2` (`k2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
root[test]19:45:08>select * from t;
+----+------+------+
| id | c1 | k2 |
+----+------+------+
| 1 | 1 | 6 |
| 11 | 11 | 11 |
| 22 | 22 | 22 |
+----+------+------+
3 rows in set (0.01 sec)
root[test]19:45:14>
root[test]19:45:48>begin;
Query OK, 0 rows affected (0.00 sec)
root[test]19:45:49>update t set k2=7 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
At this time ,i did the query:
select * from performance_schema.data_locks ;
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4785056296:1180:140574704557768 | 16042 | 64 | 25 | test | t | NULL | NULL | NULL | 140574704557768 | TABLE | IX | GRANTED | NULL |
| INNODB | 4785056296:33:4:2:140574716132888 | 16042 | 64 | 25 | test | t | NULL | NULL | PRIMARY | 140574716132888 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
I think data_locks shoud have three records,but there are two only, i think data_locks lack of a record on index i_k2
At this time,i open other session,and do this:
root[test]19:50:45>begin;
Query OK, 0 rows affected (0.00 sec)
root[test]19:50:51>update t set c1=6 where k2=7;
i query the data_locks,the result is :
root[test]19:46:29>select * from performance_schema.data_locks ;
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4785057152:1180:140574704559800 | 16043 | 65 | 19 | test | t | NULL | NULL | NULL | 140574704559800 | TABLE | IX | GRANTED | NULL |
| INNODB | 4785057152:33:5:5:140574716137496 | 16043 | 65 | 19 | test | t | NULL | NULL | i_k2 | 140574716137496 | RECORD | X | WAITING | 7, 1 |
| INNODB | 4785056296:1180:140574704557768 | 16042 | 64 | 25 | test | t | NULL | NULL | NULL | 140574704557768 | TABLE | IX | GRANTED | NULL |
| INNODB | 4785056296:33:4:2:140574716132888 | 16042 | 64 | 25 | test | t | NULL | NULL | PRIMARY | 140574716132888 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| INNODB | 4785056296:33:5:5:140574716133232 | 16042 | 65 | 19 | test | t | NULL | NULL | i_k2 | 140574716133232 | RECORD | X,REC_NOT_GAP | GRANTED | 7, 1 |
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
then ,i exit the second session(THREAD_ID=65), the bug appear:
root[test]19:51:23>select * from performance_schema.data_locks ;
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
| INNODB | 4785056296:1180:140574704557768 | 16042 | 64 | 25 | test | t | NULL | NULL | NULL | 140574704557768 | TABLE | IX | GRANTED | NULL |
| INNODB | 4785056296:33:4:2:140574716132888 | 16042 | 64 | 25 | test | t | NULL | NULL | PRIMARY | 140574716132888 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |
| INNODB | 4785056296:33:5:5:140574716133232 | 16042 | 65 | 19 | test | t | NULL | NULL | i_k2 | 140574716133232 | RECORD | X,REC_NOT_GAP | GRANTED | 7, 1 |
+--------+-----------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
The thread 65 has already exit, but zhe data_locks still have the record
