공부하는 습관

SQLD 시험 전에 공부해야 할 것4 : TRANSACTION, COMMIT, ROLLBACK

 안녕하세요 오늘은 아주아주 중요한 TRANSACTION, COMMIT, ROLLBACK에 대해서 알아보려고 합니다!

ORACLE 과  SQL Server에서 각각 조금씩 차이점이 있는데요.

우선 SQL Server부터 살펴보겠습니다.

SQL Server에서의 TRANSACTION, COMMIT, ROLLBACK

SQL(Structured Query Language)에서 트랜잭션은 단일 작업 단위로 처리되는 일련의 SQL 문입니다. 

SQL Server에서 트랜잭션 작업을 위한 BEGIN TRANSACTION, COMMIT 및 Rollback의 세 가지 주요 명령을 제공합니다.

BEGIN TRANSACTION

이 명령은 트랜잭션의 시작을 표시합니다. BEGIN TRANSACTION 명령이 실행되면 COMMIT 또는 ROLBACK 명령이 실행될 때까지 동일한 세션에서 실행되는 후속 SQL 문은 동일한 트랜잭션의 일부가 됩니다.

COMMIT

이 명령은 트랜잭션의 종료를 표시하고 트랜잭션 중에 수행된 모든 변경 사항을 영구적으로 적용합니다. COMMIT 명령이 실행되면 트랜잭션의 변경 내용이 다른 사용자에게 표시됩니다.

ROLLBACK

이 명령은 트랜잭션 중에 변경된 내용을 모두 실행 취소하고 데이터베이스를 트랜잭션이 시작되기 전의 상태로 되돌립니다. 트랜잭션에 오류가 발생하거나 어떤 이유로 완료할 수 없는 경우 ROLBACK 명령을 사용하여 이미 수행된 변경 내용을 실행 취소하고 추가 변경 내용이 데이터베이스에 커밋되지 않도록 할 수 있습니다.

예시를 한 번 살펴볼까요!

BEGIN TRANSACTION
UPDATE Customers SET Phone = '123-456-7890' WHERE Name = 'John Smith'
COMMIT

이 예시에서 BEGIN TRANSACTION 명령은 트랜잭션의 시작을 표시하고 UPDATE 명령은 고객 "John Smith"의 전화 번호를 수정합니다. 트랜잭션이 성공적으로 완료되면 COMMIT 명령이 실행될 때 UPDATE 명령에 의해 변경된 내용이 데이터베이스에 영구적으로 적용됩니다.

그러나 트랜잭션을 실행하는 동안 오류가 발생하면 UPDATE 명령이 실행되기 전의 초기 상태로 트랜잭션을 롤백할 수 있습니다. 이렇게 하면 오류 또는 오류 발생 시에도 데이터베이스가 일관되고 정확하게 유지됩니다.

Oracle 및 SQL Server는 모두 트랜잭션, 커밋 및 롤백 작업을 지원하는 관계형 데이터베이스 관리 시스템이지만 각 시스템에서 이러한 작업이 구현되는 방식에는 약간의 차이가 있습니다.

ORACLE에서의 TRANSACTION, COMMIT, ROLLBACK

Oracle에서 트랜잭션은 하나 이상의 SQL 문으로 구성된 작업의 논리 단위입니다. 이러한 문은 단일 단위로 처리되며 트랜잭션은 모든 문이 함께 성공하거나 실패하도록 보장하는 데 사용되어 데이터의 무결성을 유지합니다. ACID(원자성, 일관성, 격리, 내구성) 속성을 따릅니다.

원자성(Automicity)

트랜잭션의 일부라도 실패하면 전체 트랜잭션이 롤백되고 데이터베이스가 이전 상태로 복원됩니다.

일관성(Consistency)

트랜잭션이 실행 되기 전의 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션이 실행된 이후에도 데이터베이스의 내용에 오류가 있으면 안됩니다.

고립성(Isolation)

트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어내서는 안된다

지속성(Durability)

트랜잭션이 커밋되면 트랜잭션에 의해 변경된 내용이 영구적이며 손실되지 않도록 보장합니다.

COMMIT

오라클에서 트랜잭션을 커밋하려면 COMMIT 문을 사용합니다. COMMIT 문은 트랜잭션의 모든 변경 내용을 영구적으로 변경하고 트랜잭션이 보유한 잠금을 해제합니다. 트랜잭션이 커밋되면 변경 내용이 다른 세션에 표시됩니다.

ROLLBACK

Oracle에서 트랜잭션을 롤백하려면 ROLBACK 문을 사용합니다. ROLBACK 문은 트랜잭션의 모든 변경 내용을 실행 취소하고 데이터베이스를 이전 상태로 복원합니다.

BEGIN
  UPDATE Customers SET status = 'Active' WHERE id = 1;
  UPDATE Orders SET status = 'Shipped' WHERE customer_id = 1;
  COMMIT;
END;

SAVEPOINT

Oracle에서는 SAVEPOINT 문을 사용하여 트랜잭션 내에서 저장 지점을 정의할 수도 있습니다. 저장 지점을 사용하면 전체 트랜잭션을 롤백하지 않고 트랜잭션의 일부를 롤백할 수 있습니다. 또한 SET TRANSACTION 문을 사용하여 분리 수준, 일관성 수준 및 트랜잭션 시간 초과와 같은 트랜잭션 특성을 설정할 수 있습니다.

SAVEPOINT 생성하는 구문

SAVEPOINT savepoint_name;

SAVEPOINT를 활용한 예시입니다

거래에서 한 은행 계좌에서 다른 은행 계좌로 돈을 이체한다고 가정합니다. 원본 계정에서 돈을 차감하기 전에 저장 지점을 생성하여 대상 계정으로 돈을 입금할 때 오류가 발생하면 트랜잭션을 저장 지점으로 롤백하고 원본 계정에서 돈을 차감하지 않도록 하려고 합니다.

BEGIN
  SAVEPOINT before_transfer; -- SAVEPOINT 생성
  UPDATE accounts SET balance = balance - 500 WHERE account_number = '123456';
  UPDATE accounts SET balance = balance + 500 WHERE account_number = '789012'; 
EXCEPTION
  WHEN OTHERS THEN 
    ROLLBACK TO before_transfer; -- SAVEPOINT로 ROLLBACK
END;

이 예에서는 SAVEPOINT 문을 사용하여 before_transfer라는 이름의 저장 지점을 만듭니다. 그런 다음 우리는 소스 계좌에서 500 단위의 통화를 공제하고 동일한 금액을 목적지 계좌로 입금합니다. 트랜잭션 도중 오류가 발생하면 EXPECTION 블록이 실행되고 ROLLBACK TO 문을 사용하여 트랜잭션을 before_transfer 저장 지점으로 롤백합니다. 이렇게 하면 목적지 계좌로 입금하는 과정에서 오류가 발생하더라도 원본 계좌에서 돈이 차감되지 않습니다.

만약 두 SAVEPOINTS의 이름이 같다면 어떻게 될까요?

SAVEPOINT my_savepoint;
-- make some changes here
SAVEPOINT my_savepoint;
-- make some more changes here
ROLLBACK TO my_savepoint;

여기 이름이 my_savepoint로 같은 savepoint 2개가 있는데요

결론은 Rollback을 했을 때 가장 나중에 생긴 savepoint로 이동이 됩니다.

이 코드에서는 "my_savepoint"라는 이름의 저장 지점을 설정하고, 몇 가지를 변경하고, "my_savepoint"라는 이름의 다른 저장 지점을 설정하고, 몇 가지를 더 변경합니다. "my_savepoint"로 롤백하면 두 번째 저장 지점 이후의 변경 내용만 실행 취소되고 이전에 변경된 내용은 실행 취소되지 않습니다.

따라서 혼동을 방지하고 트랜잭션의 올바른 지점으로 롤백할 수 있도록 트랜잭션에서 고유한 저장 지점 이름을 사용하는 것이 중요합니다.

자동커밋?

자동커밋 또는 암묵적 커밋이라고도 하는데요. 말 그대로 commit을 작성하지 않아도 알아서 자동으로  commit이 되는 경우가 있습니다. 이와 관련해 SQL Server와 ORACLE 각각 다른 특징을 보이는데요.

SQL Server에서는 기본적으로 각 문이 자체 트랜잭션 내에서 실행되며, 문이 성공적으로 완료되면 자동으로 커밋됩니다. 이를 자동 커밋 모드라고 합니다. 

Oracle에서는 자동 커밋 모드가 기본적으로 사용되지 않습니다. 각 문은 트랜잭션의 컨텍스트 내에서 실행되며 변경 내용을 영구적으로 변경하려면 COMMIT 문을 사용하여 명시적으로 커밋되어야 합니다. 

Oracle에서 DDL 구문이 사용시 자동 COMMIT이 수행되기도 하는데요

DDL(데이터 정의 언어)은 테이블, 인덱스, 보기 및 시퀀스와 같은 데이터베이스 개체를 정의, 수정 또는 삭제하는 데 사용되는 SQL 문 집합을 말합니다. DDL 문에는 CREATE, ALTER ,DROP,RENAME 문이 포함됩니다.

Oracle에서 DDL 문을 실행할 때 데이터베이스는 암묵적 트랜잭션 모드로 작동합니다. 즉, 각 DDL 문이 실행된 후 자동으로 커밋됩니다. 

자동 커밋 모드는 대부분의 데이터베이스 작업에 권장되지 않습니다. 자동 커밋 모드는 예기치 않은 데이터 변경을 초래하고 오류가 발생할 경우 트랜잭션을 롤백하기 어려울 수 있기 때문입니다. 따라서 Oracle 데이터베이스로 작업할 때는 COMMIT 및 ROLBOLLBACK 문을 사용하여 트랜잭션 동작을 명시적으로 제어하는 것이 가장 이상적입니다.

그럼 오늘도 화이팅!

SQLD 시험 전 공부해야 할 것2 : DROP, TRUNCATE, DELETE 차이점

시험에 자주 나오는 단골 질문 중 하나인 DROP, TRUNCATE, DELETE의 차이점 및 기능에 대해 알아보도록 하겠습니다!

SQL에서 DROP, TRUNCATE 및 DELETE 문의 주요 차이점은 다음과 같습니다

기능

DROP  예시

DROP TABLE employees; -- 인덱스, 트리거 및 제약 조건을 포함하여 전체 "인덱스" 테이블과 관련된 모든 개체를 삭제합니다.

TRUNCATE 예시

TRUNCATE TABLE orders; -- 테이블 구조와 관련 개체(예: 인덱스 및 트리거)를 유지하면서 "주문" 테이블에서 모든 행을 제거합니다.

DELETE 예시

DELETE FROM customers WHERE city = 'London';-- 도시가 런던인 "고객" 테이블에서 모든 행을 제거합니다.

속도

복구여부

여기서 COMMIT과 ROLLBACK이란

SQL에서 COMMIT 및 ROLBOLLBACK은 트랜잭션을 관리하는 데 사용됩니다. 트랜잭션은 단일 단위로 실행되는 하나 이상의 SQL 문으로 구성된 논리적 작업 단위입니다. 커밋 및 롤백을 사용하면 트랜잭션 내에서 수행된 데이터 변경의 내구성과 일관성을 제어할 수 있습니다.

COMMIT: 트랜잭션의 변경 내용을 데이터베이스에 저장하는 데 사용됩니다. COMMIT 문이 실행되면 변경 내용이 영구적으로 변경되어 실행 취소할 수 없습니다. COMMIT 문은 현재 트랜잭션을 종료하고 트랜잭션 내의 모든 변경 내용을 다른 트랜잭션에 표시합니다.

ROLBACK: 트랜잭션의 변경 내용을 실행 취소하고 데이터베이스를 이전 상태로 복원하는 데 사용됩니다. ROLBACK 문이 실행되면 트랜잭션 내의 모든 변경 내용이 삭제되고 데이터베이스가 이전 상태로 복원됩니다. ROLBACK 문은 현재 트랜잭션을 종료하고 데이터베이스를 트랜잭션이 시작되기 전의 이전 상태로 되돌립니다.

권한

요약

DROP는 전체 테이블 또는 데이터베이스를 영구적으로 삭제하는 데 사용되고, TRUNCATE는 테이블 구조를 유지하면서 테이블에서 모든 행을 신속하게 제거하는 데 사용되며, DELETE는 특정 조건에 따라 테이블에서 특정 행을 선택적으로 제거하는 데 사용됩니다. 사용할 명령문의 선택은 속도, 데이터 보존 또는 작업 실행 취소 기능과 같은 당면한 작업의 특정 요구 사항에 따라 달라집니다.