MySQL Forums
Forum List  »  Performance

MySQL 5.7 : InnoDB. 2 Deletes query using index but... too many rows locked and cause deadlock
Posted by: Christine Antoine
Date: July 17, 2019 08:30AM

I always have the same deadlock appearing on the same type of transaction and I don't understand the root cause
I suspect MySQL to don't use the index set on the table cid_operation and do a full scan... I don't understand the high number of rows locked in the transac 1.

When I explain this query, it's using an index and I have only 100 rows... why 4386323 in the innodb status ???
EXPLAIN DELETE FROM cid_operation WHERE (id_env_case = 4448) AND (id BETWEEN 15006449 AND 15007342)

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'DELETE', 'cid_operation', NULL, 'range', 'PRIMARY,FK_CID_OPERATION_ENV_CASE', 'FK_CID_OPERATION_ENV_CASE', '9', 'const,const', '100', '100.00', 'Using where'


Please note that there is a PK on cid_operation and an index for the foreign key.

Maybe I should add an index on both id_env_case and id...

Many thanks for the help

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-07-16 14:25:42 0x7ff23e3bc700
*** (1) TRANSACTION:
TRANSACTION 23998754, ACTIVE 1348 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 177122 lock struct(s), heap size 31547600, 4400003 row lock(s), undo log entries 1756000
MySQL thread id 36, OS thread handle 140678403942144, query id 219227 boll.eurocontrol.fr 147.196.221.47 boll updating
DELETE FROM cid_operation WHERE (id_env_case = 4448) AND (id BETWEEN 15006449 AND 15007342)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 369 page no 34335 n bits 216 index PRIMARY of table `ERM_ACC`.`cid_operation` trx id 23998754 lock_mode X locks rec but not gap waiting
Record lock, heap no 95 PHYSICAL RECORD: n_fields 35; compact format; info bits 0


*** (2) TRANSACTION:
TRANSACTION 23998926, ACTIVE 1270 sec fetching rows, thread declared inside InnoDB 1782
mysql tables in use 3, locked 3
73640 lock struct(s), heap size 11542736, 4386323 row lock(s), undo log entries 1747220
MySQL thread id 41, OS thread handle 140678402918144, query id 2468248 boll.eurocontrol.fr 147.196.221.47 boll Sending data
DELETE fpc FROM cid_flight_point_coord fpc INNER JOIN cid_flight_point fp ON fpc.id_cid_flight_point = fp.id INNER JOIN cid_operation o ON fp.id_cid_operation = o.id WHERE (o.id_env_case = 4446) AND (id_cid_operation BETWEEN 14479175 AND 14479961)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 369 page no 34335 n bits 216 index PRIMARY of table `ERM_ACC`.`cid_operation` trx id 23998926 lock_mode X locks rec but not gap
Record lock, heap no 95 PHYSICAL RECORD: n_fields 35; compact format; info bits 0
0: len 4; hex 00d49b17; asc ;;
1: len 6; hex 0000014e4933; asc NI3;;
2: len 7; hex b30005802c0144; asc , D;;
3: len 4; hex 00001161; asc a;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 369 page no 15069 n bits 1192 index FK_CID_OPERATION_ENV_CASE of table `ERM_ACC`.`cid_operation` trx id 23998926 lock mode S locks rec but not gap waiting
Record lock, heap no 1013 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 0000115f; asc _;;
1: len 4; hex 00dd5617; asc V ;;

*** WE ROLL BACK TRANSACTION (2)

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.