CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Jun
Jun

Reputation: 541

In Sequelize, N:M junction table with pre-defined table makes transaction lock error(MySQL)

I have two tables named User and Gathering. I wanted to create an N:M join table by adding several columns to display the creator, likes, bookmarks for a gathering. But, An Error : lock wait timeout exceeded: try restarting transaction was caused when i tried to add User model to created Gathering model. Could you tell me what is the problem in my code??

// routing code

let putGathering = async function (req, res) {
    await sequelize.transaction(async transaction => {
        try {
            const user = await User.findOne({
                where: {
                    email: req.body.email,
                },
            }, { transaction: transaction });

            const category = await Category.findOne({
                where: {
                    category_name: req.body.categoryName,
                },
            }, { transaction: transaction });

            const [location, created] = await Location.findOrCreate({
                where: {
                    location_name: req.body.meetLoc,
                },
                transaction,
            });

            const gathering = await Gathering.create({
                title: req.body.meetTitle,
                date: req.body.meetDate,
                time: req.body.meetTime,
                content: req.body.content,
                maxnum: req.body.maxNum,
                minnum: req.body.minNum,
                maxage: req.body.maxAge,
                minage: req.body.minAge,
            }, { transaction: transaction });

            await category.addGathering(
                gathering,
                { transaction },
            );

            await location.addGathering(
                gathering,
                { transaction },
            );

            console.log(10);    // This log is worked.

            await gathering.addUser(
                user,
                {
                    through: {
                        status: 1,
                        like: 0,
                        book_mark: 0,
                    }
                },
                { transaction },
            );

            console.log(11);    // This log is not worked. I think there is a problem int upper code.

            res
                .status(201)
                .json({
                    responseMsg: 'succeed',
                })
        } catch (err) {
            console.error(err);
        }
    });
};

// user.js(Sequelize Model file)

const Sequelize = require('sequelize');

module.exports = class User extends Sequelize.Model {
    static init(sequelize) {
        return super.init({
            idx: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            email: {
                type: Sequelize.STRING,
                allowNull: false,
                validate: {
                    isEmail: true
                },
            },
            pw: {
                type: Sequelize.STRING,
                allowNull: false
            },
            phone: {
                type: Sequelize.CHAR(15),
                allowNull: false
            },
            nickname: {
                type: Sequelize.STRING(10),
                allowNull: false
            },
            gender: {
                type: Sequelize.CHAR(1),
                allowNull: false
            },
            who: {
                type: Sequelize.INTEGER,
                allowNull: false
            },
            introduction: {
                type: Sequelize.TEXT
            },
            userImg: {
                type: Sequelize.STRING
            },
            birth: {
                type: Sequelize.DATE,
                allowNull: false
            },
            salt: {
                type: Sequelize.STRING
            },
        }, {
            sequelize,
            timestamps: true,
            underscored: true,
            paranoid: true,
            modelName: 'user',
            tableName: 'User',
            charset: 'utf8',
            collate: 'utf8_general_ci',
        });
    }
    static associate(db) {
        db.User.belongsToMany(db.Gathering, {
            through: db.UserGathering,
            foreignKey: 'user_idx',
            timestamps: false
        });
    }
};

// gathering.js(Sequelize Model file)

const Sequelize = require('sequelize');

module.exports = class Gathering extends Sequelize.Model {
    static init(sequelize){
        return super.init({
            idx: {
                type: Sequelize.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            title: {
                type: Sequelize.STRING,
                allowNull: false,
            },
            date: {
                type: Sequelize.STRING,
                allowNull: false
            },
            time: {
                type: Sequelize.STRING,
                allowNull: false
            },
            content: {
                type: Sequelize.TEXT,
                allowNull: false
            },
            maxnum: {
                type: Sequelize.INTEGER,
                allowNull: false
            },
            minnum: {
                type: Sequelize.INTEGER,
                allowNull: false
            },
            maxage: {
                type: Sequelize.INTEGER,
                allowNull: false
            },
            minage: {
                type: Sequelize.INTEGER,
                allowNull: false
            }
        }, {
            sequelize,
            timestamps: true,
            underscored: true,
            paranoid: true,
            modelName: 'gathering',
            tableName: 'Gathering',
            charset: 'utf8',
            collate: 'utf8_general_ci',
        });
    }
    static associate(db) {
        db.Gathering.belongsToMany(db.User, { 
            through: db.UserGathering,
            foreignKey: 'gathering_idx',
            timestamps: false 
        });
    }
};

// user_gathering.js(Sequelize Model)

const Sequelize = require('sequelize');

module.exports = class UserGathering extends Sequelize.Model {
    static init(sequelize) {
        return super.init({
            status: {
                type: Sequelize.INTEGER,
            },
            like: {
                type: Sequelize.INTEGER,
            },
            book_mark: {
                type: Sequelize.INTEGER,
            },
        }, {
            sequelize,
            timestamps: false,
            modelName: 'userGathering',
            tableName: 'User_Gathering',
            charset: 'utf8',
            collate: 'utf8_general_ci',
        });
    }
};

// index.js (for Sequelize Config)

const Sequelize = require('sequelize');
const User = require('./user');
const Category = require('./category');
const Gathering = require('./gathering');
const Location = require('./location');
const Comment = require('./comment');
const UserGathering = require('./user_gathering');
const env = process.env.NODE_ENV || 'development';
const config = require(__dirname + '/../config/config.json')[env];
const db = {};

const sequelize = new Sequelize(config.database, config.username, config.password, config);

db.sequelize = sequelize;

db.User = User;
db.Category = Category;
db.Gathering = Gathering;
db.Location = Location;
db.Comment = Comment;
db.UserGathering = UserGathering;

User.init(sequelize);
Category.init(sequelize);
Gathering.init(sequelize);
Location.init(sequelize);
Comment.init(sequelize);
UserGathering.init(sequelize);

User.associate(db);
Gathering.associate(db);
Location.associate(db);
Category.associate(db);
Comment.associate(db);
UserGathering.associate(db);

module.exports = db;

// error log

0|app      |    { Error: Lock wait timeout exceeded; try restarting transaction
0|app      |        at Packet.asError (/home/ec2-user/mepus_test/node_modules/my                                                                                                                                                             sql2/lib/packets/packet.js:712:17)
0|app      |        at Query.execute (/home/ec2-user/mepus_test/node_modules/mys                                                                                                                                                             ql2/lib/commands/command.js:28:26)
0|app      |        at Connection.handlePacket (/home/ec2-user/mepus_test/node_m                                                                                                                                                             odules/mysql2/lib/connection.js:425:32)
0|app      |        at PacketParser.Connection.packetParser.p [as onPacket] (/ho                                                                                                                                                             me/ec2-user/mepus_test/node_modules/mysql2/lib/connection.js:75:12)
0|app      |        at PacketParser.executeStart (/home/ec2-user/mepus_test/node                                                                                                                                                             _modules/mysql2/lib/packet_parser.js:75:16)
0|app      |        at Socket.Connection.stream.on.data (/home/ec2-user/mepus_te                                                                                                                                                             st/node_modules/mysql2/lib/connection.js:82:25)
0|app      |        at Socket.emit (events.js:198:13)
0|app      |        at addChunk (_stream_readable.js:288:12)
0|app      |        at readableAddChunk (_stream_readable.js:269:11)
0|app      |        at Socket.Readable.push (_stream_readable.js:224:10)
0|app      |      code: 'ER_LOCK_WAIT_TIMEOUT',
0|app      |      errno: 1205,
0|app      |      sqlState: 'HY000',
0|app      |      sqlMessage: 'Lock wait timeout exceeded; try restarting transa                                                                                                                                                             ction',
0|app      |      sql:
0|app      |       'INSERT INTO `User_Gathering` (`status`,`like`,`book_mark`,`u                                                                                                                                                             ser_idx`,`gathering_idx`) VALUES (1,0,0,1,7);',
0|app      |      parameters: undefined },

Upvotes: 1

Views: 385

Answers (1)

Carlos Manuel Cabello
Carlos Manuel Cabello

Reputation: 26

The syntax for the method Model.belongsToMany.add() is:

entityA.addEntityB(entityB: Model, ... , options: object)

The solution is to include both through and transaction, not as separate parameters, but in the same options object.

await gathering.addUser(
  user,
  {
    through: {status: 1, like: 0, book_mark: 0},
    transaction: transaction
  }
);

You might already solved it but I had the same confusion and couldn't find useful results, so I think it's worth it to share a solution.

Upvotes: 1

Related Questions