[PostgreSQL] Invalid Transaction Termination 에러

 

PostgreSQL의 프로시저로 트랜잭션을 처리하다가 Invalid Transaction Termination 예외가 발생했다.

 

 

Procedure

CREATE TABLE custom (
    cno bigserial,
    title varchar(20) NOT NULL,
    message varchar(200),
    hit INTEGER,
    CONSTRAINT c_pk PRIMARY KEY (cno)
);

CREATE OR REPLACE PROCEDURE p_ex(
    IN v_title VARCHAR(100),
    IN v_message VARCHAR(200),
    IN v_hit integer,
    OUT v_code integer
)
LANGUAGE plpgsql
AS $end$
DECLARE
    count integer := 0;
BEGIN 
    v_code := 200;

    INSERT INTO 
        test.custom (
            title,
            message,
            hit
        )
        VALUES (
            v_title,
            v_message,
            v_hit
        );

    GET DIAGNOSTICS count = ROW_COUNT;

    IF count <= 0 THEN 
        v_code := 400;
    END IF;    

    EXCEPTION
        WHEN integrity_constraint_violation THEN 
            v_code := 1000;
        WHEN OTHERS THEN
            v_code := 2000;

    IF v_code != 200 THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;

END;
$end$;

 

 

 

CustomService

    @Transactional
    public int test(Map<String, Object> param) {

        int code = mapper.test(param);

        if (code == 200) {
            log.info("커밋 성공! result code : {}", code);
//            throw new RuntimeException("의도적인 에러!!!!");
        } else { 
            log.info("커밋 실패! result code : {}", code);
        }

        return code;
    }

 

Spring과 DBeaver 양단에서 트랜잭션 처리를 할 때 상위(Spring)의 서비스 레이어에서 트랜잭션 애노테이션을 걸고 하위(DBMS)에서 트랜잭션 처리를 하고 결과를 Commit하게 됐을 때 트랜잭션이 전파되어 스프링으로 결과가 넘어오게 되고 스프링에서 Rollback 처리를 하면 트랜잭션이 롤백되는 결과를 예상한다.

 

하지만 PostgreSQL에서는 Invalid Transaction Termination 예외를 던진다.

 

 

@Test
void bothEndTransaction() {
    Map<String, Object> map = new HashMap<>();
    map.put("title", "양단 트랜잭션 처리");
    map.put("message", "양단의 트랜잭션 적용 시 예외 확인");
    map.put("hit", 500);

    customService.test(map);

    //Assertions.assertThatThrownBy(() -> {
    //    customService.test(map);
    //}).isInstanceOf(UncategorizedSQLException.class);
}

/*
결과
org.springframework.jdbc.UncategorizedSQLException: 
### Error querying database.  Cause: org.postgresql.util.PSQLException: ERROR: invalid transaction termination

...
*/

 

스프링에서도 UncategorizedSQLException 라는 분류되지 않은 SQL 예외를 나타낸다.

 

Postgresql에서는 PSQL(PROCEDURE, FUNCTION) 내에서 시작된 트랜잭션은 상위타입으로 넘어갈 수 없으며 그 안에서 트랜잭션이 처리되고 종료를 시켜야 한다고 한다.

 

이에 대해서 양단에 트랜잭션을 걸었는데 정상 동작하는 것은 PostgreSQL의 버그이다. (필자도 위의 코드로 테스트를 돌렸을 때 정상작동을 몇 번 했었었다.)

 

PSQL을 사용할 때는 트랜잭션을 처리할 때는 양단에 트랜잭션을 걸지 않도록 주의해야 하며, 양단의 트랜잭션 처리를 할 때는 스프링이나 PSQL 한 쪽을 정해서 처리하도록 해야한다.

 

 

 

 

참조

• https://www.postgresql.org/message-id/CA%2BcYFtuQkq_PR%2BEXocnEL-nxDXd_uz5Wf7Eb2SLrQN6gjA-ipA%40mail.gmail.com
• https://stackoverflow.com/questions/59159091/invalid-transaction-termination