[mysql] 동일한 조건으로 update시에 처음 실행만 반영되고 그 다음부터는 반영이 안되는 현상

동일한 조건으로 update시에 처음 실행만 반영되고 그 다음부터는 반영이 안되는 현상

 

작업 중 다량의 데이터를 Update 쿼리를 실행할 과정이 필요하였습니다.
한번의 많은 양의 데이터를 Update 한다면 DB의 커넥션이 끊어진다거나. 메모리 이슈가 생겨
증분으로 업데이트 시도하였습니다. 코드는 아래와 같습니다.

 

[method]

    int changeCntSum = 0;    // 업데이트된 데이터의 총 개수
    while (true) {
        int changCnt = 0;    // 업데이트된 데이터 개수
        changeCnt = updateMapper.updateData(...매개변수..);
        changeCntSum += changeCnt;

        // 업데이트할 데이터의 개수가 없을 때 while문 break
        if (changeCnt == 0) {
            break;
        }
    }

 

[query]

    UPDATE test_table
    SET
        mod_user_id = #{userId},
        status = status | 2
    WHERE mall_id = #{mallId}
    AND brand = #{brand}
    LIMIT 10000

 

하지만 위와 같이 작성하여 실행하면 changeCnt 값이 첫 update 시만 limit 만큼 변경개수를 리턴하고
그 다음부터 계속 0값만 리턴하고있는 문제가 발생합니다.

 

원인은 동일한 조건으로 계속 업데이트를 치니 한 트랜잭션내에 같은 조건의 범위로만 인식하여 같은 조건의 다른 데이터 반영이 안되는 것이였습니다. 그래서 증분업데이트를 size와 offset값을 기준으로 Looping하여 Update하는 방법으로 변경하였고 아래 코드를 참고하시면 됩니다.

 

[method]

    int page = 1;
    int size = 10000;
    int changeCntSum = 0;    // 업데이트된 데이터의 총 개수
    while (true) {
        int changCnt = 0;    // 업데이트된 데이터 개수
        Integer offset = (page - 1) * size;

        changeCnt = updateMapper.updateData(...매개변수..); // size, offset 추가
        changeCntSum += changeCnt;

        // 업데이트할 데이터의 개수가 없을 때 while문 break
        if (changeCnt == 0) {
            break;
        }

        page++;
    }

 

[query]

    // CASE 1
    UPDATE test_table AS upd_t
    INNER JOIN (
        SELECT product_id
        FROM test_table
        WHERE mall_id = #{mallId}
        AND brand = #{brand}
        LIMIT #{size} OFFSET #{offset}
    ) AS join_t ON upd_t.product_id = join_t.product_id
    SET
        upd_t.mod_user_id = #{userId},
        upd_t.status = status | 2,
        upd_t.update_field = #{updateFieldVal}
    WHERE upd_t.mall_id = #{mallId}
    AND upd_t.brand = #{brand}

// CASE 2: CASE 1보다 성능이 좋다 명확하게 변경된 값이 있을때만 CASE 2를 사용하자. ( update_field 가 변경될 값 )
// 여기서 LIMIT를 지정했으니 상위의 [method]의 Looping 하는 코드에서 page, limit, offset 관련 매개변수는 제거해도 된다.
    UPDATE test_table
    SET 
    	mod_user_id = #{userId},
        status = status | 2,
        update_field = #{updateFieldVal}
    WHERE mall_id = #{mallId}
    AND brand = #{brand}
    AND update_field != #{updateFieldVal}
    LIMIT 10000
    

 

• • 즉, 위의 CASE2 기준으로 다시 정리하자면 예제 코드는 아래와 같다. 해당 쿼리에 LIMIT를 주고 프로그래밍 코드상에서는 Looping돌려 Change Count값이 0일때 break문 처리를 한다.
    [Program Code(Java)]

        while (true) {
          int changeCnt = 0;
    
          changeCnt = categoryMapRequireMapper.putProduct(
              jsonObject.getString("mallId"),
              jsonObject.getString("cateMstCd")
          );
    
          changeCntSum += changeCnt;
    
          log.info("setVendorCategoryMapRequireMasterCodeMapping Change Cnt !! : {}", changeCnt);
          if (changeCnt == 0) {
              log.info("setVendorCategoryMapRequireMasterCodeMapping Sucess Cnt !! : {}", changeCntSum);
              break;
          }
      }
    

    
    

    [Query]

      // 같은 조건이 되지 않게 cateMstCd값을 같지 않을 때의 조건을 걸어 증분 UPDATE를 반영한다.
      UPDATE ws_product_category_map
      SET
          cate_mst_cd = #{cateMstCd},
          status = status | 1
      WHERE
          mall_id = #{mallId}
          AND cate_mst_cd != #{cateMstCd}
      LIMIT 10000