3

I have an accounting system in which I try to create transactions at high concurrency (load testing). This results in getting error SequelizeDatabaseError: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction from the database (mysql). As per MySQL it is an expected error, but I am observing a unique behavior in this situation:

  1. The connections to database doesn't get closed after such a deadlock. MySQL show processlist shows these connections stuck in insert/update queries.
  2. These connections doesn't get closed for long long time.
  3. My application timesout getting connection to database for further requests on.
  4. It fails to work afterwards for long long time.

Why is this happening? Ideally Sequelize should close the connection if it gets into an error right? Is this a bug with Sequelize?

vworld4u
  • 89
  • 1
  • 5
  • Open connections are not reasons for deadlock. Deadlocks are a code issue. Really to little code to tell this. Post the code too. – Norbert May 02 '16 at 16:21
  • I know that it is not a library issue to cause the deadlock. But my question is what should be the behaviour of the library when a deadlock happens on a connection? MySQL DB throws an error on which that connection should be closed from application/library right? – vworld4u May 03 '16 at 05:01
  • When a deadlock occurs, MySQL (and most other RDBMS) will opt to kill the process causing the deadlock. This results in an aborted transaction, which the application will then have to handle. You do not have to close a connection, your transaction is just lost. So you might want to restart that transaction and hope that you do not come into the same situation which caused the deadlock in the first place. – Norbert May 03 '16 at 14:37
  • Fair explanation. What happens in my case is - transaction is lost! fine! But after that I can see that all those connections are still active and in sleep/query mode in mysql side (I used show processlist command). These connections never get closed anytime soon (I waited for next 10 hours). But application too becomes unresponsive (this means that it doesn't create further connections to mysql). I can explain this behaviour(not opening new connection) from sequelize side - It is configured to handle max connections of 10 (which are lost now). – vworld4u May 07 '16 at 11:53
  • So it doesn't open new connections. But I do face this problem that sequelize is not opening new connection, but all existing connections are lost in deadlock condition and mysql doesn't close connections! My application doesn't open new connections! Kind of lost completely! – vworld4u May 07 '16 at 11:57

1 Answers1

4

This appears to be a bug with Sequelize reported for MariaDB and MySQL 5.7 as of February 16th, 2020. The code that handles the deadlock condition neglects to call cleanup on the transaction when this particular error condition is detected. Manually calling cleanup on the transaction when the ER_LOCK_DEADLOCK condition is detected seems to be the current work-around.

For example. something like this code would solve the issue.

await sequelize.transaction(options, async t => {
    try {
        // transaction code
    } catch (e) {
        if (e instanceof db.Sequelize.DatabaseError
                && e.parent
                && e.parent.code === "ER_LOCK_DEADLOCK")
            await t.cleanup();
    }
 });

See https://github.com/sequelize/sequelize/issues/11571

planetbeing
  • 310
  • 3
  • 6