[Trouble Shooting - Transaction과 DB Session] DB 특정 테이블 (row) 업데이트 안되는 문제

💥 개요

각자 개발된 프로젝트를 병합(리펙토링)하는 과정에서 요구사항에 포함된 신규 API를 구현하는 도중, DB 특정 테이블의 Row에서 Update가 Excution time만 증가하고 update는 실행되지 문제가 발생함

단순 update가 되지 않음

 

---

 

❓ 문제 원인

레거시 프로젝트 어플레케이션 로직에서 트랜잭션을 실행하고, update를 하는데 성공시 1이 return되고 실패시 -1이 반환되는 코드 개발 중간 테스트하던 도중 아래 코드와 같이 문제가 발생함.

public class JdbcActorDao implements ActorDao {

    private JdbcTemplate jdbcTemplate;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }
	
    //예시 업데이트 메서드, 업데이트 완료시 반영된 행이 등록됨
    public int[] batchUpdate(final List<Actor> actors) {
        List<Object[]> batch = new ArrayList<Object[]>();
        for (Actor actor : actors) {
            Object[] values = new Object[] {
                    actor.getFirstName(), actor.getLastName(), actor.getId()};
            batch.add(values);
        }
        return this.jdbcTemplate.batchUpdate(
                "update t_actor set first_name = ?, last_name = ? where id = ?",
                batch);
    }

    // ... additional methods
}

@Service
@Requiredargsconstructor
public class JdbcActorService {

    private final JdbcActorDao jdbcActorDao;

    public void actorUpdate(final List<Actor> actors) {
        //트랜잭션 시작됨
        
        int result = jdbcActorDao.batchUpdate(actors);
        
        if(result == 1) {
        	//커밋
            return;
        }else if(result < 0) {
        	//롤백 후 exception 처리
        }
    }
}

위 코드는 스프링 공식 홈페이지에서 제공하는 Data access with JDBC의 예시 코드, 그리고 아래는 프로젝트에서 발생한 상황을 예시로 구현해보았다. 여기서 if 조건문을 잘못 생성하게 되어, Transaction이 실행되었고, 로직이 정상적으로 실행되었지만 commit과 rollback이 되지 않았기 때문에 해당 트랜잭션은 타임아웃이 나게 된다.

https://docs.spring.io/spring-framework/docs/4.3.20.RELEASE/spring-framework-reference/html/jdbc.html

19. Data access with JDBC

 

하지만 해당 db에서는 비즈니스 로직 상 트랜잭션의 timeout이 긴 단위로 설정이 되어 있기 때문에 브라우저의 개발자 도구에서 디버깅을 하게 된다면 pending으로 서버에서 응답을 계속해서 기다리게 되고, 디버깅을 하게 된다면 정상적인 흐름이라고 인식하기 때문에 해당 문제가 발생하였다.

Transaction이 commit 혹은 rollback되지 않았기 때문에 해당 Table에서 트랜잭션의 특징인 ACID중 독립성(Isolation)으로 인해 타임아웃이 나기 전 까지 update된 row에 lock을 걸어 다른 트랜잭션에서 사용하지 못하도록 대기하게 만들고, 트랜잭션이 커밋 혹은 롤백될 때까지 모든 lock을 유지합니다.

Oracle에서 설명하는 Transaction blocking_deadlocks문서의 Lock Lifetime

https://docs.oracle.com/cd/E17076_05/html/gsg_txn/CXX/blocking_deadlocks.html

---

 

✅ 해결 방법

1. 해당 트랜잭션의 세션 disconnect
2. db restart
3. timeout까지 대기

우선 2번은 가장 확실하지만, 기존의 작업들과 24시간 배치성 작업들이 취소되는 사실상 불가능한 방법입니다.

그럼 1번과 3번인데 보통의 db에서 default timeout은 5~10second로 이러한 오류가 난다고 해도 보통 일시적으로 문제가 발생했다가, 다시 정상적으로 작동하여 크게 문제를 못느끼는 부분이 대부분이라고 생각됩니다.

하지만 위에서 언급했지만 해당 db는 기존 개발된 에이전트들과, 어플리케이션에서 session을 아주 길게 가져갈 수 있도록 설계된 시스템이기 때문에 3번은 사실상 기약없는 기다림이 되어버릴 가능성이 있다고 판단하였습니다.

 

💬 해당 트랜잭션의 세션 disconnect

오라클 사용 중 발생한 문제이기 때문에 오라클을 기준으로 정리하겠습니다.

오라클 세션은 사용자가 db에 접속을 하게되면 세션이 생성되고, 사용자가 db에 접속 종료할 때 까지 유지됩니다. 각 세션은 고유한 SID와 SERIAL#을 가지고 있습니다. 시리얼 번호는 세션 종료 후 다른 세션이 동일한 SID를 갖고 시작하는 경우 정확한 식별을 위해 제공됩니다.

1. Lock 찾기                         
      SELECT B.USERNAME USERNAME, C.SID SID, C.OBJECT OBJECT,A.SQL_TEXT SQL
      FROM   V$SQLTEXT A, V$SESSION B,V$ACCESS C
      WHERE  A.ADDRESS    = B.SQL_ADDRESS
      AND    A.HASH_VALUE = B.SQL_HASH_VALUE
      AND    B.SID        = C.SID
      AND    C.OWNER      = 'USER ID' ;
   혹은
   select a.sid, a.serial#, b.type, c.object_nam
      from   V$session a, v$lock b, dba_objects c
      where  a.sid = b.sid
      and    b.id1 = c.object_id
      and    b.type = 'TM'
      and    c.object_name = 'FSFE1_STRACK_INFO'
   
   
2. Lock 삭제
    alter system kill session '150,101';   --세션ID, Serial#
   
   
3. Lock이 발생한 Object 확인
   SELECT object_id, object_type
       FROM dba_objects
     WHERE object_name='테이블 / 인덱스 이름';
   
4. Lock을 잡고 있는 세션 Id 확인
   SELECT *
       FROM v$locked_object
     WHERE object_id='이전에 검색한 object_id';
   
   
5. Lock을 잡고 있는 세션 Serail Number 확인
   SELECT sid, serial#, command, taddr
       FROM v$session
     WHERE sid='4번의 세션 ID 번호';
   
6. 세션 강제종료
   ALTER SYSTEM KILL SESSION '<세션 ID, Serial#>';
   
   
7. 최종 확인 및 Rollback 체크
   SELECT used_ublk
       FROM v$transaction
     WHERE ADDR='<5번의 TADDR>';
   
   

출처

• https://farmerkyh.tistory.com/302
• https://ko.wikipedia.org/wiki/ACID
• https://docs.oracle.com/cd/E17076_05/html/gsg_txn/CXX/blocking_deadlocks.html

• https://docs.spring.io/spring-framework/docs/4.3.20.RELEASE/spring-framework-reference/html/jdbc.html