MySQL Deadlock found?

Question

[serious911]

Здравствуйте.

У меня есть таблица в MySQL и в ней постоянно возникает ошибка `Deadlock found when trying to get lock; try restarting transaction`.

var sql = "CREATE TABLE IF NOT EXISTS @table ("
	sql += "`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,"
	sql += "`user_id` bigint(20) UNSIGNED NOT NULL,"
	sql += "`peer_id` bigint(20) UNSIGNED NOT NULL,"
	sql += "`sender_id` bigint(20) UNSIGNED NOT NULL,"
	sql += "`time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,"
	sql += "`status` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',"
	sql += "`seen` tinyint(3) UNSIGNED NOT NULL DEFAULT '0',"
	sql += "PRIMARY KEY (id)"
	sql += ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;"

Также в таблице есть индексы:

var sql = "ALTER TABLE @table "
	sql += "ADD KEY `@table_user_id` (`user_id`),"
  	sql += "ADD UNIQUE KEY `@table_user_peer_sender` (`user_id`, `peer_id`, `sender_id`),"
  	sql += "ADD KEY `@table_sender_id` (`sender_id`),"
  	sql += "ADD KEY `@table_time` (`time`),"
  	sql += "ADD KEY `@table_status` (`status`),"
  	sql += "ADD KEY `@table_seen` (`seen`);"

Ошибка возникает во время выполнения именно UPDATE запроса:

UPDATE common_likes SET `seen` = '1' WHERE user_id = '79773' AND status = '2' AND sender_id != '79773';

В таблице выполняются в основном 3 типа запросов: SELECT, INSERT, UPDATE.
INSERT добавляет запись в таблицу с значением `seen` = '0', а UPDATE устанавливает `seen` = '1' . Также выполняються запросы для обновления поля status, но там дедлоки не возникают.
Я не использую транзации, но судя по всему MySQL выполняет каждый запрос в отдельной транзакции.

Подскажите, пожалуйста, как исправить эту ошибку.

Спасибо.

Comments

[mayton2019]

А сколько уникальных значений seen? Если их 2 штуки (0 и 1) то тогда индекс по этому полю не нужен. Он даже вреден.

Deadlock возникает обычно когда ресурсы в транзакции захвачены "крест-накрест". Например я блокирую пользоваля 79773 и потом 79774 а ты делаешь тоже самое наоборот. Так и возникает мёртвое противостояние.

Какая связь с индексом и дедлоком я пока не знаю но попробуй для начала прибить лишний индекс.

[serious911]

mayton2019, Значений seen всего 2: 0 и 1. Индекс добавил так как есть еще запросы для определения количества непросмотренных записей:

SELECT COUNT(`id`) AS `counter` FROM table WHERE user_id = '79773' AND sender_id != '79773' AND status = '1' AND seen = '0';

[Slava Rozhnev]

SHOW ENGINE INNODB STATUS; из клиента командной строки MySQL предоставит вам информацию о deadlock - ах.

[serious911]

Slava Rozhnev, смотрел эту информацию и там этот же запрос:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-16 04:41:00 0x7f05cc3f7700
*** (1) TRANSACTION:
TRANSACTION 130522343, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 96932, OS thread handle 139662906558208, query id 77554557 127.0.0.1 Updating
<b>UPDATE common_likes SET `seen` = '1' WHERE user_id = '79773' AND status = '2' AND sender_id != '79773'</b>
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 60 page no 10 n bits 336 index PRIMARY of table `common_likes` trx id 130522343 lock_mode X locks rec but not gap waiting
Record lock, heap no 78 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
 0: len 8; hex 0000000000000063; asc        c;;
 1: len 6; hex 000000000000; asc       ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 8; hex 0000000000004328; asc       C(;;
 4: len 8; hex 000000000000a496; asc         ;;
 5: len 8; hex 0000000000004328; asc       C(;;
 6: len 5; hex 99aca08e59; asc     Y;;
 7: len 1; hex 02; asc  ;;
 8: len 1; hex 00; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 130522295, ACTIVE 2 sec fetching rows
mysql tables in use 1, locked 1
5512 lock struct(s), heap size 761976, 1371587 row lock(s)
MySQL thread id 96931, OS thread handle 139662878275328, query id 77554513 127.0.0.1 Updating
DELETE FROM common_likes WHERE peer_id = '71663'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 60 page no 10 n bits 336 index PRIMARY of table `common_likes` trx id 130522295 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

[Slava Rozhnev]

UPDATE common_likes SET `seen` = '1' WHERE user_id = '79773' AND status = '2' AND sender_id != '79773'
locked by
DELETE FROM common_likes WHERE peer_id = '71663'

Have you index on peer_id field?

[serious911]

Slava Rozhnev, есть уникальный индекс на 3 поля:

ADD UNIQUE KEY `@table_user_peer_sender` (`user_id`, `peer_id`, `sender_id`)

Сейчас заменил UNIQUE на Primary по 3 полям PRIMARY KEY (user_id, peer_id, sender_id).

Нужно добавить еще отдельный индекс для peer_id и sender_id?

[Slava Rozhnev]

try EXPLAIN SELECT * FROM common_likes WHERE peer_id = '71663'; and look which index used
also
EXPLAIN SELECT * FROM WHERE user_id = '79773' AND status = '2' AND sender_id != '79773';

[serious911]

EXPLAIN SELECT * FROM `common_likes` WHERE user_id = '1' AND sender_id != '1' AND status = '2';
+------+-------------+--------------+------+-----------------------------+---------+---------+-------+------+-------------+
| id   | select_type | table        | type | possible_keys               | key     | key_len | ref   | rows | Extra       |
+------+-------------+--------------+------+-----------------------------+---------+---------+-------+------+-------------+
|    1 | SIMPLE      | common_likes | ref  | PRIMARY,common_likes_status | PRIMARY | 8       | const |    1 | Using where |
+------+-------------+--------------+------+-----------------------------+---------+---------+-------+------+-------------+

EXPLAIN SELECT * FROM common_likes WHERE peer_id = '71663'; 
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | common_likes | ALL  | NULL          | NULL | NULL    | NULL | 7004 | Using where |
+------+-------------+--------------+------+---------------+------+---------+------+------+-------------+

In the first case PRIMARY,common_likes_status indexes are used. And in the second case no indexes are used.

So looks like I need to create a separate index for peer_id and sender_id fields.