Understanding Deadlocks in MySQL

[Saubhik Banerjee]

I am new to MySQL, I used to work in Oracle database. I am having some problem in resolving Deadlocks in my application. Please help me to understand the issue.

Table Definition:
------------------
CREATE TABLE `APPLICATION` (
`ID` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`APPLICATION_NUMBER` varchar(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`STATUS` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`SUB_STATUS` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`SOURCE_TYPE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`SOURCE_ID` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`SOURCE_CHANNEL` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`PRODUCT_PROGRAM` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`LOAN_TYPE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`ASSIGNED_TO` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CREATED_BY` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CREATION_DATE` datetime DEFAULT NULL,
`LAST_UPDATE_DT` datetime DEFAULT NULL,
`LAST_UPDATE_BY` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`LOAN_AMOUNT` decimal(38,0) DEFAULT NULL,
`INSURANCE_OPT_IN` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`RCU_STATUS` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`TVR_COMMENTS` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`TVR_DECISION` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`MM_PAID_TO` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`PURPOSE_OF_LOAN` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`MARGIN_MONEY` double DEFAULT NULL,
`PAYMENT_MODE` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`IS_ELIGIBLE` decimal(1,0) DEFAULT NULL,
`CRM_STATUS` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CRM_REASON` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`IS_INTERESTED_CLI` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`IS_INTERESTED_CI` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CRITICAL_ILLNESS` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CREDIT_LIFE_INSURANCE` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CREATE_USER` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`CREATE_DATE` datetime DEFAULT NULL,
`LAST_UPDATE_USER` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`LAST_UPDATE_DATE` datetime DEFAULT NULL,
`STATUS_ID` bigint(35) DEFAULT NULL,
`SUBSTATUS_ID` bigint(35) DEFAULT NULL,
`DOCUMEMNTUPLOAD_COMMENTS` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`DOCUMEMNTUPLOAD_ACK` decimal(1,0) DEFAULT NULL,
`NO_OF_FINANCIERS_FOR_ALL_ASSET` decimal(3,0) DEFAULT NULL,
`DUPLICATED_FROM` varchar(100) DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE,
KEY `FK_APL_STATID` (`STATUS_ID`) USING BTREE,
KEY `FK_APL_SUBSTATID` (`SUBSTATUS_ID`) USING BTREE,
CONSTRAINT `FK_APL_STATID` FOREIGN KEY (`STATUS_ID`) REFERENCES `STATUS` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `FK_APL_SUBSTATID` FOREIGN KEY (`SUBSTATUS_ID`) REFERENCES `SUB_STATUS` (`ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

My Deadlock Details from the SHOW ENGINE INNODB STATUS:
=======================================================
------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-22 04:48:06 0x2ad75cf91700
*** (1) TRANSACTION:
TRANSACTION 291327, ACTIVE 37 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 48 lock struct(s), heap size 8400, 1540 row lock(s), undo log entries 5
MySQL thread id 14042, OS thread handle 47099630130944, query id 4174847 172.29.24.227 bpapi updating
UPDATE APPLICATION SET NO_OF_FINANCIERS_FOR_ALL_ASSET = 7 WHERE id >'' AND APPLICATION_NUMBER = '001601'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3803 page no 21 n bits 144 index PRIMARY of table `BAPIDB`.`APPLICATION` trx id 291327 lock_mode X waiting
Record lock, heap no 72 PHYSICAL RECORD: n_fields 42; compact format; info bits 128
0: len 30; hex 65646466383861382d633733342d346561332d393665302d396366343234; asc eddf88a8-c734-4ea3-96e0-9cf424; (total 36 bytes);
1: len 6; hex 000000046e42; asc nB;;
2: len 7; hex 02000000fa0518; asc ;;
3: len 6; hex 303031353930; asc 001590;;
4: len 4; hex 31303033; asc 1003;;
5: len 4; hex 31303033; asc 1003;;
6: SQL NULL;
7: SQL NULL;
8: len 6; hex 506f7274616c; asc Portal;;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: len 30; hex 35633131613436612d303963302d313165612d396533372d396165613961; asc 5c11a46a-09c0-11ea-9e37-9aea9a; (total 36 bytes);
13: SQL NULL;
14: len 5; hex 99a4ac4b92; asc K ;;
15: len 11; hex 427573696e657373415049; asc BusinessAPI;;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: len 7; hex 53554343455353; asc SUCCESS;;
27: len 27; hex 5265636f72642055706461746564205375636365737366756c6c79; asc Record Updated Successfully;;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 291891, ACTIVE 4 sec starting index read
mysql tables in use 1, locked 1
22 lock struct(s), heap size 1136, 10 row lock(s), undo log entries 8
MySQL thread id 13972, OS thread handle 47104466163456, query id 4178089 172.29.25.88 bpapi updating
UPDATE APPLICATION SET APPLICATION_NUMBER=IFNULL('001590', APPLICATION_NUMBER), STATUS=IFNULL('1003', STATUS), SUB_STATUS=IFNULL('1003', SUB_STATUS), CRM_STATUS=IFNULL('SUCCESS', CRM_STATUS), CRM_REASON=IFNULL('Record Created Successfully', CRM_REASON) WHERE ID='eddf88a8-c734-4ea3-96e0-9cf424ced71e'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3803 page no 21 n bits 144 index PRIMARY of table `BAPIDB`.`APPLICATION` trx id 291891 lock mode S locks rec but not gap
Record lock, heap no 72 PHYSICAL RECORD: n_fields 42; compact format; info bits 128
0: len 30; hex 65646466383861382d633733342d346561332d393665302d396366343234; asc eddf88a8-c734-4ea3-96e0-9cf424; (total 36 bytes);
1: len 6; hex 000000046e42; asc nB;;
2: len 7; hex 02000000fa0518; asc ;;
3: len 6; hex 303031353930; asc 001590;;
4: len 4; hex 31303033; asc 1003;;
5: len 4; hex 31303033; asc 1003;;
6: SQL NULL;
7: SQL NULL;
8: len 6; hex 506f7274616c; asc Portal;;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: len 30; hex 35633131613436612d303963302d313165612d396533372d396165613961; asc 5c11a46a-09c0-11ea-9e37-9aea9a; (total 36 bytes);
13: SQL NULL;
14: len 5; hex 99a4ac4b92; asc K ;;
15: len 11; hex 427573696e657373415049; asc BusinessAPI;;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: len 7; hex 53554343455353; asc SUCCESS;;
27: len 27; hex 5265636f72642055706461746564205375636365737366756c6c79; asc Record Updated Successfully;;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3803 page no 21 n bits 144 index PRIMARY of table `BAPIDB`.`APPLICATION` trx id 291891 lock_mode X locks rec but not gap waiting
Record lock, heap no 72 PHYSICAL RECORD: n_fields 42; compact format; info bits 128
0: len 30; hex 65646466383861382d633733342d346561332d393665302d396366343234; asc eddf88a8-c734-4ea3-96e0-9cf424; (total 36 bytes);
1: len 6; hex 000000046e42; asc nB;;
2: len 7; hex 02000000fa0518; asc ;;
3: len 6; hex 303031353930; asc 001590;;
4: len 4; hex 31303033; asc 1003;;
5: len 4; hex 31303033; asc 1003;;
6: SQL NULL;
7: SQL NULL;
8: len 6; hex 506f7274616c; asc Portal;;
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: len 30; hex 35633131613436612d303963302d313165612d396533372d396165613961; asc 5c11a46a-09c0-11ea-9e37-9aea9a; (total 36 bytes);
13: SQL NULL;
14: len 5; hex 99a4ac4b92; asc K ;;
15: len 11; hex 427573696e657373415049; asc BusinessAPI;;
16: SQL NULL;
17: SQL NULL;
18: SQL NULL;
19: SQL NULL;
20: SQL NULL;
21: SQL NULL;
22: SQL NULL;
23: SQL NULL;
24: SQL NULL;
25: SQL NULL;
26: len 7; hex 53554343455353; asc SUCCESS;;
27: len 27; hex 5265636f72642055706461746564205375636365737366756c6c79; asc Record Updated Successfully;;
28: SQL NULL;
29: SQL NULL;
30: SQL NULL;
31: SQL NULL;
32: SQL NULL;
33: SQL NULL;
34: SQL NULL;
35: SQL NULL;
36: SQL NULL;
37: SQL NULL;
38: SQL NULL;
39: SQL NULL;
40: SQL NULL;
41: SQL NULL;

*** WE ROLL BACK TRANSACTION (2)

My understanding:
-----------------
UPDATE APPLICATION
SET NO_OF_FINANCIERS_FOR_ALL_ASSET = 7
WHERE id >'' AND APPLICATION_NUMBER = '001601';

This transaction is Causing the dead lock, This is waiting to get lock_mode X.

UPDATE APPLICATION SET APPLICATION_NUMBER=IFNULL('001590', APPLICATION_NUMBER),
STATUS=IFNULL('1003', STATUS), SUB_STATUS=IFNULL('1003', SUB_STATUS),
CRM_STATUS=IFNULL('SUCCESS', CRM_STATUS), CRM_REASON=IFNULL('Record Created Successfully', CRM_REASON)
WHERE ID='eddf88a8-c734-4ea3-96e0-9cf424ced71e';

holds the lock mode S and also trying to hold a lock_mode X which is waiting
The second update is rolled back.

My Questions are:
-----------------
1. Why the second update is holding and lock_mode S ? Shouldn't be lock_mode X is sufficient?
2. These are updating two different rows, and I think that lock_mode S is the main culprit. Am I correct?
3. How to avoid this dead lock.

Regards
Saubhik