javascript - Handling rollbacked MySQL transactions in Node.js -

i'm dealing promblem couple of days, , i'm hoping, me.

it's node.js based api using sequelize mysql.

on api calls code starts sql transactions lock tables, , if send multiple requests api simultaneously, 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, simultaneously running requests lock each other long time, , request returns after long-long time (~60 seconds).

i hope explain clear , understandable, , offer me solution.

this may not direct answer question, maybe looking @ why had problem help.

1) dosomething() do? anyway can improvements there?

first, transaction take 60 sec suspicious.. if lock table long, chances design should revisited. given typical db operation runs 10 - 100 ms.

ideally, data preparation should done outside of transaction, including data read database. , transaction should transactional operations.

2) possible use mysql stored procedure?

true, stored procedure mysql not compiled, pl/sql oracle. still running on database server. if application complicated , contain lot of , force network traffic between database , node application in transaction, , considering there many layer of javascript calls, slowing things down. if 1) doesn't save lot of time, consider using mysql stored procedure.

the drawback of approach, obviously, is harder maintain codes in both nodejs , mysql.

if 1) , 2) not possible, may consider kind of flow control or queuing tool. either app make sure 2nd request doesn't go until first 1 finishes, or have 3rd party queuing tools handle that. seems don't need parallelism in running requests anyway.