title: lock wait timeout exceeded try restarting transaction
body: <p>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 - <strong>lock wait timeout exceeded try restarting transaction</strong> 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.</p>
  <p>Thank you in advance</p>
  <pre><code>const registerUsers = async (req, res) =&gt; {
      const {
          body
      } = req;
  
      try {
          await db.sequelize.transaction(async (t) =&gt; {
              await body.users.reduce(async (promise, item) =&gt; {
                  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');
      }
  };
  </code></pre>
  <h2>UPDATE</h2>
  <p>Here is mysql's general log printed.</p>
  <pre><code>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&amp;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  
  </code></pre>
  <p>All tables have one to one relation with KSUser table.</p>
  <h2>Working code</h2>
  <p>Thanks to <a href="https://stackoverflow.com/users/1766831/rick-james">Rick James</a>. You are right. <code>KSUserCredential</code> table is much bigger than other tables therefore it took too long to be committed. So I added another transaction that handle inserting from <code>KSUserStatus</code> to <code>KSLikeInfo</code>. It worked well. But it might cause another problem in terms of <em>Rollback</em>. 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.</p>
  <p>Instead of using 2 transactions, another approach was to not to use <em>Relation</em> but all required actions in one transaction. After that my code started working.</p>
  <p>Here is updated code.</p>
  <pre><code>const registerUsers = async (req, res) =&gt; {
          const {
              body
          } = req;
  
          try {
              await body.users.reduce(async (promise, item) =&gt; {
                  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) =&gt; {
                      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');
          }
      };
  </code></pre>