Mysql/innodb 8.0.16, read committed transactions, several statements in transaction raise deadlock on deletion of non intercepting rows. Trying to understand what is happening:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-05-14 21:57:44 0x7fe9546c6700
*** (1) TRANSACTION:
TRANSACTION 2852, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 14 row lock(s), undo log entries 25
MySQL thread id 146, OS thread handle 140640122267392, query id 1586 localhost 127.0.0.1 oc5z updating
DELETE FROM deal_product_rows_tmp WHERE batch_no=7533
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 4 n bits 88 index PRIMARY of table `db1`.`deal_product_rows_tmp` trx id 2852 lock_mode X locks rec but not gap waiting
Record lock, heap no 14 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 4; hex 8000650e; asc e ;;
1: len 6; hex 000000000b25; asc %;;
2: len 7; hex 81000001340110; asc 4 ;;
3: len 4; hex 80001d6e; asc n;;
4: len 4; hex 8010a626; asc &;;
5: len 4; hex 8000253a; asc %:;;
6: len 4; hex 8000986e; asc n;;
7: len 4; hex 80000002; asc ;;
8: len 30; hex 415254455820d184d0bed180d0bcd18b20d0bfd180d18fd0bcd0bed183d0; asc WRX ; (total 81 bytes);
9: len 8; hex 0000000000208c40; asc @;;
10: len 1; hex 4d; asc M;;
*** (2) TRANSACTION:
TRANSACTION 2853, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 12
MySQL thread id 147, OS thread handle 140640120497920, query id 1594 localhost 127.0.0.1 oc5z updating
DELETE FROM deal_product_rows_tmp WHERE batch_no=7534
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 11 page no 4 n bits 88 index PRIMARY of table `db1`.`deal_product_rows_tmp` trx id 2853 lock_mode X locks rec but not gap
Record lock, heap no 14 PHYSICAL RECORD: n_fields 11; compact format; info bits 0
0: len 4; hex 8000650e; asc e ;;
1: len 6; hex 000000000b25; asc %;;
2: len 7; hex 81000001340110; asc 4 ;;
3: len 4; hex 80001d6e; asc n;;
4: len 4; hex 8010a626; asc &;;
5: len 4; hex 8000253a; asc %:;;
6: len 4; hex 8000986e; asc n;;
7: len 4; hex 80000002; asc ;;
8: len 30; hex 415254455820d184d0bed180d0bcd18b20d0bfd180d18fd0bcd0bed183d0; asc WRX ; (total 81 bytes);
9: len 8; hex 0000000000208c40; asc @;;
10: len 1; hex 4d; asc M;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 4 n bits 96 index PRIMARY of table `db1`.`deal_product_rows_tmp` trx id 2853 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 11; compact format; info bits 32
0: len 4; hex 80006502; asc e ;;
1: len 6; hex 000000000b24; asc $;;
2: len 7; hex 0200000128012d; asc ( -;;
3: len 4; hex 80001d6d; asc m;;
4: len 4; hex 80109d40; asc @;;
5: len 4; hex 800023ba; asc # ;;
6: len 4; hex 800098e6; asc ;;
7: len 4; hex 80000007; asc ;;
8: len 9; hex 466f696c20676c7565; asc Foil glue;;
9: len 8; hex 0000000000005940; asc Y@;;
10: len 1; hex 4d; asc M;;
*** WE ROLL BACK TRANSACTION (2)
Table structure:
CREATE TABLE `deal_product_rows_tmp` (
`batch_no` int(11) NOT NULL,
`bitrix_id` int(11) NOT NULL,
`deal_id` int(11) NOT NULL,
`product_id` int(11) NOT NULL,
`quantity` int(11) NOT NULL,
`product_name` varchar(1000) NOT NULL,
`price` double DEFAULT NULL,
`status` varchar(50) NOT NULL,
`tmp_id` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`tmp_id`),
KEY `idx_deal_row_tmp_deal` (`deal_id`),
KEY `idx_deal_row_tmp_batchno` (`batch_no`)
) ENGINE=InnoDB AUTO_INCREMENT=7500 DEFAULT CHARSET=utf8
1) queries delete separated rows, how it is possible RECORD LOCKS on the same row?
2) even if the transactions applying lock on the same row, why deadlock? why first transaction can't wait until second release the lock ?
UPDATE1: App logic - spring boot app receiving rest call from crm system, fetching client order (deal) product rows and trying to sync the data in micro dwh. There are multiple statements in RC transaction, only one type of transaction implemented in this app. First statement REPLACE INTO deal table, this should prevent processing batches on the same deal_id in parallel (FK keys not used at the moment). Next the transaction inserting received product rows in "tmp" table and comparing on target "dwh" table. inserting missing deal products, updating status of "deleted" product rows. Next step deleting "tmp" data from deal_product_rows_tmp (point of deadlock) and final step - mark batch as completed (update batch set status=.. table where batch_no=? )
autocommit=ON
and theseDELETEs
stand alone?