PSQL 반복적인 Update query 실행시 발생 하는 상황 - SQLSTATE(08006)

 

주문 제작 성능 테스트를 실행하다 발견한 문제점이다. 처음에는 한번에 100주문, 500 주문, 1000주문으로 주문량을 늘려가면서 로드 테스트를 해보다가 동시 4000주문으로 늘렸을 때 발생된 일이다.

 

비교적 불규칙적으로, 2000주문 쯤 되었을 때, 외부 서비스로 AMQ를 통해 주문 신청이 완료가 되고 주문보고서가 S3에 올라갔지만 마지막에 처리해야하는 PSQL의 주문 상태 업데이트가 종종 뻑이 나는데, 알고보니 중간에 상태를 변경하다 멈추는 현상이 발견된 것이다.  

 

에러 코드는 다음과 같다

 

2023-12-29 13:03:28,626 [reactor-http-epoll-1] WARN pool.ProxyConnection {} - HikariPool-1 - Connection org.postgresql.jdbc.PgConnection@6cda9f4d marked as broken because of SQLSTATE(08006), ErrorCode(0) org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.

 

 

PSQL 공식 에러코드: https://www.postgresql.org/docs/current/errcodes-appendix.html

Appendix A. PostgreSQL Error Codes

08006

connection_failure

 

연결이 끊어 진 것이다. 무언 간가 잘 못 되어 중간에 멈춘 것이고 주문 신청은 완료된 상황인데 실제 DB에서는 아직 주문 신청 대기로 뜨고 있어 문제가 된다.

 

코드는 간단히 요약하면 다음과 같다

 

        List<Order> orders; //주문이 있다 가정
        
        //AMQ로 외부 서비스에 주문 정보 전달
        
        //S3에 주문 보고서 작성 후, 업로드 
        
        orders.forEach(order -> {
            repository.updateOrderStatus(order); //문제 지점
        });

 

updateOrder 에서 문제가 터진 것인데 자세히 들여다 보면 이렇다. (물론 간략하게 정리 한 예시이다)

public int updateOrderStatus(order){
 return dsl.transactionResult(ctx -> {
            var configuration = ctx.dsl().configuration();
            var settings = configuration.settings()
                    .withExecuteWithOptimisticLocking(true)
                    .withUpdateRecordVersion(true);
            configuration.set(settings);

            var optimistic = DSL.using(configuration);

            var orderToUpdate = Objects.requireNonNull(
                    optimistic.fetchOne(Tables.ORDER, Tables.ORDER.ORDER_ID.eq(order.getOrderID())),
                    "Unable to find the required order"
            );
            orderToUpdate.setStatus(OrderStatus.SUBMITTED);
            return orderToUpdate.update();
        });
}

 

말로 풀이하자면, 주문마다 transaction을 설정을 다음과 같이 해준다.

 

withExecuteWithOptimisticLocking - optimisting locking으로 설정해 데이터의 접근을 transaction을 실행하는 동안 허용하는 것이다. 

withUpdateRecordVersion - Timestamp locking기법의 대안으로, Record versioning을 통해 optimisting locking을 실행합니다. 업데이트를 시도 할 때 새로운 버전을 넣어 중복 업데이트가 되어도 버전이 다르면 업데이트를 중단하거나 에러를 던집니다. 따라서 동시에 2개 이상의 transaction이 같은 레코드를 변경하려고 시도 하면 하나는 통과가 되지만 하나는 막혀 의도치 않은 데이터 수정을 막아줍니다.

 

출처: https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-optimistic-locking/

Optimistic Locking

https://www.jooq.org/doc/latest/manual/sql-execution/crud-with-updatablerecords/optimistic-locking/

Optimistic locking

 

 

문제는 위 Query의 실행 방식이 Select for update에 있다는 것이고, 다른 하나는 update query 하나당 transaction을 계속 생성한다는 것입니다. 

 

var orderToUpdate = Objects.requireNonNull(
                    optimistic.fetchOne(Tables.ORDER, Tables.ORDER.ORDER_ID.eq(order.getOrderID())),
                    "Unable to find the required order"
            );
            orderToUpdate.setStatus(OrderStatus.SUBMITTED);
            orderToUpdate.update();

 

Select을 하여 기존에 있는 레코드를 패칭하고 상태 업데이트를 다시하는 건데 이렇게 되면 Select for update 로 바뀌고 Pessimistic lock으로 바뀌어 해당 레코드를 사용 중에는 다른곳에서 참조를 못하게 되고 해당 레코드가 필요한 프로세서들이 기다려야 하기때문에 잠재적인 성능저하를 가져 올 수 있다. 때문에 위에 optimistic이라고 설정한것이 무의미 하게 되었다. 선언만 optimisitc이라고 했을 뿐, 실제로는 pessimistic lock 기법을 쓰기 때문이다.

 

optimistic lock을 가장한 pessimistic lock

 

그리고 transaction 생성은 수량이 많아지면 비효율적이고 연결 문제가 생길 수가 있다. 그러기에 5000개 이상의 주문 상태를 한번에 업데이트 하기 위해서는 batch기법이 제일 안성 맞춤이다.

 

여러개를 묶어서 효율적으로 한다는 뜻의 batch

 

Batch는 사용하게 되면 한번에 update당 하나의 transaction을 쓸 필요없이 여러개의 update문을 묶음으로 한 transaction에 보내기 때문에 불안정한 네트워크에서 연결이 끊기거나 프로세스가 과부하 되는 것을 방지할 수 있다.

 

 

우선 PSQL transaction은 다음 단계들을 거친다

 

 

시작 ->  커밋 -> 완료

 

중간에 연결이 끊기거나 성공적으로 Commit을 못하면 DB에서 자체적으로 롤백을 한다. 그러면 그만큼 추가적인 확인을 하기때문에 느려진다는 것인데 Batch를 하면 Batch당 하나의 transaction이 있기때문에 transmission의 round-trip이 줄어들어 당연히 반복적인 프로세스가 줄어든다는 의미이다. 더 나아가, 반복적인 SQL reparsing이 줄어들기도 한다.

 

따라서 아래 코드처럼 작성해준다 

 

JOOQ ORM으로 batch 구현. 기술 출처: https://www.jooq.org/doc/latest/manual/sql-execution/batch-execution/

 

참고로 List partition 라이브러리는 Guava를 사용했다. List partitioning은 List를 다시 설정한 크기의 리스트로 다시 쪼개는 역활을 해준다.

<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>31.1-jre</version>
</dependency>

 

public class OrderUpdate {

    private UdatedConfitionStep<OrderRecord> statusUpdateForm() {
       return dsl.update(ORDER)
               .set(ORDER_STATUS, OrderStatus.SUBMITTED)
               .where(ORDER.ORDDER_ID.eq((String) null)
                        .and(ORDER.STATUS.eq(OrderStatus.NEW)));
    }
    private void submitArbitraryOrders() {
        List<Order> orders = new ArrayList<>(); //주문이 있다 가정

        //AMQ로 외부 서비스에 주문 정보 전달

        //S3에 주문 보고서 작성 후, 업로드


        //주문들을 100개 묶음으로 다시 묶어준다.
        List<List<Order>> partitions = Lists.partition(orders, 100);

        AtomicInteger updateCount = new AtomicInteger(0);

        var batchStatement = dsl.batch(statusUpdateForm());

        partitions.forEach(partition -> {
            partition.forEach(order -> {
                batchStatement.bind(OrderStatus.SUBMITTED, order.getOrderID(), OrderStatus.NEW);
            });
            
            updateCount.addAndGet(Arrays.steam(batchStatement.execute()).sum());
        });

        return updateCount.get();

    }
}

 

위 코드를 해석하면 만약 주문이 5000개가 들어오면. 5000개의 주문을 100개의 주문이 한 파티션안에 들어가게 끔 다시 나누어 준다. 그러면 총 50개의 파티션이 생기는데. 한 파티션에 100개의 주문을 batching을 하여 100 개의 update query를 만든 다음 한 transaction으로 보내 commit을 완료하면 끝나는 것이다.

 

그렇게 되면 50번의 transaction을 보내기만 하면 된다!

 

이렇게 본인은 batch update 기법을 대량 주문처리 프로세스에 사용 함으로서 대량 주문 업데이트시 PSQL 연결이 끊기는 불상사를 막을 수 있게 되었다. 물론 100% 막을 수 있는 것은 아니다. 다만 그 확율을 현저히 줄였고 만약 이 적은 량의 batching으로도 연결이 다운되면 Exception 핸들링을 통해 retry를 할 수 있다.

 

 

그렇다고 batch update가 모두 정답은 아니다. 예를들어 반복적인 구문이 아닌 서로 연관된 구문을 batch로 실행하고 혹시나 다른 프로세스가 동인 시간대에 해당 레코드를 참조했을 때, 데이터에 오차가 생긴다.

 

가령 한 프로세서가 한번의 batch transaction을 10 분걸리면 해당 10분동안은 다른 프로세서는 해당 레코드를 참조하여 비지니스 로직을 처리해서는 안된다. 분명 데이터가 달라지고 나중에 문제가 생기기 때문이다. 이런 식으로 batching을 실행 한다면 매우 불안정안 형태의 batching이다. 왜냐하면 commit이 아직 되지 않았기 때문이다.

 

빵가게라고 해보자, 빵 재고가 0개가 되어 빵 10개를 하나의 batch로 굽기 시작했다. 이 batch는 10분밖에 걸리지 않는데, 재고를 확인하는 사람이 batch에 굽고있는 빵을 생각하지 못하고 어? 재고에 빵이 0개네 하고 또다시 빵 10개인 batch를 추가로 굽고 이를 계속 반복하면 문제가 생긴다. 그냥 단순히 10분만 기다리면 되는데 말이다. 그러나 DB는 Commit이 되기 전까지는 batch가 있는 사실을 모른다. 늘 재고가 0이라고 되어 있다고 생각한다. 그래서 batch를 쓰기전 다른 비지니스 로직을 잘 생각해 봐야 한다. 중첩되는 프로세스가 있는지...

 

그래서 일반적으로 batch를 잘 안쓰고 optimistic lock을 쓰거나, 중요한 금전거래나 주문 수량 거래는 pessimistic lock으로 하는것이 중요하다. Optimistic과 Pessimistic lock에 관한 내용은 나중에 실무 사례와 함께 자세히 다루어 보겠다.