TIL Node.js / SQL 트랜잭션 적용하기

트랜잭션이란? 데이터베이스의 상태를 변화시키기 위한 하나의 논리적인 작업 단위이다.

내가 적용해볼 데이터베이스는 아래와 같다.
1. controller-service로 부터 받은 state값이 2이면 발주 테이블(place_order_item)의 state 값을 2로 update한다.

2. 변경 된 발주 row로부터 item_id와 amount를 조회한다(읽기)

3. 메뉴 테이블(item)의 amount(재고)를 변경한다.

completeOrder = async (state, id) => {
    try {
      await expressApp.dbConnection.beginTransaction();
      const placeOrderSql = 'UPDATE place_order_item SET state=? WHERE id=?';
      await expressApp.dbConnection.query(placeOrderSql, [state, id]);
      const updateItemSql =
        'SELECT amount, item_id FROM place_order_item WHERE id=?';
      const updateItem = await expressApp.dbConnection.query(updateItemSql, [
        id,
      ]);
      const itemSql = 'UPDATE item SET amount=? WHERE id=?';
      await expressApp.dbConnection.query(
        itemSql,
        Object.values(updateItem[0][0])
      );
      await expressApp.dbConnection.commit();
    } catch (err) {
      await expressApp.dbConnection.rollback();
      return err;
    }
  };

try catch문을 이용하여 처음에 트랜잭션 시작을 선언하고, 중간에 에러가 발생하면 err로 넘겨 rollback한다. 또한 모든 것을 거치고 마지막에 commit을 하여 영구저장을 한다.

async/await을 이용하여 비동기 처리를 하였기때문에 중간에 하나라도 순서대로 처리되지 않으면 바로 err로 넘어가게 된다.

• 이제는 state=3 : canceled상태일 때의 코드를 작성하려 한다.

=> state=3이면 발주 취소이다. 그런데 만약 현재 재고 수량이 발주한 수량보다 적으면 취소를 할 수 없고 많으면 취소가 가능하다.

1. 현재 수량과 발주 수량을 조회하는 쿼리문을 작성

2. 조회한 데이터를 service에서 받아 if문으로 현재 수량과 발주 수량을 비교하고 현재 수량이 적을 시 rollback한다.

3. 현재 수량이 많을 시 state를 3으로 업데이트하고 영구히 저장한다(commit)

Service 부분

if (state == 3) {
        const getAmount = await this.placeOrderRepository.getOrder(id);
        if (getAmount[0] > getAmount[1]) {
          return res.status(400).json({
            message: '현재 수량이 발주 수량보다 적어 발주 취소가 불가능합니다.',
          });
        } else {
          return await this.placeOrderRepository.cancelOrder(id);
        }
      }

Repository 부분

cancelOrder = async (id) => {
    // 메뉴 발주했던 데이터를 삭제하지 않고 수량만 0으로 감소시킴
    const sql = 'UPDATE place_order_item SET amount=0 WHERE id=?';
    await expressApp.dbConnection.query(sql, [id]);
  };
  getOrder = async (id) => {
    const sql =
      'SELECT p.amount, i.amount FROM place_order_item p JOIN item i ON p.item_id=i.id WHERE p.id=?';
    const result = await expressApp.dbConnection.query(sql, [id]);
    console.log(result[0]);
    return result;
  };

실행하여 보니

값이 하나밖에 나오지 않는다. 테스트용으로 넣은 값인데 저 값은 재고 수량으로 뜬다. 왜 발주 수량은 조회되지 않는 것일까?

혹시 몰라 작성한 쿼리문을 dbeaver에서 실행해 보았으나 여기에서는 발주 수량, 재고 수량이 정상적으로 조회가 되었다.

이유를 알 수 없이 i.amount를 앞에다 가져왔을 때는 i.amount만 가져오고 p.amount를 앞에다 가져왔을 때는 p.amount만 가져왔다.

이러한 문제때문에 결국 쿼리가 길거나 두 개이상을 select 해 올 수 없거나라고 판단하여 쿼리문을 두 개로 나누어서 작성하였다.

getOrder = async (id) => {
    const sql =
      'SELECT i.amount FROM place_order_item p INNER JOIN item i ON p.item_id=i.id WHERE p.id=?';
    const sql2 =
      'SELECT p.amount FROM place_order_item p INNER JOIN item i ON p.item_id=i.id WHERE p.id=?';
    const recentAmount = await expressApp.dbConnection.query(sql, [id]);
    const orderAmount = await expressApp.dbConnection.query(sql2, [id]);
    const result = [
      Object.values(recentAmount[0][0]),
      Object.values(orderAmount[0][0]),
    ];
    return result;
  };

sql1과 sql2로 선언하여 받은 값을 result에 배열로 다시 할당하여 다시 보내주었다.