db 트랜젝션 롤백 (feat,커서)

커서로 문제해결!

InsertReservation에서 한 번이라도 db 작업에서 에러나면 이때까지 한 db 작업 롤백함 ㅋㅋㅋ

ROUTER.post("/InsertReservation", async (req, res) => {
  const connection = await DB.getConnection();

  try {
    await connection.beginTransaction();

    const parsedReservation = JSON.parse(req.body.custom);

    const insertQuery = RESERVATION_QUREY.insertReservation(parsedReservation);
    const newReservationData =
      await DB.executeQueryPreparedStatementWithConnection(
        connection,
        insertQuery[0],
        insertQuery[1]
      );

    const selectQuery = RESERVATION_QUREY.selectReservation(req.body);
    const selectedReservation = await DB.executeQueryWithConnection(
      connection,
      selectQuery,
      [{ q: "=", f: "reservation_no", v: newReservationData.insertId }]
    );

    const updateQuery = RESERVATION_QUREY.updateReservation({
      reservation_no: selectedReservation[0].reservation_no,
      tid: "'" + String(req.body.paytoken) + "'",
    });

    await DB.executeQueryWithConnection(connection, updateQuery);


    const carriageUpdateQuery = CARRIAGE_TOTAL_QUREY.updateTotal({
      total_no: selectedCarriageTotalData[0].total_no,
      total: "'" + JSON.stringify(finalTotal) + "'",
    });
    await DB.executeQueryWithConnection(connection, carriageUpdateQuery);

    await connection.commit();

    if (socket) {
      socket.emit("change-reservation-schedule");
    }

    res.status(200).send({ ok: true });
  } catch (err) {
    await connection.rollback();
    console.log("에러확인", err);
    res.status(200).send({ ok: false, error: err.message });
  } finally {
    if (connection) {
      await connection.end();
    }
  }
});

// DB.js
executeQueryWithConnection: async (connection, query, conditions = []) => {
   ... 이전 코드
    let exQuery = query + whereStr;
    try {
      let rows = await connection.query(exQuery);
      delete rows["meta"];
      return rows;
    } catch (exception) {
      throw exception;
    }
  },
  executeQueryPreparedStatementWithConnection: async (
    connection,
    query,
    values
  ) => {
    try {
      let rows = await connection.query(query, values);
      delete rows["meta"];
      return rows;
    } catch (exception) {
      throw exception;
    }
  },
};

핵심은 아래와 같아
1. DB.js에서 try catch 문으로 감싸고connection.query 를 실시한다. catch 문에서 throw exception; 한다.
2. DB.js 바깥에서 다시 try catch로 감싸고 catch 에서 connection.rollback() 한다.