javascript - 我是否正确释放了我的 SQL 连接？

Question

我有一个执行许多 API 调用的应用程序，但是我像这样释放每个连接？

app.put("/interview/create/questions/:lastEmployeeId", function (req, res) {
  console.log("It is getting to the route");
  const employee_id = req.body.lastEmployeeId;
  const tableName = req.body.table;
  connection.getConnection(function (err, connection) {
    connection.query(
      `INSERT INTO ${tableName} (employee_id)
            VALUES (?)`,
      [employee_id],
      function (error, results, fields) {
        if (error) throw error;
        res.json(results);
        console.log(`Interview has been created`);
      }
    );
    connection.release();
  });
});

这是正确的方法吗，因为我收到了很多不一致的 502 和 503 错误。在我看来，它可能正在耗尽资源。路径是正确的，因为有时它们有效，有时则无效。这是随机的，所以我不知道该怎么做。

不connection.release()释放资源吗？我目前将最大连接数设置为 50，这应该比我需要的要多，因为一次只有一个人会使用它，并且有一种方法可以进行 8 次调用。我不介意一些帮助重构它，但我仍然需要这么多调用，因为我需要它在 8 个不同的表中创建行。下面这个长方法只是取一个表中输入的值，并将其作为参考输入到其他 8 个表中。

createQuestions() {
    fetch(
      API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
      {
        method: "PUT",
        body: JSON.stringify({
          lastEmployeeId: this.state.lastEmployeeId,
          table: "audit_general",
        }),
        headers: { "Content-Type": "application/json" },
      }
    )
      .then((res) => {
        if (!res.ok) {
          throw new Error();
        }
        return res.json();
      })
      .then((data) => console.log(data))
      .catch((err) => console.log(err))
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_culture",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_performance",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_policies",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_risk",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_strategy",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_rewards",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      )
      .then(
        fetch(
          API_URL + `/interview/create/questions/${this.state.lastEmployeeId}`,
          {
            method: "PUT",
            body: JSON.stringify({
              lastEmployeeId: this.state.lastEmployeeId,
              table: "audit_workforce",
            }),
            headers: { "Content-Type": "application/json" },
          }
        )
          .then((res) => {
            if (!res.ok) {
              throw new Error();
            }
            return res.json();
          })
          .then((data) => console.log(data))
          .catch((err) => console.log(err))
      );
  }

标签： javascriptmysqlreactjs

Answer 1

您在查询完成之前释放连接。这需要解决：

app.put("/interview/create/questions/:lastEmployeeId", function (req, res) {
  console.log("It is getting to the route");
  const employee_id = req.body.lastEmployeeId;
  const tableName = req.body.table;

  connection.getConnection(function (err, connection) {
    if (err) {
      res.statusCode = 500;
      res.send('DB connection could not be acquired.');
      return;
    }

    connection.query(
      `INSERT INTO ${tableName} (employee_id)
            VALUES (?)`,
      [ employee_id ],
      function (error, results, fields) {
        // throw here does nothing useful, it's inside a callback and
        // the calling function may have terminated ages ago. You need
        // to handle this error here and now.
        if (error) {
          res.statusCode = 500;
          res.send('Nope!');
          return;
        }

        res.json(results);
        console.log(`Interview has been created`);

        // Now you can release the handle
        connection.release();
      }
    );
    
    // Code here runs before the query can complete.
  });
});

想想你的异步程序流程是这样的：

connection.getConnection(..., cbGC) executes and returns

** Long time passes

cbGC executes -> connection.query(..., cbQ) executes and returns

** Tiny eternity elapses

cbQ executes!

调用一个接受回调的函数和实际执行的回调之间的时间间隔可能很长，秒或更长，这意味着调用函数不仅已死，而且是古老的历史，可能是垃圾收集。

您需要围绕嵌套任何依赖于该排序的任何内容的原则来组织您的代码。