Deadlock, что за зверь, с чем едят?

Question

[JimmyTheWorm]

Здравствуйте. Имею небольшой сервер на node.js+mysql. использую движок mysqljs. Сам сервер парсит данные раз в минуту с некоторых сайтов и складывает их в таблицу бд(таблица получается относительно небольшой, 8к строк и около 15 столбцов). А также делает выборку из бд в таблицу html. С некоторого времени стали появляться ошибки "Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction". Погуглив, я понял, что необходимо повторить запрос и ошибка уйдёт. С конкретным запросом то оно вроде пропадает, но тут же появляется новая ошибка лока, уже с другим запросом.
Также гугл подсказал делать commit'ы. Но возникает вопрос. На движке mysqljs вроде как нет возможности отправлять сразу несколько запросов вида

BEGIN;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

И приходится открывать pool, и делать много connection.query() запросов один за одним. Всё это ужасно не удобно.

Есть ли какой-то деликатный способ обхода ER_LOCK_DEADLOCK на nodejs?
Есть ли способ сбросить непроведённые транзакции?
Почему возникают дедлоки? Из-за того что sql не успевает обрабатывать данные? Если да, то поможет ли, если я создам новую таблицу и часть данных перенесу туда?

p.s. забыл написать, проверяю статус через "SHOW ENGINE INNODB STATUS", там выдаёт 3.5к строк текста, в которых написано что 140 локов висит

Comments

[JimmyTheWorm]

Лентюй, спасибо, гуглить я и сам умею. Тем более доки на английском найти вообще ума не надо.

Я задал вопрос здесь, чтобы найти человека, который популярно на пальцах сможет объяснить, в чём проблема и как её решить.

Чтобы вы понимали, я с мускулом всего неделю работаю.

Answer 1

[Anton fon Faust]

Судя по исходным данным, тут не коммиты с транзакциями писать надо, а просто почитать про составление sql-запросов.
Запрос типа

INSERT INTO `table2`
SELECT `type` , SUM(`salary` ) 
FROM `table1`
GROUP BY `type`

UPDATE table2 AS t2 JOIN (
SELECT SUM( salary ) AS summary, 
TYPE FROM table1
GROUP BY TYPE
) AS t1 ON t1.type = t2.type
SET t2.summary = t1.summary WHERE t2.type = t1.type

решит вашу проблему.

Comments

[JimmyTheWorm]

Код в топике я взял из гугла. Вот пример моего кода:

pool.getConnection(function (err, connection) {
                    function clearTempLOOT() {
                        return new Promise((resolveDB, rejectDB) => {
                            connection.query('DELETE FROM temp_loot', function (e, results, fields) {
                                if (e) {
                                    log.error(e);
                                    return rejectDB();
                                }
                                else {
                                    return resolveDB();
                                }
                            });
                        })
                    }

                    function clearAll_prices() {
                        return new Promise((resolveDB, rejectDB) => {
                            connection.query('update all_prices set have_loot = 0 where price_loot is not null', function (e, results, fields) {
                                if (e) {
                                    log.error(e);
                                    return rejectDB('er2');
                                }
                                else {
                                    return resolveDB();
                                }
                            });
                        })
                    }

                    function fillTempLOOT() {
                        return new Promise((resolveDB, rejectDB) => {
                            connection.query('INSERT INTO temp_loot (name, price_loot, have_loot, max_loot,updated_loot) VALUES ?', [arrDB], function (e, results, fields) {
                                if (e) {
                                    log.error(e);
                                    return rejectDB();
                                }
                                else {
                                    return resolveDB();
                                }
                            });
                        })
                    }

                    function addToAll_prices() {
                        return new Promise((resolveDB, rejectDB) => {
                            connection.query("INSERT INTO all_prices(name, price_loot, have_loot, max_loot, updated_loot) (SELECT name, price_loot, have_loot, max_loot, updated_loot FROM `temp_loot` WHERE name NOT IN (SELECT name FROM `all_prices`));", (e, val) => {
                                if (e) {
                                    log.error(e);
                                    return rejectDB();
                                }
                                else {
                                    s = val;
                                    return resolveDB();
                                }
                            });
                        })
                    }

                    function replaceNotRelevant() {
                        return new Promise((resolveDB, rejectDB) => {
                            connection.query("update all_prices a set a.relevant_loot = 0 where a.name in (SELECT name FROM (select * from all_prices) as t1 WHERE name NOT IN (SELECT t.name FROM temp_loot t)); ", (e, val) => {
                                if (e) {
                                    log.error(e);
                                    return rejectDB();
                                }
                                else {
                                    log.info('Replaced %s prices', val.changedRows);
                                    return resolveDB();
                                }
                            });
                        })
                    }

                    function replaceNewPrice() {
                        return new Promise((resolveDB, rejectDB) => {
                            connection.query("UPDATE all_prices a inner JOIN temp_loot t ON t.name = a.name SET a.price_loot = t.price_loot, a.have_loot = t.have_loot, a.max_loot = t.max_loot, a.updated_loot = t.updated_loot, a.relevant_loot = 1 where a.name is not null", (e, val) => {
                                connection.release();
                                if (e) {
                                    log.error(e);
                                    return rejectDB();
                                }
                                else {
                                    log.info('Replaced %s prices', val.changedRows);
                                    return resolveDB();
                                }
                            });
                        })
                    }

                    Promise.all([clearTempLOOT(), clearAll_prices()])
                        .then(result => {
                            return fillTempLOOT();
                        })
                        .then(result => {
                            return addToAll_prices();
                        })
                        .then(result => {
                            return replaceNotRelevant();
                        })
                        .then(result => {
                            return replaceNewPrice();
                        })
                        .then(result => {
                            log.info('Loot db synced');
                            return resolve();
                        })
                        .catch(error => {
                            connection.release();
                            log.error(error);
                            return reject(error);
                        });

                });

т.е. тут присутствует запрос с взятием из другой таблицы данных и записью в основную

INSERT INTO all_prices(name, price_loot, have_loot, max_loot, updated_loot) (SELECT name, price_loot, have_loot, max_loot, updated_loot FROM `temp_loot` WHERE name NOT IN (SELECT name FROM `all_prices`));

Тем не менее, спасибо, что откликнулись

[JimmyTheWorm]

Как видите, тут приходится писать 100500 промисов, чтобы выполнять запросы последовательно. Если пробую объединить их в один connect.query, выдаёт ошибку. Так и должно быть? Неужели все так и делают

[Anton fon Faust]

JimmyTheWorm, ну, не знаю... я бы просто написал хранимую процедуру, которая приняла пару параметров и все сделала - и один промис и все выполнено.

[JimmyTheWorm]

Anton fon Faust, разве это решит проблему с deadlock'ами?

[Anton fon Faust]

JimmyTheWorm, сложно говорить о качестве постройки дома по фотографии форточки на чердаке)))
Имхо, тут архитектура решения хромает.
На сколько я могу предположить, у вас несколько Promise.all([clearTempLOOT(), clearAll_prices()]) одновременно срабатывает.
Вообще, я бы сделал так:
Складывал бы все записи во временную таблицу (в качестве разрешения коллизий ставил бы для каждого сайта свое значение индекса), innodb благополучно позволяет не лочить всю таблицу при записи, а дальше уже из этой таблицы выбирал в рабочие только нужное.

[JimmyTheWorm]

Anton fon Faust, а можно с вами как-то связаться, чтобы в режиме реального времени через чат пообщаться? А то ответов долго ждать.

На сколько я могу предположить, у вас несколько Promise.all([clearTempLOOT(), clearAll_prices()]) одновременно срабатывает.

Тут запрос идёт к двум разным таблицам: к таблице temp_loot и к таблице all_prices.

Складывал бы все записи во временную таблицу

Таблица temp_loot и есть временная. Перед каждой сессией она полностью очищается, потом туда закидываются данные, потом из неё данные перекидываются в all_prices.
Стек подсказывает, что проблема возникает при запросе

INSERT INTO all_prices(name, price_loot, have_loot, max_loot, updated_loot) (SELECT name, price_loot, have_loot, max_loot, updated_loot FROM `temp_loot` WHERE name NOT IN (SELECT name FROM `all_prices`));

, который добавляет в общую таблицу все предметы(товары), которых нет в таблице temp_loot,
либо при запросе

update all_prices a set a.relevant_loot = 0 where a.name in (SELECT name FROM (select * from all_prices) as t1 WHERE name NOT IN (SELECT t.name FROM temp_loot t));

который обновляет все цены в таблице all_prices, беря значения из temp_loot.

Идентификаторов в таблице нет, единственное - столбец name содержит уникальные значения, которые не повторяются. И по этим значениям остальные таблицы добавляют свои цены.
В проекте в сумме 6 временных таблиц, из которых цены перекидываются в основную таблицу.

Вообще, изначально проект был построен на чтении и записи данных в файл. И в момент, когда что-то в многопотоке записывалось и считывалось одновременно возникала ошибка. Для её избежания и для оптимизации решил перейти на mysql, но по-видимому тут ещё больше проблем. Мне казалось sql умнее и сам умеет в очередь всё ставить.
Когда ещё была реализована запись в файлы, была идея создать небольшой сервер, на который бы посылались запросы с чтением и записью из файла, и этот сервер бы сам в очередь ставил запись и чтение. Чтобы только он считывал и записывал и тогда проблем не было бы. Но это довольно муторно. Судя по всему здесь придётся реализовывать что-то подобное?

[Anton fon Faust]

Ну, смотрите...
в первом вашем запросе у вас сразу же дедлок, потому что вы пытаетесь вставлять в таблицу, и то, что вы собираетесь вставлять тут же у вас селектится.
По-хорошему, это делается иначе:
на name вешается unique индекс и производится вставка с игнором.
Типа такого:

INSERT IGNORE INTO all_prices(name, price_loot, have_loot, max_loot, updated_loot) (SELECT name, price_loot, have_loot, max_loot, updated_loot FROM `temp_loot`);

Второй запрос ... это, конечно, жесть)))
Апдейтить все в all_prices, у которых all_prices.name в результатах выборки из результатов выборки из all_prices, за исключением имен из temp_loot).
Я бы его как-то так переписал:

UPDATE all_prices a SET a.relevant_loot = 0 WHERE a.name NOT IN (SELECT t.name FROM temp_loot t);

[JimmyTheWorm]

Anton fon Faust, Просто огромное спасибо! ) буду пробовать внедрять своими кривыми руками))

[JimmyTheWorm]

Anton fon Faust, проект переписал, перезапустил. Посмотрим что получится.
Не знаю, разрешено или нет тут такое, но можете написать свои личные контакты (vk/telegram/whatsapp/что-то ещё), я хочу связаться и отблагодарить. (можно удалить сразу)
либо напишите мне на почту - [e-mail]

[Anton fon Faust]

JimmyTheWorm, отправил на емейл скайп и whatsapp

Answer 2

[Владимир Скибин]

Вам как раз правильно гугл подсказал. Сама идея заключается в том, чтобы брать один коннект из пула и в рамках него уже делать всю транзакцию. Дедлок Вам говорит о том, что несколько запросов пытаются обратиться/изменить к одним и тем же данным. Можете решить этот вопрос при помощи транзакций, только нужно подобрать правильный уровень изоляции. Например, при самом строгом SERIALIZABLE Вам придется повторять транзакцию как раз при получении ошибки синхронизации.
https://dev.mysql.com/doc/refman/5.6/en/innodb-dea...
https://stackoverflow.com/questions/43625542/er-lo...

Comments

[JimmyTheWorm]

Спасибо за ответ.
Про изоляцию не понял, сейчас попробую покопать информацию.

По поводу дедлоков:
Разве sql не умеет запросы в очередь ставить? сам проект у меня построен таким образом, что в разных частях проекта в разное время асинхронно открываются пулы, записываются/читаются данные, потом закрываются соединения. я так понял, что в случае, если совпадает момент записи и чтения одной и той же таблицы, выскакивает блок.

[Владимир Скибин]

JimmyTheWorm, Вот Вы сами и ответили на свой вопрос

если совпадает момент записи и чтения одной и той же таблицы, выскакивает блок.

[JimmyTheWorm]

Владимир Скибин, Можно как-то сбросить зависшие deadlock'и вручную?
Если я отключу autocommit и буду коммитить самостоятельно, я так понял это не решит проблему? Просто она возможно реже будет появляться?
И ещё вопрос. Уровни изоляции - это только про чтение, или про запись тоже?
Если ставлю "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", то ошибка будет чаще выскакивать, но не будет фантомов?

[JimmyTheWorm]

Владимир Скибин, Кстати, у меня встал ещё один вопрос. Почему после полной перезагрузки сервера несколько часов(может дней) нет никакой ошибки, хотя раньше она появлялась каждую минуту. По сути только deadlock'и сбросились, но одновременная запись+чтение как были так и остались, ничего не поменялось

[Владимир Скибин]

JimmyTheWorm, я ж не вкурсе, что у Вас за данные обрабатываются в течение этих гладких часов/дней. Возможно как раз и не задрагиваются, либо проходит проскальзывание, что операция чтение начинается уже тогда, когда операция записи уже закончилась. При использовании транзакций - mysql сам попробует побороть дедлок и будет сообщение вида

Deadlock found when trying to get lock; try restarting transaction

А по ручному закрытию - надо смотреть какие запросы вызвали взаимоблокировку и уже тогда один из них прибивать.

Answer 3

[Олег]

https://ru.wikipedia.org/wiki/%D0%92%D0%B7%D0%B0%D...

поправьте код так, чтобы ресурсы захватывались всегда в порядке а,б
или "Бери ложку и вилку сразу (WaitForMultipleObjects)"