GitHub 1.8k ЗвездаПуть к тому, чтобы стать Java-инженером, почему бы тебе не прийти и не узнать?
GitHub 1.8k ЗвездаПуть к тому, чтобы стать Java-инженером, ты правда не хочешь узнать?
GitHub 1.8k ЗвездаПуть к тому, чтобы стать Java-инженером, ты действительно уверен, что не хочешь узнать?
Феномен
Однажды ночью коллега работал над публикацией, и внезапно в сеть было отправлено большое количество сигналов тревоги, многие из которых касались взаимоблокировок базы данных.
{"errorCode":"SYSTEM_ERROR","errorMsg":"nested exception is org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: ERR-CODE: [TDDL-4614][ERR_EXECUTE_ON_MYSQL]
Deadlock found when trying to get lock;
The error occurred while setting parameters\n### SQL:
update fund_transfer_stream set gmt_modified=now(),state = ? where fund_transfer_order_no = ? and seller_id = ? and state = 'NEW'
С помощью аварийного сигнала мы можем в основном найти базу данных и таблицу базы данных, где возникает взаимоблокировка. Во-первых, давайте представим информацию, связанную с базой данных, задействованную в этом случае.
задний план
Используемая нами база данных — Mysql 5.7, движок — InnoDB, а уровень изоляции транзакций — READ-COMMITED.
Метод запроса версии базы данных:
SELECT version();
Метод запроса двигателя:
show create table fund_transfer_stream;
Информация о механизме хранения будет отображаться в операторе создания таблицы, например:ENGINE=InnoDB
Метод запроса уровня изоляции транзакции:
select @@tx_isolation;
Способ установки уровня изоляции транзакции (действителен только для текущей сессии):
set session transaction isolation level read committed;
PS: Обратите внимание, что если база данных является подсиблиотекой, более нескольких операторов SQL необходимо выполнить в одной базе данных, не выполняйте логическую библиотеку.
Структура таблицы взаимоблокировки и ситуация с индексом (некоторые нерелевантные поля и индексы скрыты):
CREATE TABLE `fund_transfer_stream` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`gmt_create` datetime NOT NULL COMMENT '创建时间',
`gmt_modified` datetime NOT NULL COMMENT '修改时间',
`pay_scene_name` varchar(256) NOT NULL COMMENT '支付场景名称',
`pay_scene_version` varchar(256) DEFAULT NULL COMMENT '支付场景版本',
`identifier` varchar(256) NOT NULL COMMENT '唯一性标识',
`seller_id` varchar(64) NOT NULL COMMENT '卖家Id',
`state` varchar(64) DEFAULT NULL COMMENT '状态', `fund_transfer_order_no` varchar(256)
DEFAULT NULL COMMENT '资金平台返回的状态',
PRIMARY KEY (`id`),UNIQUE KEY `uk_scene_identifier`
(KEY `idx_seller` (`seller_id`),
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='资金流水';
База данных имеет три индекса, один кластеризованный индекс (индекс первичного ключа) и два некластеризованных индекса (индекс непервичного ключа).
Кластерный индекс:
PRIMARY KEY (`id`)
Некластеризованный индекс:
KEY `idx_seller` (`seller_id`),
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
Вышеуказанные два индекса, на самом деле, idx_seller_transNo уже охватывают idx_seller.По историческим причинам, поскольку таблица разделена на таблицы по номеру продавца, сначала идет idx_seller, а затем idx_seller_transNo.
журнал взаимоблокировок
Когда в базе данных возникает взаимоблокировка, журнал взаимоблокировки можно получить с помощью следующей команды:
show engine innodb status
При возникновении взаимоблокировки как можно скорее проверьте журнал взаимоблокировок и получите содержимое журнала взаимоблокировок следующим образом:
Transactions deadlock detected, dumping detailed information.
2019-03-19T21:44:23.516263+08:00 5877341 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 173268495, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 304 lock struct(s), heap size 41168, 6 row lock(s), undo log entries 1
MySQL thread id 5877358, OS thread handle 47356539049728, query id 557970181 11.183.244.150 fin_instant_app updating
update `fund_transfer_stream` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))
2019-03-19T21:44:23.516321+08:00 5877341 [Note] InnoDB:
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap
Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2019-03-19T21:44:23.516565+08:00 5877341 [Note] InnoDB:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `xxx`.`fund_transfer_stream` trx id 173268495 lock_mode X locks rec but not gap waiting
Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
2019-03-19T21:44:23.517793+08:00 5877341 [Note] InnoDB:
*** (2) TRANSACTION:
TRANSACTION 173268500, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 81
mysql tables in use 1, locked 1
302 lock struct(s), heap size 41168, 2 row lock(s), undo log entries 1
MySQL thread id 5877341, OS thread handle 47362313119488, query id 557970189 11.131.81.107 fin_instant_app updating
update `fund_transfer_stream_0056` set `gmt_modified` = NOW(), `state` = 'PROCESSING' where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))
2019-03-19T21:44:23.517855+08:00 5877341 [Note] InnoDB:
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 173 page no 12416 n bits 128 index PRIMARY of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap
Record lock, heap no 56 PHYSICAL RECORD: n_fields 17; compact format; info bits 0
2019-03-19T21:44:23.519053+08:00 5877341 [Note] InnoDB:
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 173 page no 13726 n bits 248 index idx_seller_transNo of table `fin_instant_0003`.`fund_transfer_stream_0056` trx id 173268500 lock_mode X locks rec but not gap waiting
Record lock, heap no 168 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
2019-03-19T21:44:23.519297+08:00 5877341 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
Кратко интерпретируя журнал взаимоблокировок, вы можете получить следующую информацию:
1. Две инструкции SQL, вызывающие взаимоблокировку:
update `fund_transfer_stream_0056`
set `gmt_modified` = NOW(), `state` = 'PROCESSING'
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031957477256'))
и
update `fund_transfer_stream_0056`
set `gmt_modified` = NOW(), `state` = 'PROCESSING'
where ((`state` = 'NEW') AND (`seller_id` = '38921111') AND (`fund_transfer_order_no` = '99010015000805619031958363857'))
2. Транзакция 1, удерживающая блокировку индекса idx_seller_transNo, ожидает получения ПЕРВИЧНОЙ блокировки.
3. Транзакция 2, удерживающая ПЕРВИЧНУЮ блокировку, ожидает получения блокировки idx_seller_transNo.
4. Тупик возникает из-за циклического ожидания между транзакциями 1 и 2.
В то же время, имеющее ресурс и ожидание возникновения мертвых замков через облако DBA, можно найти ниже, и информация, полученная в журнале тупика, в основном соответствует.
5, блокировки транзакции 1 и транзакции 2 в настоящее время удерживаются:lock_mode X locks rec but not gap
Обе транзакции добавляют к записям блокировки X и блокировки без промежутков, то есть блокируют текущие записи строк и добавляют блокировки промежутков.
Блокировка X: эксклюзивная блокировка, также известная как блокировка записи. Если транзакция T добавляет блокировку X к объекту данных A, транзакция T может читать A или изменять A, а другие транзакции не могут добавлять какие-либо блокировки к A до тех пор, пока T не снимет блокировку с A. Это гарантирует, что другие транзакции больше не смогут читать и изменять A, пока T не снимет блокировку с A.
Ему соответствует блокировка S: общая блокировка, также известная как блокировка чтения.Если транзакция T добавляет блокировку S к объекту данных A, транзакция T может читать A, но не может изменять A, а другие транзакции могут только добавлять блокировку S к A. X блокировка не может быть добавлена до тех пор, пока T не снимет блокировку S на A. Это гарантирует, что другие транзакции могут читать A, но не могут вносить какие-либо изменения в A, пока T не снимет блокировку S на A.
Блокировка промежутка: блокировка промежутка, блокирует диапазон, но не включает саму запись. Блокировка GAP предназначена для предотвращения фантомного чтения двух текущих операций чтения одной и той же транзакции.
Next-Key Lock: 1+2, блокирует диапазон и блокирует саму запись. Для запроса строки используется этот метод, основная цель которого — решить проблему фантомного чтения.
Видеть:Блог Woohoo.cn on.com/Zhou Jinyi/Боюсь…,Dev.MySQL.com/doc/Furious/…
Устранение неполадок
Основываясь на имеющейся у нас информации о базе данных и журнале взаимоблокировок, мы можем сделать несколько простых суждений.
Во-первых,Этот тупик не должен иметь ничего общего с блокировкой Gap и Next-Key Lock.. Поскольку уровень изоляции нашей базы данных — RC (READ-COMMITED), этот уровень изоляции не будет добавлять блокировки Gap. В предыдущем журнале взаимоблокировок также упоминается об этом.
Затем пришло время просмотреть код и посмотреть, как выполняется транзакция в нашем коде. Основной код и SQL следующие:
@Transactional(rollbackFor = Exception.class)
public int doProcessing(String sellerId, Long id, String fundTransferOrderNo) {
fundTreansferStreamDAO.updateFundStreamId(sellerId, id, fundTransferOrderNo);
return fundTreansferStreamDAO.updateStatus(sellerId, fundTransferOrderNo, FundTransferStreamState.PROCESSING.name());
}
Целью этого кода является последовательное изменение двух разных полей одной и той же записи, updateFundStreamId SQL:
update fund_transfer_stream
set gmt_modified=now(),fund_transfer_order_no = #{fundTransferOrderNo}
where id = #{id} and seller_id = #{sellerId}
SQL-запрос состояния обновления:
update fund_transfer_stream
set gmt_modified=now(),state = #{state}
where fund_transfer_order_no = #{fundTransferOrderNo} and seller_id = #{sellerId}
and state = 'NEW'
Как видите, в одной транзакции выполняются два оператора Update. Вот планы выполнения следующих двух SQL-запросов:
ПЕРВИЧНЫЙ индекс используется при выполнении updateFundStreamId.
Индекс idx_seller_transNo используется при выполнении updateStatus.
Через план выполнения мы обнаружили, что updateStatus на самом деле имеет два доступных индекса, а индекс idx_seller_transNo фактически используется во время выполнения. Это потому чтоОптимизатор запросов MySQL — это метод запросов, основанный на затратах. Поэтому в процессе запроса наиболее важной частью является расчет стоимости, необходимой для запроса, в соответствии с оператором SQL запроса и различными индексами, чтобы выбрать оптимальный метод индекса для создания плана запроса.
План выполнения нашего запроса составляется после возникновения взаимоблокировки, а план выполнения посмертного запроса и использования индекса в момент взаимоблокировки не обязательно совпадают. Однако в сочетании с журналом взаимоблокировок мы также можем найти индексы, используемые при выполнении двух приведенных выше операторов SQL. которыйИндекс PRIMARY используется при выполнении updateFundStreamId, а индекс idx_seller_transNo используется при выполнении updateStatus..
Имея известную выше информацию, мы можем начать исследовать причину взаимоблокировки и ее обоснование. Проанализировав журнал взаимоблокировок в сочетании с нашим кодом и оператором построения таблицы базы данных, мы обнаружили, что основная проблема заключается в нашем индексе idx_seller_transNo:
KEY `idx_seller_transNo` (`seller_id`,`fund_transfer_order_no`(20))
Заявление о создании индекса, мы используем индекс префикса, индекс для экономии места и повышения эффективности индекса, мы выбираем только 20 лучших в качестве значения индекса fund_transfer_order_no поле.
Потому что fund_transfer_order_no — это обычный, а не уникальный индекс. И потому что в особом случае первые 20 цифр двух fund_transfer_order_nos одного и того же пользователя совпадают, что приводит к одному и тому же значению индекса двух разных записей (поскольку продавец_id и fund_transfer_order_no(20) одинаковы).
Как и в примере из этой статьи, значение поля fund_transfer_order_no в двух записях, в которых возникает взаимоблокировка: 99010015000805619031958363857 и 99010015000805619031957477256, одинаковы в первых 20 битах.
Так почему же одни и те же первые 20 битов fund_transfer_order_no вызывают взаимоблокировку?
Принцип блокировки
Давайте возьмем этот случай, чтобы увидеть, каков принцип блокировки базы данных MySql и что произошло за взаимоблокировкой в этой статье.
Мы моделируем сценарий взаимоблокировки в базе данных, выполняем следующую последовательность:
мы знаем,В MySQL блокировки на уровне строк блокируют не записи, а индексы. Индексы делятся на индексы с первичным ключом и индексы с непервичным ключом.Если оператор SQL оперирует с индексом с первичным ключом, MySQL блокирует индекс с первичным ключом; индекс первичного ключа, а затем заблокировать индекс непервичного ключа.Связанный индекс первичного ключа.
Листовые узлы индекса первичного ключа хранят всю строку данных. В InnoDB индекс первичного ключа также известен как кластерный индекс (clustered index).
Содержимое листовых узлов индекса непервичного ключа является значением первичного ключа.В InnoDB индекс непервичного ключа также называется некластеризованным индексом (вторичным индексом).
Таким образом, структура индекса, задействованная в примере в этой статье (индекс представляет собой дерево B+, упрощенное до таблицы), показана на рисунке:
Возникновение взаимоблокировки не зависит от количества операторов SQL в транзакции Ключом к взаимоблокировке является то, что порядок блокировки двух (или более) сеансов несовместим. **Тогда давайте посмотрим на последовательность блокировки двух транзакций в приведенном выше примере:
На следующем рисунке показано покомпонентное представление ситуации блокировки при выполнении каждого SQL:
Объединив два приведенных выше рисунка, мы нашли причину взаимоблокировки: транзакция 1 выполняет update1, чтобы занять блокировку PRIMARY = 1 -> транзакция 2 выполняет update1, чтобы занять блокировку PRIMARY = 2; транзакция 1 выполняет update2, чтобы занять idx_seller_transNo = ( 3111095611, 99010015000805619031) попытка удержать блокировку PRIMARY = 2 не удалась (блокировка); транзакция 2 выполняет update2 и пытается удержать блокировку с idx_seller_transNo = (3111095611, 99010015000805619031) и терпит неудачу (тупиковая блокировка);
Когда транзакция использует индекс непервичного ключа в качестве условия обновления, она сначала блокирует индекс непервичного ключа, затем запрашивает соответствующие индексы первичного ключа индекса непервичного ключа, а затем блокирует эти индексы первичного ключа. . )
Решение
До сих пор мы анализировали фундаментальные принципы, которые приводят к взаимоблокировкам, и принципы, лежащие в их основе. Тогда эту проблему решить несложно.
Вы можете начать с двух аспектов, а именно с изменения индекса и изменения кода (включая операторы SQL).
Измените индекс: пока мы изменяем длину префикса fund_transfer_order_no в индексе префикса idx_seller_transNo, этого достаточно. Например, изменить его на 50. Тупиковую ситуацию можно избежать.
Однако после изменения длины префикса idx_seller_transNo необходимым условием для выхода из тупика является использование индекса fund_transfer_order_no при фактическом выполнении оператора обновления. Если оптимизатор запросов MySQL решит использовать индекс KEY idx_seller(seller_id) после анализа затрат, все равно возникнет проблема взаимоблокировки. Принцип аналогичен этой статье.
Итак, принципиальное решение — изменить код:
* 所有update都通过主键ID进行。
* 在同一个事务中，避免出现多条update语句修改同一条记录。
Резюме и размышления
В течение недели после возникновения тупиковой ситуации я уделял некоторое время изучению почти каждый день.Проблема была обнаружена рано, и план модификации также был доступен, но принцип был не ясен.
Я сделал много выводов и предположений до и после, но я снова и снова опровергал их. В конце концов, вам придется полагаться на практику, чтобы проверить свои собственные идеи. Поэтому я установил базу данных локально, провел несколько реальных тестов и проверил ситуацию с блокировкой базы данных в режиме реального времени.show engine innodb status ;Вы можете проверить состояние блокировки. Наконец-то понял почему.
Всего несколько мыслей:
1. Если у вас возникнут проблемы, не гадайте! ! ! Воспроизведите проблему вручную, а затем снова проанализируйте ее.
2. Не игнорируйте контекст! ! ! Вначале я обращал внимание только на журнал взаимоблокировок и продолжал игнорировать тот факт, что транзакция в коде фактически выполняла другой оператор SQL (updateFundStreamId).
3. Какими бы достаточными ни были теоретические знания, вы можете не успеть подумать об этом в критический момент! ! !
4. Ямы закапываются сами собой! ! !
Если есть способ, но нет искусства, искусство может быть достигнуто; если есть искусство, но нет пути, оно заканчивается на пути; добро пожаловать, чтобы обратить внимание на публичный аккаунт [Java Way], давайте воспользуемся способом, чтобы овладеть искусством и знать путь с искусством;
Использованная литература:Анализ обработки блокировки MySQL
уровень изоляции транзакций innodb
Блокировки на уровне строки, блокировки на уровне таблицы, блокировки на уровне страницы в MySQL