CODEWITHSUNDEEP
mysqltransactionssequelize.js
A.R.H
A.R.H

Reputation: 391

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!

Upvotes: 6

Views: 11878

Answers (1)

Adam
Adam

Reputation: 5233

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

Upvotes: 8

Related Questions