I must say that I am fairly new to backend development and mysql programming as well. I just started learning how to develop backend using Node.js. To operate with mysql I am using Sequelize. When I call an api listed below I get an error - lock wait timeout exceeded try restarting transaction and the code jump into catch block. I believe that this should be an error caused with the wrong use of sequelize transaction which will finally invoke innodb transaction. But I can't figure out this problem and I would need you kind help.
Thank you in advance
const registerUsers = async (req, res) => {
const {
body
} = req;
try {
await db.sequelize.transaction(async (t) => {
await body.users.reduce(async (promise, item) => {
await promise;
const user = await db.KSUser.findOne({
where: {
phoneNum: item.userId,
},
transaction: t
});
if (!user) {
const newUser = await db.KSUser.create({
phoneNum: item.userId,
verifyURL: item.password,
loginType: body.type,
}, {
include: [{
model: db.KSUserCredential,
as: 'credential',
},
{
model: db.KSUserStatus,
as: 'userStatus',
},
{
model: db.KSTrainInfo,
as: 'trainInfo',
},
{
model: db.KSFollowInfo,
as: 'followInfo',
},
{
model: db.KSLikeInfo,
as: 'likeInfo',
},
],
transaction: t
});
await newUser.createCredential({
countryCode: body.countryCode,
}, {
transaction: t
});
await newUser.createUserStatus({ transaction: t });
await newUser.createTrainInfo({ transaction: t });
await newUser.createFollowInfo({ transaction: t });
await newUser.createLikeInfo({ transaction: t });
}
}, Promise.resolve());
return apiResponse.successResponse(
res,
'Successfully registered accounts'
);
});
} catch (err) {
console.log(err);
return apiResponse.ErrorResponse(res, 'Internal server error');
}
};
UPDATE
Here is mysql's general log printed.
2021-01-07T18:10:45.118166Z 13 Query SELECT * FROM ks_db.ksuser
LIMIT 0, 5000
2021-01-07T18:10:45.119052Z 12 Query SHOW INDEX FROM `ks_db`.`ksuser`
2021-01-07T18:10:46.081547Z 13 Query set autocommit=1
2021-01-07T18:11:05.790801Z 34 Connect root@localhost on ks_db using TCP/IP
2021-01-07T18:11:05.791242Z 34 Query SET time_zone = '+00:00'
2021-01-07T18:11:05.792341Z 34 Query START TRANSACTION
2021-01-07T18:11:05.794342Z 34 Query SELECT `id`, `phoneNum`, `verifyURL`, `wbId`, `wbPassword`, `wxId`, `wxPassword`, `qqId`, `qqPassword`, `loginType`, `createdAt` FROM `KSUser` AS `KSUser` WHERE `KSUser`.`phoneNum` = '11111111111' LIMIT 1
2021-01-07T18:11:05.805810Z 34 Prepare INSERT INTO `KSUser` (`id`,`phoneNum`,`verifyURL`,`wbId`,`wbPassword`,`wxId`,`wxPassword`,`qqId`,`qqPassword`,`loginType`,`createdAt`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?)
2021-01-07T18:11:05.806970Z 34 Execute INSERT INTO `KSUser` (`id`,`phoneNum`,`verifyURL`,`wbId`,`wbPassword`,`wxId`,`wxPassword`,`qqId`,`qqPassword`,`loginType`,`createdAt`) VALUES (DEFAULT,'855764823583','http://45.251.242.43:17879/api/1.0.0/message.php?api_key=e74475a6f4bc285e405c524e3fa3091&mobile=855764823583','','','','','','',0,'2021-01-07 18:11:05')
2021-01-07T18:11:05.811604Z 34 Prepare INSERT INTO `KSUserCredential` (`id`,`userId`,`countryCode`,`androidId`,`clientSalt`,`token`,`egId`,`apiSt`,`passToken`,`h5St`,`IMEI`,`deviceId`,`kwaiId`,`user_id`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?,?,?,?)
2021-01-07T18:11:05.812002Z 34 Execute INSERT INTO `KSUserCredential` (`id`,`userId`,`countryCode`,`androidId`,`clientSalt`,`token`,`egId`,`apiSt`,`passToken`,`h5St`,`IMEI`,`deviceId`,`kwaiId`,`user_id`) VALUES (DEFAULT,'','KH','','','','','','','','',-1,'',676)
2021-01-07T18:11:05.815591Z 35 Connect root@localhost on ks_db using TCP/IP
2021-01-07T18:11:05.816038Z 35 Query SET time_zone = '+00:00'
2021-01-07T18:11:05.817191Z 35 Prepare INSERT INTO `KSUserStatus` (`id`,`isPlaying`,`isTrain`,`playingType`,`isActive`,`connectorId`,`isProxy`,`proxyIp`,`proxyPort`,`location`,`user_id`) VALUES (DEFAULT,?,?,?,?,?,?,?,?,?,?)
2021-01-07T18:11:05.817584Z 35 Execute INSERT INTO `KSUserStatus` (`id`,`isPlaying`,`isTrain`,`playingType`,`isActive`,`connectorId`,`isProxy`,`proxyIp`,`proxyPort`,`location`,`user_id`) VALUES (DEFAULT,0,1,0,1,0,0,'',0,'',676)
2021-01-07T18:11:57.067850Z 34 Query ROLLBACK
2021-01-07T18:12:07.120148Z 35 Quit
2021-01-07T18:12:07.120247Z 34 Quit
All tables have one to one relation with KSUser table.
Working code
Thanks to Rick James. You are right. KSUserCredential table is much bigger than other tables therefore it took too long to be committed. So I added another transaction that handle inserting from KSUserStatus to KSLikeInfo. It worked well. But it might cause another problem in terms of Rollback. After successful commit of first transaction, if second one falls into errors, then unexpected data will be inserted into DB because first one was already committed.
Instead of using 2 transactions, another approach was to not to use Relation but all required actions in one transaction. After that my code started working.
Here is updated code.
const registerUsers = async (req, res) => {
const {
body
} = req;
try {
await body.users.reduce(async (promise, item) => {
await promise;
var in_condition;
var query;
switch (body.type) {
case 0:
in_condition = { phoneNum: item.userId };
query = {
phoneNum: item.userId,
verifyURL: item.password,
loginType: body.type,
};
break;
case 1:
in_condition = { wbId: item.userId };
query = {
wbId: item.userId,
wbPassword: item.password,
loginType: body.type,
};
break;
case 2:
in_condition = { wxId: item.userId };
query = {
wxId: item.userId,
wxPassword: item.password,
loginType: body.type,
};
break;
case 3:
in_condition = { qqId: item.userId };
query = {
qqId: item.userId,
qqPassword: item.password,
loginType: body.type,
};
break;
default:
break;
}
await db.sequelize.transaction(async (t) => {
const user = await db.KSUser.findOne({
where: in_condition,
transaction: t
});
if (!user) {
const newUser = await db.KSUser.create(query, {
transaction: t
});
// eslint-disable-next-line no-extra-boolean-cast
if (!!newUser) {
await db.KSUserCredential.create({
countryCode: body.countryCode,
user_id: newUser.id
}, {
transaction: t
});
await db.KSUserStatus.create({user_id: newUser.id}, {transaction: t});
await db.KSTrainInfo.create({user_id: newUser.id}, {transaction: t});
await db.KSFollowInfo.create({user_id: newUser.id}, {transaction: t});
await db.KSLikeInfo.create({user_id: newUser.id}, {transaction: t});
}
}
});
}, Promise.resolve());
return apiResponse.successResponse(
res,
'Successfully registered new accounts'
);
} catch (err) {
console.log(err);
return apiResponse.ErrorResponse(res, 'Internal server error');
}
};
