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,
UNIQUE KEY `UK_APPLICATION` (`APPLICATION_NUMBER`),
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.
The UPDATE operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.
However, in InnoDB all secondary indexes also include the primary key, so a secondary key lock does affect the primary key as well. But to be honest, if it had an exclusive lock only, that would not change the outcome.
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.
You wrote in a comment that you added the unique index on application_number column after posting your question. This means that your 1st sql statement locks the entire table because id >'' matches all records and application_number field was not indexed.
So, regardless of the fact that your 1st sql statement updates a single record only, you managed to lock the entire table. Which brings us to your last question.
a) handle the deadlock error (restart transaction, error message, etc). b) minimize the chances of a deadlock occuring.
How can you minimize the chances of a deadlock occuring? Use appropriate indexes and where criteria to minimize the number of records locked by a statement; avoid using long running transactions; minimize the use of explicit locking (for update, for share).
Your adding the unique index on application_number is a really good start, but you must verify using explain that it is used by mysql.