Handling rollbacked MySQL transactions in Node.js

Question

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.

Answer 1

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 compiled, as PL/SQL for Oracle. But it is still running on the database server. If your application is really complicated 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.

Answer 2

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.