I have a table with millions of rows and I have to use count it divided by groups.
CREATE TABLE `customers` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`group_id` INT(10) UNSIGNED NULL DEFAULT NULL
)
so calls I made very often are
SELECT COUNT(*) FROM customers WHERE group_id=XXX
But unfortunately MySQL is really slow (>10 sec for one call) when counting in tables with dozens millions of rows.
So I decided to create a new table to keep counters only:
CREATE TABLE `customer_stats` (
`group_id` INT(11) NOT NULL,
`value` INT(11) NOT NULL,
)
where I can keep current counters and make sure it's up to date using triggers.
So I have a triggers for insert/update/delete, here's example of insert one:
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
and it works fine for most cases, but on high load (dozens of calls per seconds) I got deadlocks.
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)
It exists only on high load and I wonder if there is some easy way to change the trigger to make sure, that they don't try to execute that UPDATE customer_stats
in the same time, as this is causing deadlock. So two customer record must be created in the same time to raise deadlock.
Tables and system of triggers I have is a little bit more complicated, but I tried to simplify it as much I can to explain you what is my problem.