1️⃣ 트랜잭션이란?
트랜잭션이란, 데이터베이스의 최소 논리적 연산단위이다. 즉, 밀접히 관련되어 분리될 수 없는 한 개 이상의 데이터베이스 조작을 가리키며 세부적인 연산들의 집합을 가리킨다.
트랜잭션에는 가장 중요한 특성이 있는데, 바로 ⭐ ""ALL OR NOTHING"" 개념이다. 대표적인 예로, 은행에서 계좌이체 상황을 하나의 트랜잭션으로 볼 수 있다.
STEP1. 100 번 계좌의 잔액에서 10,000 원을 뺀다.
STEP2. 200 번 계좌의 잔액에 10,000 원을 더한다
계좌이체라는 작업 단위는 위의 두 개의 업데이트가 모두 성공적이어야 종료가 된다. 둘 중 하나라도 실패할 경우 계좌이체는 원래의 금액을 유지하고 있어야 하기 때문에, 트랜잭션은 전부 적용하거나, 전부 취소해야 하는 것이다.
✅ 트랜잭션의 특성
1) 원자성(atomicity)
트랜잭션에 정의된 연산들은 모두 성공적으로 실행되던지, 아니면 전혀 실행되지 않은 상태로 남아 있어야 한다(all or nothing).
2) 일관성(consistency)
트랜잭션 실행 결과로 DB 상태에 모순이 없고, 실행 후에도 일관성이 유지되어야 한다.
3) 고립성(isolation)
트랜잭션 실행 중 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근할 수 없다(부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다).
4) 지속성(durability)
트랜잭션이 그 실행을 성공적으로 완료하면 그 결과는 영구적으로 보장되어야 한다.
✅트랜잭션 잠금 기능
또한, 트랜잭션 중간에 다른 사용자가 데이터를 변경하지 못하도록(원자성 충족) 데이터베이스는 다양한 레벨의 잠금 기능을 제공한다.
🔓잠금은, 기본적으로 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한하는 기법이다.
잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만 독점적으로 접근할 수 있고, 다른 트랜잭션으로부터 간섭이나 방해를 받지않는 것이 보장된다. 그리고 잠금이 걸린 데이터는 잠금을 수행한 트랜잭션만이 해제할 수 있다.
트랜잭션의 대상이 되는 SQL문은 데이터를 수정(ex)UPDATE/INSERT/DELETE)하는 DML문이며, COMMIT/ ROLLBACK/ SAVEPOINT 이 3가지 명령어를 TCL(TRANSACTION CONTROL LANGUAGE)으로 분류한다.
2️⃣ 커밋(Commit)
커밋이란, 입력/수정/삭제 등 변경한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 트랜직션을 완료하는 명령어이다.
커밋 이전의 데이터 상태는 다음과 같다.
💡 커밋 이전 상태
1) 단지 메모리 BUFFER 에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
2) 현재 사용자는 SELECT 문장으로 결과를 확인 가능하다
3) 다른 사용자는 현재 사용자가 수행한 명령의 결과를 볼 수 없다.
4) 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수
없다.
즉, ⭐변경된 데이터는 커밋 하기 이전에는 DB에 실제로 반영되지 않으며 커밋 이후(트랜잭션 종료)에 반영이 된다. COMMIT은 이렇게 INSERT, UPDATE, DELETE 문장을 사용한 후 이런 변경 작업이 완료되었음을 DB에 알려 주기 위해 사용한다.
COMMIT 이후의 데이터 상태는 다음과 같으며 COMMIT으로 하나의 트랜잭션 과정을 종료한다.
💡 커밋 이후 상태
1) 데이터에 대한 변경 사항이 데이터베이스에 반영된다.
2) 이전 데이터는 영원히 잃어버리게 된다.
3) 모든 사용자는 결과를 볼 수 있다.
4) 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
✅SQL Server에서의 트랜잭션 방식
참고로, DML을 실행하는 경우 DBMS가 트랜잭션을 내부적으로 실행하여 사용자가 직접 COMMIT 혹은 ROLLBACK을 수행해주어야 트랜잭션이 종료되는 Oracle과 달리, SQL Server는 기본적으로 AUTO-COMMIT 모드이기 때문에 DML 수행 후 사용자가 COMMIT 이나 ROLLBACK 을 처리할 필요가 없다.
#Oracle
Oracle UPDATE PLAYER SET HEIGHT = 100;
COMMIT;#SQL Server
SQL Server UPDATE PLAYER SET HEIGHT = 100;1) AUTO COMMIT
SQL Server 의 기본 방식이며, DML, DDL 을 수행할 때마다 DBMS 가 트랜잭션
을 컨트롤하는 방식이다. 명령어가 성공적으로 수행되면 자동으로 COMMIT 을 수행하고 오류가 발생하면 자동으로 ROLLBACK 을 수행한다.
2) 암시적 트랜잭션
Oracle 과 같은 방식으로 처리된다. 즉, 트랜잭션의 시작은 DBMS 가 처리하고
트랜잭션의 끝은 사용자가 명시적으로 COMMIT 또는 ROLLBACK 으로 처리한다. 인스턴스 단위 말고 세션 단위로 설정하기 위해서는 세션 옵션 중, SET IMPLICIT TRANSACTION ON 을 사용하면 된다.
3) 명시적 트랜잭션
트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다. BEGIN (TRANSACTION) 으로 트랜잭션을 시작하고 COMMIT/ROLLBACK(TRANSACTION) 으로 트랜잭션을 종료한다. ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK 이 수행된다.
3️⃣ 롤백(Rollback)
ROLLBACK을 실행하면 데이터에 대한 변경 사용을 모두 취소하고 트랜잭션을 종료한다. 즉, INSERT/UPDATE/DELETE문의 모든 작업을 취소하고, 이전 COMMIT한 곳까지만 복구된다.
🔓또한, ROLLBACK을 실행하면 관련된 행에 대한 잠금이 풀리고(UNLOCK) 다른 사용자도 DB 행을 조작할 수 있다.
✅ SQL Server에서의 롤백
SQL Server 는 AUTO COMMIT 이 기본 방식이므로, 임의적으로 ROLLBACK 을 수행하려면 명시적으로 트랜잭션을 선언해야 한다.
# 데이터 수정
SQL Server BEGIN TRAN UPDATE PLAYER SET HEIGHT = 100;
#롤백
ROLLBACK;롤백 이후 데이터 상태는 다음과 같다.
💡롤백 이후 상태
1) 데이터에 대한 변경 사항은 취소된다.
2) 이전 데이터는 다시 재저장된다.
3) 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.
✅COMMIT과 ROLLBACK 효과
그렇다면, COMMIT과 ROLLBACK을 함으로써 어떤 효과를 얻을 수 있을까?
바로 ⭐데이터 무결성이 보장된다는 것이다. 데이터의 변경을 발생시키는 입력(INSERT), 수정(UPDATE), 삭제(DELETE) 수행시 그 변경되는 데이터의 무결성을 보장하는 것이 두 명령어의 목적이다. 또한, 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능하며 논리적으로 연관된 작업을 그룹핑하여 처리 가능하다.
✅트랜잭션 종료가 자동으로 이루어지는 경우?
다음의 경우에는 COMMIT 과 ROLLBACK 을 실행하지 않아도 자동으로 트랜잭션이 종료된다.
💡 트랜잭션 자동 종료
1) DML 문장 이후에 커밋 없이 DDL 문장이 실행되면 DDL 수행 전에 자동으로 커밋
2) 데이터베이스를 정상적으로 접속을 종료하면 자동으로 트랜잭션이 커밋
3) 애플리케이션의 이상 종료로 데이터베이스와의 접속이 단절되었을 때는 트랜잭션이 자동으로 롤백
4️⃣ 저장점(Savepoint)
SAVEPOINT는 트랜잭션을 작게 분할하여 관리하는 것으로, 트랜잭션에 포함된 전체 작업을 롤백하는 것이 아니라 현 시점에서 지정된 위치까지만 트랜잭션을 ROLLBACK 할 수 있다.
복수의 저장점을 정의할 수 있으며, 동일이름으로 저장점을 정의했을 때는 나중에 정의한 저장점이 유효하다.
또한, 복잡한 대규모 트랜잭션에서의 에러가 발생했다면 저장점까지의 트랜잭션만 롤백하여 실패한 부분에 대해서만 다시 실행할 수 있다는 장점이 있다.
SAVEPOINT SVPT1; #저장점 명
ROLLBACK TO SVPT1;위와 같이 롤백(ROLLBACK)에 SAVEPOINT 명을 부여하여 실행하면, 저장점 설정 이후에 있었던 데이터 변경에 대해서만 원래 데이터 상태로 되돌아가게 된다.
하지만, 특정 저장점까지 롤백하면 그 저장점 이후에 설정한 저장점은 모두 무효가 된다. 즉, A 로 되돌리고 나서 다시 B 와 같이 미래 방향으로 되돌릴 수 없다. 만약 저장점 지정 없이 “ROLLBACK”을 실행했을 경우 반영 안된 모든 변경 사항을 취소하고 트랜잭션 시작 위치로 되돌아간다.
5️⃣ SQL Delete와 트랜잭션
참고로 데이터베이스는 DDL 명령어와 DML 명령어를 처리하는 방식에 있어서 차이를 보인다.
1)DDL 명령어(CREATE, ALTER, RENAME, DROP)
DDL 명령어인 경우에는 직접 데이터베이스의 테이블에 영향을 미치기 때문에, DDL 명령어를 입력하는 순간 명령어에 해당하는 작업이 즉시 (AUTO COMMIT) 완료된다.
✨ ORACLE VS SQL SERVER
1)ORACLE : DDL 문장 수행 후 자동 커밋 + 트랜잭션 종료
2)SQL SERVER : DDL 문장 수행 후 자동 커밋 X
2)DML 명령어(INSERT, UPDATE, DELETE, SELECT)
DML명령어의 경우, 조작하려는 테이블을 메모리 버퍼에 올려놓고 작업을 하기 때문에 실시간으로 테이블에 영향을 미치지 않는다. 따라서, 버퍼에서 처리한 DML 명령어가 실제 테이블에 반영되기 위해서는 COMMIT 명령어를 입력하여 TRANSACTION 을 종료해야 한다.
👉그러나 SQL Server는 앞에서 말했듯이 DML 의 경우도 AUTO COMMIT 으로 처리되기 때문에 COMMIT 명령어를 입력하지 않아도 된다.
✅DELETE VS TRUNCATE
테이블의 전체 데이터를 삭제하는 경우, 시스템 활용 측면에서는 삭제된 데이터를 로그로 저장하는 DELETE TABLE 보다는 시스템 부하가 적은 TRUNCATE TABLE 을 사용하는 것이 좋다.
단, TRUNCATE TABLE 의 경우 삭제된 데이터의 로그가 없어서 DELETE와 달리 ROLLBACK 이 불가능하므로 주의해야 한다.
👉하지만 SQL Server의 경우에는 사용자가 임의적으로 트랜잭션을 시작한 후 TRUNCATE TABLE 을 이용하여 데이터를 삭제한 이후 오류가 발견되어,
다시 복구를 원할 경우 ROLLBACK 문을 이용하여 테이블 데이터를 원 상태로 되돌릴 수 있다.
참고자료.
SQL 전문가 가이드 책
