Mysql: Deadlock found when trying to get lock; try restarting transaction

Question

[AntonioK]

Таблица:

CREATE TABLE `counter_countries_rotates` (
  `country_id` int(11) unsigned NOT NULL,
  `date` date NOT NULL,
  `count` int(11) unsigned NOT NULL DEFAULT '0',
  UNIQUE KEY `UK_country_date` (`country_id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

В таблице лежат счётчики «сколько было показов в сутки по странам». Счётчики постоянно обновляются запросом вида:

INSERT INTO
`counter_countries_rotates`
SET
`country_id` = '20',
`count` = 1,
`date` = '2012-09-06'
ON DUPLICATE KEY UPDATE `count` = `count` + 1

Удалений из этой таблицы не бывает.

До поры до времени всё работает хорошо, но когда количество апдейтов одной и той же строчки в секунду превышает некое пороговое значение, запросы отваливаются с ошибкой:

Deadlock found when trying to get lock; try restarting transaction

ЧЯДНТ? Может быть стоит как-то задействовать PRIMARY индекс?

Server version: 5.1.65-log FreeBSD port: mysql-server-5.1.65

Answer 1

[AntonioK]

Проблема решена отчасти обработкой дедлоков в приложении, работающим с БД (оно пытается повторить последний запрос и продолжить работу в транзакции), и в наибольшей степени — отказом от UNIQUE KEY `UK_country_date` (`country_id`,`date`)
в пользу PRIMARY KEY (`country_id`,`date`).

Спасибо всем!

Answer 2

[Melkij]

Могу ошибаться, но, думаю, поможет предварительное заполнение таблицы значением count=0, а потом чистый update. По желанию, чистка прошедшего дня от нулевых записей.

PS: если country_id — это страны, то зачем там 32-битный int? Вы с другой планеты?

Comments

[AntonioK]

В таблице-справочнике стран, на которую ссылаемся, tinyint(3), а здесь проглядели. Исправил, спасибо!

Answer 3

[fred]

Как вариант вместо update'a инсертить новую запись с неким полем = 1, и считать сумму по этому полю, ночью или когда загрузка маленькая, собирать все записи за день в одну установив в не поле = sum() схлапываемых ааписей.
Не знаю на сколько это эффективное решение, у меня один сервис так работает, с похожим функционалом. дедлоки ушли.

Comments

[AntonioK]

Увы, не подходит — сумму надо знать в любой момент времени, а не только «после ночного пересчёта». Архитектура подразумевает именно инкрементные «счётчики».

[fred]

Не, понятно что в любой момент.
На пальцах примерно так
Начинаем работу — табличка пустая
на каждое увеличение счетчика вставляем запись
qty
— 1
1
1
Узнать текущее значение счетчика — select sum(qty) from counterTable
Ночью в одной транзакции
fullQty = select sum(qty)
delete from counterTable
insert into counterTable values (fullQty)

на следующий день после увеличения счетчика получаем
qty
— 3
1
1

текущее значение счетчика так же — select sum(qty)

Здесь получается что хоть и незначительно увеличивается получение счетчика. уходят блокировки на вставку.

Answer 4

[vsespb]

поможет вызов get_lock до и release_lock после операции (у всех клиентов). но будет медленнее.

Answer 5

[mayorovp]

Вам же все написали:
Deadlock found when trying to get lock; try restarting transaction

Если возникла эта ошибка — надо попытаться выполнить операцию еще раз, вот и все.

PS но я бы вместо этого странного INSERT написал самый обычный UPDATE, а в случае нулевого числа обновленных строк делал бы INSERT. По-идее, это должно дать возможность серверу сразу повесить нужную блокировку. Но не факт, тут надо пробовать оба варианта.

Answer 6

[Алексей Акулович]

Если много одновременных инсертов, то может стоит подумать о варианте, только одного пишушего. Складывать логи счетчика до инсерта, и пусть он их разгребает.
Еще вариант, если логи приходят с многих машин, то аггрегировать на них в течение N секунд/минут, а потом уже накопившеется скидывать гораздо меньшим числом одновременных коннектов. Но судя по вашему комменту «сумму надо знать в любой момент времени», вам это не очень подходит.
[offtopic]Ну и как совсем уж альтернатива, складывать в какой угодно nosql.[/offtopic]