Deadlock, what kind of animal, what do they eat with?

Question

MySQL

Hello. I have a small server on node.js+mysql. I am using mysqljs engine . The server itself parses data once a minute from some sites and adds them to a database table (the table is relatively small, 8k rows and about 15 columns). And also makes a selection from the database to the html table. For some time now, errors "Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction" began to appear. Googling, I realized that it is necessary to repeat the request and the error will go away. With a specific request, it seems to disappear, but then a new lock error appears, already with another request.
Google also prompted me to make commits. But a question arises. On the mysqljs engine, it seems like there is no way to send several queries at once like

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

And you have to open the pool, and do a lot of connection.query() requests one by one. All this is terribly inconvenient.
Is there some delicate way to bypass ER_LOCK_DEADLOCK on nodejs?
Is there a way to reset pending transactions?
Why do deadlocks occur? Due to the fact that sql does not have time to process the data? If so, will it help if I create a new table and transfer some of the data there?
ps I forgot to write, I check the status through "SHOW ENGINE INNODB STATUS", it gives out 3.5k lines of text, which says that 140 locks are hanging

Answer 1

Judging by the initial data, it is not necessary to write commits with transactions here, but simply read about compiling sql queries.
Request type

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

will solve your problem.

Answer 2

Google told you exactly the right thing. The idea itself is to take one connection from the pool and within it already make the entire transaction. Deadlock tells you that several requests are trying to access/change the same data. You can solve this issue with transactions, you just need to choose the right isolation level. For example, with the strictest SERIALIZABLE, you will have to retry the transaction just when you get a synchronization error.
https://dev.mysql.com/doc/refman/5.6/en/innodb-dea...
https://stackoverflow.com/questions/43625542/er-lo...

Answer 3

https://ru.wikipedia.org/wiki/%D0%92%D0%B7%D0%B0%D...
correct the code so that the resources are always captured in the order a,b
or "Take a spoon and a fork at once ( WaitForMultipleObjects)"