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.