SQL BOOSTER - 트랜잭션

 

1. 트랜잭션

 

• 트랜잭션은 반드시 한 번에 처리되어야 하는 논리적인 작업 단위다.
• 하나의 트랜잭션은 여러 개의 작업으로 구성될 수 있다. 
• 트랜잭션을 종료하는 명령어에는 COMMIT과 ROLLBACK이 있다. COMMIT은 트랜잭션 과정 중에 변경된 데이터를 모두 반영하고 종료하는 명령어다. 반면에 ROLLBACK은 트랜잭션 과정에서 진행된 데이터를 모두 종료하는 명령어다. 

1.1 트랜잭션 COMMIT

 

SQL BOOSTER

A계좌에서 B계좌로 500원을 이체하는 트랜잭션 COMMIT 한 경우다.

트랜잭션을 COMMIT 했으므로 A계좌와 B계좌의 잔액이 모두 변경되었다.

 

1.2 트랜잭션 ROLLBACK

 

계좌이체 트랜잭션을 ROLLBACK 처리한 경우 A계좌에서 차감한 작업이 취소된다. 

 

1.3 COMMIT을 잘 못 사용한 예 

 

COMMIT을 잘 못 사용하면 부정확한 데이터가 만들어진다. 

A계좌 잔액이 500원으로 변경되었다. ROLLBACK을 해야하는 상황에서 COMMIT을 수행해 A계좌에서만 500원이 사라졌다.

SQL BOOSTER

SQL 문장에서 에러가 발생해도, 데이터베이스는 전체 트랜잭션을 ROLLBACK하지 않는다. 

에러가 난 문장만 ROLLBACK될 뿐이고, 트랜잭션은 여전히 살아있다. 

반드시 ROLLBACK을 실행해야만 트랜잭션 전체를 되돌릴 수 있다. 

 

1.4 트랜잭션의 시작과 끝 

 

COMMIT이나 ROLLBACK을 만난 후, 새로운 데이터 변경 SQL이 실행되면, 그 순간 새로운 트랜잭션이 시작된다.

SQL BOOSTER

---

2. 트랜잭션 요점 정리 

 

1. 트랜잭션은 한 번에 이루어져야 하는 작업 단위다.

2. 트랜잭션은 COMMIT이나 ROLLBACK으로 종료가 이루어진다.

3. COMMIT으로 종료될 경우, 트랜잭션에서 변경된 데이터들은 모두 DB에 반영된다.

4.ROLLBACK로 종료될 경우, 트랜잭션 시작 이전으로 데이터들을 복구한다.

5. 트랜잭션 내에 에러가 발생했다고 해서 자동으로 ROLLBACK이 수행되지 않는다.

---

3. 트랜잭션 고립화 수준 - READ COMMITTED

 

- 트랜잭션 고립화 수준이란, 하나의 트랜잭션에서 작업 중인 데이터가 다른 트랜잭션에 영향을 받지 않는 정도를 뜻한다.

- 또는, 하나의 트랜잭션에서 작업 중인 데이터를 다른 트랜잭션이 어느 정도까지 접근할 수 있는지에 대한 정도

- 가장 낮은 단계의 고립화 수준은 한 트랜잭션에서 변경 중인 데이터를 다른 트랜잭션에서 접근할 수 없다.

- 반대로, 높은 단계로 설정하면 조회만 이루어진 데이터도 다른 트랜잭션이 변경할 수 없게 한다. 

- 트랜잭션은 데이터베이스의 동시성과 밀접한 연관이 있다. 일반적으로 고립화 수준을 낮게 설정하면, 동시성은 좋아진다.

- 특정 수준의 고립화 수준을 사용해 데이터의 정확도를 확보하고, 트랜잭션을 최적화하여 동시성을 높여야 한다.

 

-동시성은 다음 4단계가 있다.

1. READ UNCOMMITTED
2. READ COMMITTED
3. REPRATABLE READ
4. SERIALIZABLE READ

가장 많이 사용하는 트랜잭션 수준은 READ COMMITTED이다. 다만, 이 만으로는 데이터 정확성을 확보하기에 어려움이 있다.

따라서 SELECT ~ FOR UPDATE도 알고 있어야 한다. 

 

READ COMMITED는 변경된 데이터에 로우 단위로 변경 락을 생성한다. 

데이터의 변경 락을 소유한 트랜잭션 외에 다른 트랜잭션에서는 해당 데이터를 변경할 수 없으며, 같은 데이터 조회를 요청하면 변경 이전의 데이터만 조회 가능하다.

 

3.1 READ COMIITED : UPDATE-SELECT 

 

- 첫 세션에서 UPDATE에 대해 COMMIT하지 않으면, 두번째 세션은 UPDATE 전 데이터만 읽을 수 있다.

  - 이름따라 COMMIT 된 데이터만 READ할 수 있다.

- 두 번째 세션은 대기 상태에 빠지게 된다. (WAIT 상태)

SQL BOOSTER

 

3.2 READ COMMITTED: INSERT-INSERT 테스트 

 

- INSERT역시 COMMIT이 되어야만 다른 세션에서 조회할 수 있다.

- 트랜잭션에서 사용하지 않는 행 정보에는 INSERT 가능하다.

- ACC01에 INSERT하면서, ACC01에 다른 세션이 INSERT는 못하지만, 

- ACC01에 INSERT하면서, ACC04에 INSERT는 가능하다.

 

 

3.3 READ-COMMITED의 특징 

 

1. 한 트랜잭션이 변경 중 데이터는 다른 트랜잭션에서 변경 전 데이터만 조회할 수 있다.

(변경 중 데이터: UPDATE,DELETE,INSERT 후에 COMMIT이나 ROLLBACK 되지 않은 데이터)

2. 한 트랜잭션이 변경 중 데이터는, 다른 트랜잭션에서 동시에 변경할 수 없다.

: 늦게 UPDATE를 시도한 세션은 대기 상태에 빠지게 된다. (DELETE도 마찬가지)

3. UPDATE가 대기 상태에 빠지면 선행 트랜잭션의 처리에 따라 UPDATE결과가 다르다.

4. 같은 키 값을 가진 데이터가 동시에 입력되면, 후행 트랜잭션은 대기 상태에 빠진다.

:여기서 키는 Primary Key와 Unique Key 모두를 이야기한다.

:선행 트랜잭션의 처리에 따라 후행 트랜잭션은 중복 에러가 발생할 수 있다.

5. 에러가 발생해도 트랜잭션 전체가 자동 ROLLBACK 되지 않는다.

---

4. 락(LOCK)

 

4.1 락이란?

 

락은 말 그대로 데이터에 잠금을 걸어 놓는 장치다. 데이터를 잠그면, 데이터를 잠금 세션 외에 다른 세션들은 잠긴 데이터에 접근할 수 없다. 

락을 완벽하게 이해하려면 락의 종류부터 사용되는 상황까지 모두 알고 있어야 한다.

중요하게 알아야 할 것은 데이터 변경시 발생하는 락이다. 

데이터를 변경하면, 해당 로우에 잠금을 걸고, 트랜잭션이 COMMIT 또는 ROLLBACK 할 때까지 유지한다.

변경 락이 발생한 로우는 락을 생성한 트랜잭션만 변경 작업을 할 수 있다. 

 

4.2 SELECT ~ FOR UPDATE

 

• READ COMMITTED 수준에서 데이터 일관성을 확보하려면, SELECT ~ FOR UPDATE를 활용해야 한다. 
• 조회된 로우 데이터에 변경 락을 생성해 다른 트랜잭션이 같은 로우를 변경하는 것을 막는다. 

*SELECT부터 트랜잭션 가져가려고 하는 것! -> 변경의 시작점 지목

 

• SELECT ~FOR UPDATE는 데이터 조회 시점부터 변경 락을 생성해 데이터 일관성 확보에 도움을 준다.
• 실제로 계좌 이체, 상품 매매, 재고 입출고와 같은 프로세스에 사용되는 기술이다. 
• 다만, 동시성을 떨어뜨리는 단점이 있음으로, SQL과 트랜잭션 최적화가 필요하다. 
• 락이 트랜잭션 종료 시점까지 유지되므로, 락을 얼만큼 빨리 해소해 주느냐에 따라 데이터베이스의 동시성이 좌우된다.
• NOWAIT과 WAIT SECONDS 기능을 활용해, 예외가 발생하면 트랜잭션을 ROLLBACK처리하고 빠져나오거나 재처리를 유도할 수 있다.

---

5. 대기상태

 

대부분의 시스템에서 일정 수만큼의 세션을 데이터베이스와 미리 연결해 놓고, 여러 명의 사용자가 세션을 공유한다.

데폴링을 사용하는 구조에서 연결된 세션이 모두 대기 상태에 빠지면 시스템은 작동하지 않게 된다. 

세션이 대기 상태에 빠지는 것은 느린 SQL과 연관이 깊다. 

한 트랜잭션이 특정 데이터에 대한 LOCK을 가져가면, 다른 세션은 대기상태에 빠진다. 

 

SELECT * T1.*FROM M_ACC FOR UPDATE;

 

FOR UPDATE구문에 의해 M_ACC의 데이터에는 어떤 변경도 불가능하다. 해당 조회를 수행한 후 어떠한 COMMIT이나 ROLLBACK이 없었다면, 모든 로우에 대해 접근 불가능 상태에 빠지게 된다. 

*단순히 조회를 위해 FOR UPDATE 구문을 사용하지말라!! 

 

시스템의 동시성을 높이려면 불필요한 SELECT FOR UPDATE 사용을 피하고, 트랜잭션은 항상 제대로 종료해야 한다. 그리고 트랜잭션은 최대한 빠르게 처리되도록 해야 한다. 

 

---

 

6. 데드락(DEAD-LOCK, 교착상태)

 

6.1 데드락이란?

첫 세션도 두 번째 세션의 작업이 끝나기를 기다리고, 두 번째 세션도 첫 세션의 작업이 끝나기를 기다리는 상태이다. 

대기 상태와 달리 더는 트랜잭션을 진행할 수 없는 상태를 의미한다. 

 

UPDATE M_ACC SET BAL_AMT = 5000 WHERE ACC_NO IN ('ACC1','ACC2'); 
COMMIT;

--세션 1

-- 첫 세션이 T1.BAL_AMT를 가져감
1 SELECT T1.BAL_AMT FROM M_ACC T1 WHERE T1.ACC_NO = 'ACC1' FOR UPDATE;

-- ACC1에서 잔액 마이너스
2 UPDATE M_ACC T1 SET T1.BAL_AMT = T1.BAL_AMT - 2000 WHERE T1.ACC_NO ='ACC1';

5. UPDATE M_ACC T1 SET T1.BAL_AMT = T1.BAL_AMT + 2000 WHERE T1.ACC_NO = 'ACC2';

--세션2 

3. SELECT T1.BAL_AMT M_ACC T1 WHERE T1.ACC_NO = 'ACC2' FOR UPDATE;

4. UPDATE M_ACC T1 SET T1.BAL_AMT = T1.BAL_AMT - 3000 WHERE T1.ACC_NO = 'ACC2';

6. UPDATE M_ACC T1 SET T1.BAL_AMT = T1.BAL_AMT + 3000 WHERE T1.ACC_NO = 'ACC1';

//..

 

SQL을 살펴보면, 5번 SQL은 3번 4번 SQL에 의해 LOCK이 걸리고, 6번 SQL은 1번 5번에 의해 대기 상태에 빠진다

첫 번째 세션은 두 번째 세션이 ACC2의 락 해제를 기다리고, 두 번째 세션은 첫 번째 세션이 ACC1에 대한 락을 해제하기를 기다리며 대기 상태에 빠진다. 

SQL BOOSTER

데드락을 피하려면, 트랜잭션 시작 부분에서 ACC1과 ACC2에 동시에 락을 생성하면 된다.

-- 세션1
--첫 번째 세션, FOR UPDATE구분으로 ACC1, ACC2의 잔액 확인하면서 락 가져가기
SELECT T1.ACC_NO, T1.BAL_AMT FROM M_ACC T1
WHERE T1.ACC_NO IN('ACC1','ACC2') FOR UPDATE;

--2 ACC1에서 잔액 마이너스 
UPDATE M_ACC T1 SET T1.BAL_AMT = T1.BAL_AMT - 2000 WHERE T1.ACC_NO = 'ACC1';


-- 세션2 --3 
SELECT T1.ACC_NO, T1.BAL_AMT FROM M_ACC T1
WHERE T1.ACC_NO IN ('ACC2','ACC1') FOR UPDATE;


--4 ACC2의 잔액 플러스 

UPDATE M_ACC T1 SET T1.BAL_AMT = T1.BAL_AMT + 2000
WHERE T1.ACC_NO = 'ACC2';

COMMIT;

 

첫 번째 세션의 1번 SQL을 보면 ACC1과 ACC2를 동시에 SELECT ~ FOR UPDATE하고 있다. 

이로 인해 첫 번째 세션은 ACC1과 ACC2의 변경 락을 동시에 소유하게 된다. 

---

 

7. 트랜잭션 최소화 

 

데이터를 변경하는 트랜잭션은 변경 락을 발생하고, 이로 인해 다른 트랜잭션을 대기 상태로 만든다.

따라서 SQL 단위로 최적화가 필요하며, 유사하게 반복 실행되는 SQL을 합쳐서 트랜잭션 길이를 최소화해야 한다.

 

SELECT T1.ACC_NO, T1.BAL_AMT
FROM M_ACC T1
WHERE T1.ACC_NO IN ('ACC1','ACC2') FOR UPDATE;

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 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