A.R.H A.R.H - 1 year ago 317
MySQL Question

Sequelize Transactions : ER_LOCK_WAIT_TIMEOUT

i've problem with sequelize transactions with mysql(5.6.17),i've one insert statement and two updates which should all done or none,howerver in the end

seems rolling back but
executes and doesn't rollback and third update which is
statement without any changes or rollback,the console hangs and after a few seconds throw this error:

Executing (42a68c8e-8347-45af-b9a2-7b0e7a89606b): START TRANSACTION;
Executing (42a68c8e-8347-45af-b9a2-7b0e7a89606b): SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Executing (42a68c8e-8347-45af-b9a2-7b0e7a89606b): SET autocommit = 1;
Executing (42a68c8e-8347-45af-b9a2-7b0e7a89606b): INSERT INTO `transactions` (`id`,`tId`,`total_price`,`company_share`,`driver_share`,`at`) VALUES (DEFAULT,'13',1000,100,900,'2016-07-04 10:44:43');
Executing (default): UPDATE `driver` SET `balance`=`balance` - 100 WHERE `id` = '1'
Executing (default): UPDATE `trip` SET `paid`=1 WHERE `id` = '13'
Executing (42a68c8e-8347-45af-b9a2-7b0e7a89606b): ROLLBACK;
5---SequelizeDatabaseError: ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction

the transaction section is:

var Sequelize = require('sequelize');
var config = {};
config.sequelize = new Sequelize('mydb', 'root', null, {
host: 'localhost',
port: 3306,
dialect: 'mysql',
logging: true,
pool: {
max: 100,
min: 0,
idle: 10000
define: {
timestamps: false
var driver = require('./../models/driver.js')(config.sequelize, Sequelize);
var transactions = require('./../models/transactions.js')(config.sequelize, Sequelize);
var trip = require('./../models/trip.js')(config.sequelize, Sequelize);

return config.sequelize.transaction({isolationLevel:Sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED},function (t) {
return transactions.create({tId: tripId, total_price: totalPrice, company_share: companyShare, driver_share: driverShare}, {transaction: t})
.then(function (result) {
return driver.update({balance: config.sequelize.literal('`balance` - '+companyShare)}, {where: {id: dId}}, {transaction: t})
.then(function (result) {
return trip.update({paid: 1}, {where: {id: tripId}}, {transaction: t});

}).then(function (result) {
res.json({'status': 'success','change':(-company_share)});
}).catch(function (err) {
res.json({'status': 'failed'});

I'm sure my models are correct because I used them somewhere else without any problem on crud and not putting them here in order to keeps the question clean and on topic but if it helps ask in comments,tnx!

Answer Source

You should pass the transaction parameter inside the options object.

    .then(function (result) {
        return driver.update({balance: config.sequelize.literal('`balance` - ' + companyShare)}, {
                    where: {id: dId},
                    transaction: t
                .then(function (result) {
                    return trip.update({paid: 1}, {where: {id: tripId}, transaction: t});