Понимание взаимоблокировок в MySQL

Я новичок в MySQL, раньше работал с базой данных Oracle. У меня возникли проблемы с разрешением взаимоблокировок в моем приложении. Пожалуйста, помогите мне понять проблему. Определение таблицы:

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)

Мое понимание:

UPDATE APPLICATION
SET NO_OF_FINANCIERS_FOR_ALL_ASSET = 7
WHERE id >'' AND APPLICATION_NUMBER = '001601';

Эта транзакция вызывает тупиковую блокировку, она ожидает получения 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';

удерживает режим блокировки S, а также пытается удерживать lock_mode X, который ожидает. Второе обновление откатывается.

Мои вопросы:

Почему второе обновление держится и lock_mode S ? Не должно быть достаточно lock_mode X?
Они обновляют две разные строки, и я думаю, что основным виновником является lock_mode S. Я прав?
Как избежать этой мертвой блокировки.

mysql deadlock database-deadlocks

Saubhik Banerjee 22.11.2019 источник

comment

Этот бит правильный: WHERE id >''? Также было бы неплохо узнать, какой движок базы данных вы используете. ИнноДБ? - KIKO Software 22.11.2019

comment

Да, столбец id является первичным ключом, и без него он выдавал ошибку о безопасном обновлении. Уникальное ограничение для application_number добавлено после этого поста. Предположим, что его там не было. Данные моей базы данных: MySQL: 8.0.15, Engine: InnoDB, V. 10 - Saubhik Banerjee 22.11.2019

comment

Ой. Это означает, что ваше первое обновление заблокировало всю таблицу... - Shadow 22.11.2019

Ответы (1)

arrow_upward
0
arrow_downward

Операторы обновления могут устанавливать общие блокировки для вторичных индексов, или во вторых транзакциях есть другие операторы, которые устанавливают общую блокировку перед обновлением. Как сказано в руководстве по mysql:

Операция UPDATE также устанавливает общие блокировки затронутых записей вторичного индекса при выполнении сканирования с проверкой дубликатов перед вставкой новых записей вторичного индекса и при вставке новых записей вторичного индекса.

Однако в InnoDB все вторичные индексы также включают первичный ключ, поэтому блокировка вторичного ключа влияет и на первичный ключ. Но, честно говоря, если бы у него была только эксклюзивная блокировка, это не изменило бы результат.

Нет, вы не правы. В 1-й ссылке руководства mysql также говорится:

Чтение с блокировкой, UPDATE или DELETE обычно устанавливают блокировки записей для каждой записи индекса, просматриваемой при обработке инструкции SQL. Неважно, есть ли в операторе условия WHERE, исключающие строку. InnoDB не запоминает точное условие WHERE, а знает только, какие диапазоны индексов были просканированы.

Вы написали в комментарии, что добавили уникальный индекс в столбец application_number после публикации своего вопроса. Это означает, что ваш 1-й оператор sql блокирует всю таблицу, потому что id >'' соответствует всем записям, а поле application_number не было проиндексировано.

Таким образом, несмотря на то, что ваш 1-й оператор sql обновляет только одну запись, вам удалось заблокировать всю таблицу. Что подводит нас к вашему последнему вопросу.

Вы не можете и не должны избегать взаимоблокировок: это важная особенность разрешения определенных условий гонки. Есть две вещи, которые вы можете сделать:

а) обработать ошибку взаимоблокировки (перезапустить транзакцию, сообщение об ошибке и т. д.). б) свести к минимуму вероятность возникновения взаимоблокировки.

Как можно свести к минимуму вероятность возникновения взаимоблокировки? Используйте соответствующие индексы и критерии, чтобы свести к минимуму количество записей, заблокированных оператором; избегайте использования длительных транзакций; свести к минимуму использование явной блокировки (для обновления, для общего доступа).

Ваше добавление уникального индекса в application_number — действительно хорошее начало, но вы должны убедиться, используя explain, что он используется mysql.

Shadow 22.11.2019

comment

Спасибо и fotiosp, и Shadow (вспоминает меня о Blue Shadow на форуме Oracle). Теперь я понял, и после того, как я сделал этот пост, я добавил уникальный индекс, и это помогло. Теперь этот конкретный тупик не возникает во время нагрузочного тестирования. Еще раз спасибо, я очень ценю. - Saubhik Banerjee; 22.11.2019

Понимание взаимоблокировок в MySQL

Мои вопросы:

Ответы (1)

Похожие вопросы