Bug #93882 Many queries executing delete returns "starting index read"
Submitted: 10 Jan 2019 20:39 Modified: 13 Jan 2019 18:26
Reporter: Edi Carlos de Moura Bragança Edi Carlos Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.42 OS:CentOS
Assigned to: CPU Architecture:x86
Tags: starting index read

[10 Jan 2019 20:39] Edi Carlos de Moura Bragança Edi Carlos
Description:
Many queries executing delete return status updating. INNODB STATUS returns the message "starting index read" like this:

---TRANSACTION 87998763591, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 240829286, OS thread handle 0x7eee879e7700, query id 1019350318 10.80.5.51 new_area_emp updating
DELETE FROM `table_name` WHERE `emp_id` = '411027' AND `vag_id` = '14300918'
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 503392 page no 4 n bits 464 index `PRIMARY` of table `database`.`table` trx id 87998763591 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 4; hex 8000001f; asc     ;;
 1: len 4; hex 80b1c889; asc     ;;
 2: len 4; hex 801152a5; asc   R ;;
 3: len 6; hex 00121debcb39; asc      9;;
 4: len 7; hex b20001402c0110; asc    @,  ;;
 5: len 4; hex 80000000; asc     ;;
 6: len 4; hex 58c6e439; asc X  9;;

Datails:

mysql> SELECT @@TX_ISOLATION;
+-----------------+
| @@TX_ISOLATION  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

mysql> SELECT @@VERSION;
+------------+
| @@VERSION  |
+------------+
| 5.6.42-log |
+------------+

mysql> SHOW CREATE TABLE database.table_name\G
*************************** 1. row ***************************
       Table: table_name
Create Table: CREATE TABLE `table_name` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vag_id` int(11) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `candidaturas` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`vag_id`,`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8097947 DEFAULT CHARSET=latin1

mysql> SELECT * FROM information_schema.INNODB_LOCKS ;
+------------------------+-------------+-----------+-----------+-----------------------------+------------+------------+-----------+----------+----------------------------+
| lock_id                | lock_trx_id | lock_mode | lock_type | lock_table                  | lock_index | lock_space | lock_page | lock_rec | lock_data                  |
+------------------------+-------------+-----------+-----------+-----------------------------+------------+------------+-----------+----------+----------------------------+
| 87998811078:503392:4:2 | 87998811078 | X         | RECORD    | `database`.`table_name`     | PRIMARY    |     503392 |         4 |        2 | 31, 11651209, 1135269      |
| 87998811049:503392:4:2 | 87998811049 | X         | RECORD    | `database`.`table_name`     | PRIMARY    |     503392 |         4 |        2 | 31, 11651209, 1135269      |
| 87998810547:503392:4:2 | 87998810547 | X         | RECORD    | `database`.`table_name`     | PRIMARY    |     503392 |         4 |        2 | 31, 11651209, 1135269      |
| 87998810547:503392:4:2 | 87998810245 | X         | RECORD    | `database`.`table_name`     | PRIMARY    |     503392 |         4 |        2 | 31, 11651209, 1135269      |
+------------------------+-------------+-----------+-----------+-----------------------------+------------+------------+-----------+----------+----------------------------+

See that it always occurs in the same lock_space and lock_data and occurred several times by locking mysql and leaving the deletes on this table very slow.

I need execute one restart on mysql to restore the writing of data in the table.

How to repeat:
I could not reproduce the problem in development, only in production.
[13 Jan 2019 18:26] MySQL Verification Team
Thank you for the bug report. It's necessary a repeatable test case to process this bug report so please comment here and provide a repeatable test case. Thanks.