MySQL — дважды вызванный триггер вызывает тупик
У меня есть таблица с миллионами строк, и я должен использовать счетчик, разделенный на группы.
CREATE TABLE `customers` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`group_id` INT(10) UNSIGNED NULL DEFAULT NULL
)
поэтому звонки, которые я сделал очень часто
SELECT COUNT(*) FROM customers WHERE group_id=XXX
Но, к сожалению, MySQL работает очень медленно (> 10 секунд на один вызов) при подсчете в таблицах с десятками миллионов строк.
Поэтому я решил создать новую таблицу для хранения только счетчиков:
CREATE TABLE `customer_stats` (
`group_id` INT(11) NOT NULL,
`value` INT(11) NOT NULL,
)
где я могу сохранить текущие счетчики и убедиться, что он обновляется с помощью триггеров.
Итак, у меня есть триггеры для вставки / обновления / удаления, вот пример вставки один:
CREATE TRIGGER `customers_insert` AFTER INSERT ON `customers` FOR EACH ROW
BEGIN
UPDATE customer_stats
SET
`value` = `value` + 1
WHERE
customer_stats.group_id = NEW.group_id;
END
и в большинстве случаев работает нормально, но при высокой нагрузке (десятки вызовов в секунду) у меня возникли тупики.
2016-09-21T20:14:30.639907Z 2057 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2016-09-21T20:14:30.639926Z 2057 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 10390, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 10 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 2059, OS thread handle 140376644818688, query id 85330 test_test-php-fpm_1.test_default 172.19.0.12 root updating
UPDATE customer_stats
SET
`value` = `value` + 1
WHERE
customer_stats.group_id = NEW.group_id;
2016-09-21T20:14:30.639968Z 2057 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 85 page no 3 n bits 72 index customer_stats_key_group_id_unique of table `test`.`customer_stats` trx id 10390 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 21; hex 637573746f6d657264657461696c735f636f756e74; asc customerdetails_count;;
1: len 4; hex 80000002; asc ;;
2: len 6; hex 000000002890; asc ( ;;
3: len 7; hex 34000002341224; asc 4 4 $;;
4: len 4; hex 80000666; asc f;;
2016-09-21T20:14:30.640302Z 2057 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 10391, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
10 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 2057, OS thread handle 140376513820416, query id 85333 test_test-php-fpm_1.test_default 172.19.0.12 root updating
UPDATE customer_stats
SET
`value` = `value` + 1
WHERE
customer_stats.group_id = NEW.group_id;
2016-09-21T20:14:30.640334Z 2057 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
2016-09-21T20:14:30.640850Z 2057 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
Он существует только при высокой нагрузке, и мне интересно, есть ли какой-нибудь простой способ изменить триггер, чтобы убедиться, что они не пытаются выполнить это UPDATE customer_stats в то же время, как это вызывает тупик. Таким образом, две записи клиента должны быть созданы одновременно, чтобы вызвать тупик.
У меня есть немного более сложные таблицы и система триггеров, но я постарался максимально упростить их, чтобы объяснить вам, в чем заключается моя проблема.
Решение
Вам нужен композит INDEX(key, group_id)в любом порядке.
Давайте упростим триггер: Шаг 1: VALUES проще чем SELECT:
BEGIN
DECLARE originalGroupId INT;
SET originalGroupId = NEW.group_id;
INSERT IGNORE INTO table_stats(`key`, value, group_id)
VALUES ('customers_count', 0, originalGroupId); -- line changed
UPDATE table_stats
SET
table_stats.`value` = table_stats.`value` + 1
WHERE
table_stats.`key` = "customers_count"AND table_stats.group_id = originalGroupId;
END
Шаг 2: Используйте IODKU. На данный момент, это должно быть UNIQUE(key, group_id)в любом порядке.
BEGIN
DECLARE originalGroupId INT;
SET originalGroupId = NEW.group_id;
INSERT INTO table_stats(`key`, value, group_id)
VALUES ('customers_count', 1, originalGroupId) -- note 1 not 0
ON DUPLICATE KEY UPDATE
`value` = `value` + 1;
END
Шаги 1 и 2 заставляют его работать быстрее, тем самым уменьшая частоту взаимоблокировок.
Шаг 3: справиться с тупиками! Они есть не полностью предотвратимо. Так что планируйте повторять всю транзакцию всякий раз, когда возникает тупик.
Другие решения
хорошо, я думаю, я выяснил, в чем проблема.
Я попытался упростить задачу, чтобы показать вам ее здесь, но, похоже, после того, как я упростил ее — проблема больше не существовала.
Мой оригинальный триггер был:
BEGIN
DECLARE originalGroupId INT;
SET originalGroupId = NEW.group_id;
INSERT IGNORE INTO table_stats(`key`, value, group_id)
SELECT 'customers_count', 0, originalGroupId;
UPDATE table_stats
SET
table_stats.`value` = table_stats.`value` + 1
WHERE
table_stats.`key` = "customers_count"AND table_stats.group_id = originalGroupId;
END
и я похоже, что тупик вызван INSERT IGNORE или переменная, как когда я его удалил — он начал работать без каких-либо проблем. Спасибо!