SQL 스터디 7주차!

트랜잭션

• 반드시 한 번에 처리되어야 하는 논리적인 작업 단위 (더는 쪼갤 수 없는 작업 단위)
• 트랜잭션은 COMMIT(반영)이나 ROLLBACK(취소)로 종료가 이루어진다.
• COMMIT으로 종료될 경우, 트랜잭션에서 변경된 데이터들은 모두 DB에 실제 반영된다.
• ROLLBACK으로 종료될 경우, 트랜잭션 시작 이전으로 데이터들은 복구가 된다.
• 트랜잭션 내에 에러가 발생했다고 해서 자동으로 ROLLBACK이 수행되지 않는다.

트랜잭션 고립화 수준 READ COMMITTED

• 하나의 트랜잭션에서 작업 중인 데이터가 다른 트랜잭션에 영향을 받지 않는정도를 뜻한다.
• 하나의 트랜잭션에서 작업중인 데이터를 다른 트랜잭션이 어느정도 접근할 수 있는지에 대한 정도이다.

데이터베이스의 동시성

• 고립화 수준이 낮을 때 = 동시성이 좋을 때: 한 트랜잭션에서 변경 중인 데이터를 다른 트랜잭션에서 접근할 수 있다.
• 고립화 수준이 높을 때 = 동시성이 나쁠 때: 조회만 이루어진 데이터도 다른 트랜잭션이 변경할 수 없게 된다.
• 동시성: 데이터베이스를 동시에 여러 명이 사용할 수 있는 능력
• 동시성이 좋다(높다)는 것 -> 많은 사용자가 동시에 데이터베이스를 사용할 수 있다는 것
• 데이터의 정확성을 확보하려면 고립화 수준을 높여야 한다.
  • 하지만 동시성이 떨어지는데, 동시성이 떨어지면 여러 상요자가 동시에 사용할 수 없다.
  • 정확도를 확보하고, 동시성도 높이려면 트랜잭션에서 불필요한 작업은 제거해 간결하게 만들고, SQL을 최적화해서 구현해야 한다.

트랜잭션 고립화 수준의 종류

1. READ UNCOMMITTED (가장 낮은 고립화 수준)
2. READ COMMITTED (오라클 기본 고립화 수준. 오라클에는 위의 1 수준이 없다)
3. REPEATABLE READ
4. SERIALIZABLE READ (가장 높은 고립화 수준)

1. READ COMMITTED

• COMMIT된 데이터만 READ 할 수 있다.
• 같은 키값(PRIMARY KEY, UNIQUE KEY)에 대해 동시에 데이터가 입력되면 후행 트랜잭션은 대기 상태에 빠진다
  • 대기: 선행 트랜잭션이 데이터를 변경하고 있으므로, 후행 트랜잭션이 데이터에 접근하지 못하고 기다리는 상태 -> 동시성을 떨어뜨리는 요인
• UPDATE가 대기 상태에 빠지면 선행 트랜잭션의 처리에 따라 UPDATE결과가 다르게 된다.
• 에러가 발생해도 트랜잭션 전체가 자동 ROLLBACK 되지는 않는다.

LOCK

• 데이터에 잠금을 걸어놓는 장치. 데이터를 잠근 세션 외에 다른 세션들은 잠긴 데이터에 접근할 수 없다.
• 데이터를 변경하면 해당 로우에 락을 걸고, 트랜잭션이 COMMIT 또는 ROLLBACK될 때까지 유지된다.

SELECT FOR UPDATE

• 조회된 로우 데이터에 변경 락을 생성해 다른 트랜잭션이 같은 로우를 변경하는 것을 막는다.

예시

SELECT T1.BAL_AMT FROM M_ACC T1
WHERE T1.ACC_NO = 'ACC1'
FOR UPDATE;

데이터 조회 시점부터 변경 락을 발생해 데이터 일관성을 확보할 수 있게 해준다. 계좌이체, 상품 매매, 재고 입출고 같은 프로세스에 사용한다.

• 락을 얼만큼 빨리 해소해주느냐에 따라 데이터베이스의 동시성이 좌우된다.
• NOWAIT 옵션: 대기상태에 빠지자마자 예외 발생
• WAIT SECONDS 옵션: 대기할 초를 설정할 수 있다. 예외가 발생하면 트랜잭션을 ROLLBACK으로 처리하고 빠져나오거나 재처리르 유도할 수 있다.

대기 상태

• 대부분의 시스템은 데이터베이스 연결 및 접근에 Polling처리 (미리 데이터베이스와 세션을 연결해놓고, 여러 명의 사용자가 세션을 공유해 사용하는 방법)를 한다.
• 폴링을 사용하는 구조에서 연결된 세션이 모두 대기 상태에 빠지면 시스템은 더는 작동하지 않게 된다. 느린 SQL이 많을 수록, 제대로 종료되지 않은 트랜잭션이 많을 수록 대기 시간이 길어진다.

만약 계좌정보 조회시, SELECT FOR UPDATE를 잘못 사용하여 이렇게 사용했다면, 계좌 이체 세션을 모두 대기 상태에 빠지게 할 수 있다…

SELECT T1.* FROM M_ACC T1 FOR UPDATE;

또는 COMMIT/ ROLLBACK을 빠뜨려서 시스템 장애에 빠질 수도 있다. 트랜잭션은 항상 제대로 종료되어야 한다.

데드락 DEAD-LOCK 교착 상태

• 첫 번째 세션이 두 번째 세션의 작업이 끝나기를 기다리고 있고, 두 번째 세션도 첫 번째 세션의 작업이 끝나기를 기다리고 있는 상태
• 대기 상태는 정확성을 위해 기다리는 정상적인 상태이고 데드락은 더는 트랜잭션을 진행할 수 없는 상태다.
• 데드락을 피하려면, 트랜잭션 시작 부분에서 락을 생성해주어서 변경 락을 한 세션이 소유하고 있어야 한다.

트랜잭션 최소화

• 유사하게 반복 실행되는 SQL을 합쳐서 트랜잭션 길이 최소화 하기

ACC1 -> ACC3 2,000원 계좌이체 트랜잭션 SQL

1
2
3
1. ACC1, ACC2 잔액 확인
2. ACC1의 잔액이 이체 금액 이상이면 이체 수행
3. ACC2의 잔액 플러스

2,3 번 UPDATE를 하나로 통합하기

-- 1. ACC1, ACC2 잔액 확인
SELECT T1.ACC_NO, T1.BAL_AMT
  FROM M_ACC T1
 WHERE T1.ACC_NO IN ('ACC1', 'ACC2') FOR UPDATE;

-- 2. ACC1의 BAL_AMT가 이체 금액보다 작으면 ROLLBACK처리
-- 3. ACC2가 존재하지 않는다면 ROLLBACK처리
-- 4. ACC1, ACC2의 잔액을 동시에 처리
UPDATE M_ACC T1
   SET T1.BAL_AMT = T1.BAL_AMT +
       CASE WHEN T1.ACC_NO = 'ACC1' THEN -1 * 2000 -- 보내는 쪽
            WHEN T1.ACC_NO = 'ACC2' THEN 1 * 2000 -- 받는 쪽
            END
 WHERE T1.ACC_NO IN ('ACC1', 'ACC2');
COMMIT;

방어로직

• 데이터베이스의 동시성을 높이면서 데이터의 정확성을 확보하는 방법
• 위의 계좌이체에 방어로직을 사용한다면, 조회 시점에 잔액을 저장해 놓고, 변경 시점에 잔액이 변경되지 않았을 때만 UPDATE하도록 SQL을 구현하는 것이다.

-- 1. ACC1, ACC2 잔액 확인
SELECT T1.ACC_NO, T1.BAL_AMT
  FROM M_ACC T1
 WHERE T1.ACC_NO IN ('ACC1', 'ACC2');
-- ACC1 BAL_AMT = @FROM_BAL_AMT, ACC2 BAL_AMT = @TO_BAL_AMT

-- 2. ACC1의 BAL_AMT가 이체 금액보다 작으면 ROLLBACK처리
-- 3. ACC2가 존재하지 않는다면 ROLLBACK처리
-- 4. ACC1, ACC2의 잔액을 동시에 처리 (조회때 저장한 잔액과 현재 잔액이 같을 때만 처리하기)
UPDATE M_ACC T1
   SET T1.BAL_AMT = T1.BAL_AMT +
       CASE WHEN T1.ACC_NO = 'ACC1' THEN -1 * 2000 -- 보내는 쪽
            WHEN T1.ACC_NO = 'ACC2' THEN 1 * 2000 -- 받는 쪽
            END
 WHERE T1.ACC_NO IN ('ACC1', 'ACC2')
   AND T1.BAL_AMT = CASE WHEN T1.ACC_NO = 'ACC1' THEN @FROM_BAL_AMT
                         WHEN T1.ACC_NO = 'ACC2' THEN @TO_BAL_AMT
                         END
;

-- 5. UPDATE건수가 2건일 때만 COMMIT 처리
COMMIT;

불필요한 트랜잭션의 분리

• 트랜잭션과 주요 연관성이 떨어지는 작업을 분리하면 트랜잭션 실행 시간을 줄일 수 있다. 개발한 트랜잭션에서 떼어내도 상관 없는 작업이 있는지 고민해볼 필요가 있다.

reference

SQL BOOSTER

• Previous
  Oracle - NL조인, 머지조인, 해시조인