I'm dealing with a promblem for a couple of days, and I'm really hoping, you could help me.

It's a node.js based API using sequelize for MySQL.

On certain API calls the code starts SQL transactions which lock certain tables, and if I send multiple requests to the API simultaneously, I got LOCK_WAIT_TIMEOUT errors.

var SQLProcess = function () {
    var self = this;
    var _arguments = arguments;

    return sequelize.transaction(function (transaction) {
            return doSomething({transaction: transactioin});
        })
        .catch(function (error) {
            if (error && error.original && error.original.code === 'ER_LOCK_WAIT_TIMEOUT') {

                return Promise.delay(Math.random() * 1000)
                    .then(function () {
                        return SQLProcess.apply(self, _arguments);
                    });

            } else {
                throw error;
            }
        });
};

My problem is, the simultaneously running requests lock each other for a long time, and my request returns after a long-long time (~60 seconds).

I hope I could explain it clear and understandable, and you could offer me some solution.

I'm dealing with a promblem for a couple of days, and I'm really hoping, you could help me.

It's a node.js based API using sequelize for MySQL.

On certain API calls the code starts SQL transactions which lock certain tables, and if I send multiple requests to the API simultaneously, I got LOCK_WAIT_TIMEOUT errors.

var SQLProcess = function () {
    var self = this;
    var _arguments = arguments;

    return sequelize.transaction(function (transaction) {
            return doSomething({transaction: transactioin});
        })
        .catch(function (error) {
            if (error && error.original && error.original.code === 'ER_LOCK_WAIT_TIMEOUT') {

                return Promise.delay(Math.random() * 1000)
                    .then(function () {
                        return SQLProcess.apply(self, _arguments);
                    });

            } else {
                throw error;
            }
        });
};

My problem is, the simultaneously running requests lock each other for a long time, and my request returns after a long-long time (~60 seconds).

I hope I could explain it clear and understandable, and you could offer me some solution.

• javascript
• mysql
• node.js
• express
• sequelize.js

• That isn't node.js or sequelize trouble, it's an error message from MySQL 1205 Lock wait timeout exceeded; try restarting transaction. You should look at transactions and their states inside MySQL server. – Vlad Churakov Commented Jan 27, 2016 at 10:42
• It's absolutely clear, why I get this error. I'm looking for a solution how I could handle it. – Adam Commented Jan 27, 2016 at 10:47
• There is only one way to understand what happens and it's SHOW ENGINE INNODB STATUS\G. In my experience, I've got dead-locks transactions in MySQL multiple times. – Vlad Churakov Commented Jan 27, 2016 at 10:55
• It is not clear what kind of answer do you expect. What do you mean by "handle"? Do you want to get rid of lock (in this case it is important what your doSomething does)? Or do you want to repeat your transaction (in this case why don't you run doSomething again from your handler? Or your catch statement doesn't work and you want to make it work? I have a large list of random notes related to deadlocks here, it look puzzling to me now, but check it, maybe you'll find something useful. – Borys Serebrov Commented Mar 3, 2016 at 20:48

2 Answers 2

This may not be a direct answer to your question, but maybe by looking at why you had this problem would also help.

1) What does that doSomething() do? Anyway we can do some improvements there?

First, a transaction that take 60 sec is suspicious.. If you lock a table for that long, chances are the design should be revisited. Given a typical db operation runs 10 - 100 ms.

Ideally, all the data preparation should be done outside of the transaction, including data read from database. And the transaction should be really for only transactional operations.

2) Is it possible to use mysql stored procedure?

True, the stored procedure for mysql is not piled, as PL/SQL for Oracle. But it is still running on the database server. If your application is really plicated and contain a lot of back and force network traffic between database and your node application in that transaction, and considering there are so many layer of javascript calls, it could really slowing things down. If 1) doesn't save you a lot of time, consider using mysql stored procedure.

The drawback of this approach, obviously, is that it is harder to maintain the codes in both nodejs and mysql.

If 1) and 2) are definitely not possible, you may consider some kind of flow control or queuing tool. Either your app make sure the 2nd request doesn't go until the first one finishes, or your have some 3rd party queuing tools to handle that. Seems you don't need any parallelism in running those requests anyway.

The main reason for deadlocks is poor database design. Without further information about your database design and which exact queries might or might not lock each other it is impossible to give you a specific solution for your problem.

However I can give you a general advice/approach to solve this issue:

• I would make sure that your database is normalized at least into Third Normal Form or, if that still isnt enough even further. There might be tools to automate this process for you.
  
  Aside from reducing the likelihood of deadlocks this also helps keeping your data consistent, which is always a good thing.
• Keep your transactions as slim as possible. If you are inserting new rows into your tables and update other tables accordingly you might want to use a Trigger rather than another SQL statement to do so. The same applies to reading rows and values. Such things can be done before or after your transaction.

• Choose the correct Isolation Level. Possible isolation levels are:

  READ_UNCOMMITTED
  READ_COMMITTED
  REPEATABLE_READ
  SERIALIZABLE
  

  Sequelize's official documentation describes how you can set the isolation level and lock/unlock transactions by yourself.

As I said, without further insight about your database and query design thats all I can do for you right now.
Hope this helps.