A.R.H A.R.H - 5 months ago 88
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

transactions.create
seems rolling back but
driver.update
executes and doesn't rollback and third update which is
trip.update
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
}
});
require('sequelize-isunique-validator')(Sequelize);
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) {
RequestQueue.hmset(ticket,"ticketState",value.Paid);
res.json({'status': 'success','change':(-company_share)});
}).catch(function (err) {
global.console.log('5---'+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

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});
                });

http://docs.sequelizejs.com/en/latest/api/model/#update

Comments