SQL Server의 트랜잭션은 명령문이나 쿼리의 순차적 그룹 데이터베이스에서 단일 또는 여러 작업을 수행합니다. 각 트랜잭션에는 단일 읽기, 쓰기, 업데이트 또는 삭제 작업이 있거나 이러한 모든 작업이 조합되어 있을 수 있습니다. 각 트랜잭션은 SQL Server에서 두 가지 일이 발생해야 합니다.
- 트랜잭션이 커밋되면 모든 수정이 성공합니다.
- 또는 트랜잭션이 롤백되면 모든 수정 사항이 취소됩니다.
세트의 모든 작업이 완료될 때까지 트랜잭션은 성공할 수 없습니다. 이는 인수가 실패하면 트랜잭션 작업이 실패함을 의미합니다. 각 트랜잭션은 첫 번째 실행 가능한 SQL 문으로 시작하고 명시적 또는 암시적으로 커밋이나 롤백을 찾으면 끝납니다. 그것은 저지르다 또는 롤백 DDL 문이 사용될 때 암시적으로 뿐만 아니라 명시적으로도 명령문을 사용할 수 있습니다.
아래 그림 표현은 거래 프로세스를 설명합니다.
다음 예에서는 트랜잭션의 개념을 설명합니다.
이 예에서는 은행 데이터베이스 시스템을 사용하여 거래의 개념을 설명합니다. 은행 고객이 ATM 모드를 사용하여 계좌에서 돈을 인출하려고 한다고 가정해 보겠습니다. ATM은 다음 세 단계로 이 작업을 수행할 수 있습니다.
- 그만큼 첫 번째 단계 요청한 금액이 계좌에 있는지 확인하는 것입니다.
- 그만큼 두번째 단계 금액이 사용 가능한 경우 계정에서 해당 금액을 차감한 다음 계정 잔액을 업데이트합니다.
- 그만큼 세 번째 단계 돈 인출 작업을 로그 파일에 기록하는 것입니다. 이 단계에서는 트랜잭션이 성공했는지 실패했는지 기록합니다. 성공하면 데이터베이스에 데이터 수정 내용을 씁니다. 그렇지 않으면 트랜잭션이 이전 상태로 롤백됩니다.
트랜잭션의 기본 원칙은 문 중 하나가 오류를 반환하면 데이터 무결성을 보장하기 위해 전체 변경 사항 집합이 롤백된다는 것입니다. 그리고 트랜잭션이 성공하면 모든 변경 사항이 데이터베이스에 영구적으로 적용됩니다. 따라서 ATM에서 돈을 인출할 때 정전이나 기타 문제가 발생하는 경우 거래를 통해 잔고가 일정하게 유지됩니다. 트랜잭션의 네 가지 주요 속성이 모든 작업을 더 정확하고 일관되게 만들기 때문에 트랜잭션 문은 이러한 작업을 가장 잘 수행합니다. 트랜잭션의 네 가지 속성을 ACID라고 합니다.
거래 속성
트랜잭션 속성은 ACID(Atomicity, Consistency, Isolation, Durability) 속성이라고 하며 아래에서 자세히 설명합니다.
그렇지 않으면 자바에서
원자성: 이 속성은 트랜잭션에 포함된 모든 문이나 작업이 성공적으로 수행되도록 보장합니다. 그렇지 않으면 전체 트랜잭션이 중단되고 작업이 실패하면 모든 작업이 이전 상태로 롤백됩니다.
일관성: 이 속성을 사용하면 트랜잭션이 성공적으로 커밋될 때만 데이터베이스 상태가 변경됩니다. 또한 충돌로부터 데이터를 보호하는 역할도 담당합니다.
격리: 이 속성은 모든 트랜잭션이 다른 트랜잭션과 격리되도록 보장합니다. 즉, 트랜잭션의 각 작업이 독립적으로 작동함을 의미합니다. 또한 문이 서로 투명하도록 보장합니다.
내구성: 이 속성은 시스템이 충돌하거나 실패하더라도 커밋된 트랜잭션의 결과가 데이터베이스에 영구적으로 유지되도록 보장합니다.
SQL Server의 트랜잭션 모드
SQL Server에서 사용할 수 있는 트랜잭션 모드는 세 가지가 있습니다.
자동 커밋 트랜잭션 모드: 이는 SQL Server의 기본 트랜잭션 모드입니다. 각 SQL 문을 트랜잭션으로 평가하고 그에 따라 결과가 커밋되거나 롤백됩니다. 따라서 성공한 문은 즉시 커밋되고 실패한 문은 즉시 롤백됩니다.
암시적 트랜잭션 모드. 이 모드를 사용하면 SQL Server가 각 DML 문에 대해 암시적 트랜잭션을 시작할 수 있지만 문 끝에 커밋 또는 롤백 명령을 명시적으로 사용해야 합니다.
명시적 트랜잭션 모드: 이 모드는 거래의 시작점과 끝점을 정확하게 식별할 수 있도록 사용자가 정의합니다. 치명적인 오류가 발생하면 자동으로 중단됩니다.
거래 통제
다음은 트랜잭션을 제어하는 데 사용되는 명령입니다.
참고: 트랜잭션 제어 언어 명령에는 DML 문(INSERT, UPDATE 및 DELETE)만 사용할 수 있습니다. 이러한 작업은 데이터베이스에 자동으로 커밋되므로 테이블을 생성하거나 삭제하는 동안 사용할 수 없습니다.
거래 상태
이는 거래가 수명 동안 어떻게 진행되는지를 나타냅니다. 현재 거래 상태와 향후 거래 처리 방법을 설명합니다. 이러한 상태는 트랜잭션의 커밋 또는 중단 여부를 결정하는 규칙을 정의합니다.
SQL Server의 각 트랜잭션 상태를 설명하겠습니다.
활성 상태: 트랜잭션의 명령이 실행되는 동안 트랜잭션은 활성 상태입니다. 다음과 같이 변경됩니다. '부분적으로 커밋된 상태' 모든 '읽기 및 쓰기' 작업이 오류 없이 완료된 경우. 어떤 명령이라도 실패하면 '실패한 상태'로 변경됩니다.
부분적으로 커밋됨: 모든 읽기 및 쓰기 작업이 완료되면 주 메모리나 로컬 버퍼에 변경이 발생합니다. 국가가 갈 것이다 '커밋된 상태' 변경 사항이 데이터베이스에 영구적으로 적용되는 경우. 그렇지 않으면 '실패한 상태'가 됩니다.
실패 상태: 트랜잭션 명령이 실패하거나 데이터베이스에 대한 영구 수정이 실패하면 트랜잭션은 실패 상태로 전환됩니다.
중단된 상태: 거래가 다음에서 이동합니다. '실패한 상태' 에 '중단된 상태' 어떤 종류의 장애가 발생했을 때. 이러한 변경 사항은 이전 상태의 로컬 버퍼 또는 주 메모리에만 적용되므로 변경 사항이 제거되거나 롤백됩니다.
커밋된 상태: 변경 사항이 데이터베이스에 영구적으로 적용되고 종료되면 트랜잭션이 완료되고 이 상태가 됩니다. '종료된 상태'.
종료된 상태: 롤백이 없고 트랜잭션이 '커밋된 상태' 시스템은 일관되고 이전 트랜잭션이 종료되는 동안 새 트랜잭션을 위한 준비가 되어 있습니다.
SQL Server에서 트랜잭션 구현
SQL Server에서 트랜잭션을 구현하는 방법을 이해하기 위해 몇 가지 예를 들어 보겠습니다. 여기서는 '제품' 모든 트랜잭션 상태를 보여주는 표입니다.
다음 SQL 스크립트는 선택한 데이터베이스에 Product 테이블을 생성합니다.
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
그런 다음 아래 스크립트를 실행하여 이 테이블에 데이터를 삽입합니다.
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
SELECT 문을 실행하여 데이터를 확인합니다.
COMMIT 트랜잭션의 예
트랜잭션에 사용되는 SQL 문을 여러 논리적 부분으로 나누는 것이 좋습니다. 그런 다음 데이터를 커밋할지 롤백할지 결정할 수 있습니다. 다음 단계에서는 트랜잭션을 생성하는 방법을 보여줍니다.
- 다음을 사용하여 거래를 시작합니다. 거래 시작 명령.
- SQL 문을 작성하고 필요에 따라 나눕니다.
- 사용 저지르다 거래를 완료하고 변경 사항을 영구적으로 저장하는 명령문입니다.
다음은 SQL Server의 COMMIT 작업을 설명하는 명령입니다.
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
오류가 발견되지 않으면 트랜잭션의 각 SQL 문이 독립적으로 실행되는 다음 출력이 표시됩니다.
INSERT 및 UPDATE 문은 트랜잭션이 커밋된 후에 롤백될 수 없습니다. 커밋 작업 후 테이블을 확인하면 다음 데이터가 표시됩니다.
ROLLBACK 트랜잭션의 예
ROLLBACK 명령을 사용하여 아직 데이터베이스에 저장되지 않은 트랜잭션을 실행 취소하고 트랜잭션이 시작된 지점으로 돌아갑니다. 다음 예에서는 SQL Server의 ROLLBACK 작업을 설명합니다.
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
위의 트랜잭션을 실행하면 성공적으로 실행되는 것을 확인할 수 있습니다. 그러나 COMMIT 또는 ROLLBACK 문을 실행하지 않으면 변경 사항이 영구적으로 적용될 수 없으므로 데이터베이스의 변경 사항에는 영향을 미치지 않습니다. 따라서 ROLLBACK 트랜잭션 명령을 사용하여 모든 데이터베이스 작업을 롤백할 수 있는 옵션이 있습니다. 전체 거래 내역은 다음과 같습니다.
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
트랜잭션에서 @@Error 전역 변수 사용:
이 변수는 오류가 있는지 확인하는 데 사용됩니다. 아래 예에서는 해당 개념을 설명합니다. 여기서는 먼저 BEGIN 명령을 사용하여 트랜잭션을 시작한 다음 두 개의 삽입 문을 작성합니다. 다음으로 전역 시스템 변수를 사용하겠습니다. @@오류 에서 IF 문 오류를 확인하려면. 값이 0보다 크면 오류가 있음을 의미합니다. 이제 트랜잭션이 롤백됩니다. 그렇지 않으면 트랜잭션이 커밋됩니다.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
위의 트랜잭션이 실행되면 롤백되었음을 알 수 있습니다. 이는 기본 키 열에 중복된 값을 삽입하려고 했기 때문입니다.
자동 롤백 트랜잭션
대부분의 트랜잭션에는 둘 이상의 쿼리가 포함되어 있습니다. 트랜잭션을 실행하는 동안 SQL 문 중 하나라도 오류가 발생하면 데이터베이스가 수정되지 않으며 나머지 문은 실행되지 않습니다. 이 개념을 SQL Server의 자동 롤백 트랜잭션이라고 합니다. 간단한 예를 사용하여 이 프로세스를 보여드리겠습니다.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
이 트랜잭션은 다음과 같은 출력을 생성합니다.
이 출력에서 insert 문이 성공적으로 실행되었음을 확인할 수 있습니다. 그러나 업데이트 문을 실행하는 동안 데이터 유형 변환 문제로 인해 오류가 발견되었습니다. 이 경우 SQL Server는 데이터베이스 변경을 허용하지 않습니다. 이는 삽입 작업이 값을 추가하지 않으며 select 문이 실행되지 않음을 의미합니다.
트랜잭션의 저장점
저장점은 저장점 이후 실행된 모든 변경 사항을 롤백할 수 있는 특수 표시를 트랜잭션에 삽입합니다. 또한 전체 트랜잭션이 아닌 트랜잭션의 특정 부분을 롤백하는 데에도 사용됩니다. 우리는 이를 사용하여 정의할 수 있습니다. 거래 저장 sp_name 성명. 다음 예에서는 insert 문을 커밋하고 delete 문을 롤백하는 트랜잭션에서 저장점을 사용하는 방법을 설명합니다.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
아래 결과를 보면 제품 ID 116이 삭제되고 첫 번째 출력에 117이 삽입되는 것을 볼 수 있습니다. 그러나 두 번째 출력에서는 저장점으로 인해 삭제 작업이 롤백됩니다.
트랜잭션에서 저장점을 해제하는 방법은 무엇입니까?
저장점 해제는 저장점 이후에 실행된 쿼리 결과를 되돌리지 않고 현재 트랜잭션에서 명명된 저장점을 제거하는 데 사용됩니다. MySQL에는 이 명령이 있지만 SQL Server에서는 저장점을 해제하는 명령을 제공하지 않습니다. 대신 커밋이나 롤백 트랜잭션이 끝나면 자동으로 해제되므로 중간에 걱정할 필요가 없습니다.
SQL Server의 암시적 트랜잭션
IMPLICIT_TRANSACTIONS 옵션을 활성화하여 암시적 트랜잭션을 정의할 수 있습니다. 다음 예에서는 이 개념을 쉽게 설명합니다.
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
이 거래에서 우리는 두 가지 옵션을 사용했습니다. @@OPTION 및 @@TRANCOUNT. @@OPTOPN은 현재 SET 옵션에 대한 정보를 제공하고, @@TRANCOUNT는 현재 세션에서 BEGIN TRANSACTION 문을 제공합니다.
이제 트랜잭션을 실행하면 아래 출력이 반환됩니다.
SQL Server의 명시적 트랜잭션
명시적 트랜잭션은 명시적 트랜잭션의 시작점을 식별하므로 BEGIN TRANSACTION 명령을 통해 정의해야 합니다. 아래와 같이 SQL Server에서 명시적 트랜잭션을 정의할 수 있습니다.
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
구문에서 trans_name 옵션은 트랜잭션의 고유한 이름을 나타냅니다. 그만큼 @trans_name_var 트랜잭션 이름을 저장하는 사용자 정의 변수를 나타냅니다. 마지막으로, 표시 옵션을 사용하면 로그 파일에 특정 트랜잭션을 표시할 수 있습니다.
BEGIN TRANSACTION 명령을 통한 명시적 트랜잭션은 트랜잭션 관련 리소스의 격리 수준에 따라 잠금을 획득했습니다. 잠금 문제를 줄이는 데 도움이 됩니다. 아래 예를 참조하세요.
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
출력은 다음과 같습니다.
SQL Server에 표시된 트랜잭션
표시된 트랜잭션은 로그 파일의 특정 트랜잭션에 설명을 추가하는 데 사용됩니다. 데이터베이스를 이전 상태로 복원할 때 날짜와 시간 대신 복구 지점으로 사용할 수 있습니다. 표시된 트랜잭션이 데이터베이스를 수정하는 경우에만 로그 파일에 표시가 추가된다는 점을 알아야 합니다. 다음 예를 통해 그 개념을 이해할 수 있습니다.
실수로 데이터베이스를 수정했는데 데이터가 변경된 정확한 순간을 모른다고 가정해 보겠습니다. 이 경우 데이터 복구에 오랜 시간이 걸릴 수 있습니다. 그러나 표시된 트랜잭션을 사용하면 데이터 변경의 정확한 시점을 결정하는 데 유용한 도구가 될 수 있습니다.
다음 구문은 SQL Server에 표시된 트랜잭션을 보여줍니다.
BEGIN TRANSACTION trans_name WITH MARK 'description';
여기서는 거래 이름을 정의한 다음 WITH MARK 옵션을 추가해야 합니다. 아래 예에서는 레코드를 삭제하고 로그 파일에 표시를 추가합니다.
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
그만큼 로그 기록 테이블이 포함되어 있습니다. msdb 데이터베이스 커밋된 표시된 각 트랜잭션에 관한 정보를 저장합니다. 아래 문을 실행하여 logmarkhistory 테이블에서 세부정보를 가져옵니다.
SELECT * FROM msdb.dbo.logmarkhistory
SQL Server의 명명된 트랜잭션
SQL Server에서 트랜잭션의 이름을 제공할 수도 있습니다. 단일 쿼리에서 많은 트랜잭션을 작업할 때는 항상 명명된 트랜잭션을 사용하는 것이 좋습니다. 아래 예에서는 거래 이름을 바꾸는 방법을 설명합니다.
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
출력은 다음과 같습니다.
결론
이 문서에서는 SQL Server 문의 트랜잭션에 대한 전체 개요를 제공합니다. 트랜잭션은 데이터베이스 무결성을 보장하므로 관계형 데이터베이스 시스템에 유용합니다.