마야울과 함께해요 ::

'mssql'에 해당되는 글 2건

2009/05/10 트랜잭션의 수는 어떻게 될까? (2)
2009/05/10 트랜 잭션의 개념

트랜잭션의 수는 어떻게 될까?

| DATABASE/MS SQL 2005 2009/05/10 15:31

Posted by 마야울

테스트 테이블을 하나 만들었고, 그 테이블에 테스트로 값을 3개를 넣었다.

CREATE TABLE testTbl(id INT IDENTITY(1,1));

SELECT * FROM dbo.testTbl;

이제 트랜잭션의 수를 테스트를 해보자.

BEGIN TRAN
BEGIN TRAN
PRINT N'BEGIN 2개 안의 트랜잭션 수 : '+CONVERT(CHAR(1),@@trancount);
COMMIT TRAN
PRINT N'BEGIN 1개 안의 트랜잭션 수 : '+CONVERT(CHAR(1),@@trancount);
COMMIT TRAN
PRINT N'COMMIT 후의 트랜잭션 수 : '+CONVERT(CHAR(1),@@trancount);

그럼 이제 롤백을 테스트를 해보자.

CREATE TABLE #tranTest(id int);
INSERT INTO #tranTest VALUES(0);

BEGIN TRAN -- 1번째 트랜잭션
  UPDATE #tranTest SET id = 111;
  BEGIN TRAN -- 2번째 트랜잭션
    UPDATE #tranTest SET id = 222;
   SELECT * FROM #tranTest
  ROLLBACK TRAN -- 1번째 롤백
SELECT * FROM #tranTest
ROLLBACK TRAN -- 2번째 롤백
SELECT * FROM #tranTest

이 테스트의 결과값은,

에러가 난 이유는 ROLLBACK TRAN은 2번째 트랜잭션까지만 롤백이 될거 같지만, 실제적으로는 1번째 트랜잭션까지 모두 롤백이 되기 때문에, 2번째 롤백은 더 이상 롤백을 할 트랜잭션이 없기때문에 에러가 나는 것이다.

하지만 약간의 쿼리문을 수정을 하면, 원하는 결과가 나온다.

CREATE TABLE #tranTest(id int);
INSERT INTO #tranTest VALUES(0);

BEGIN TRAN -- 1번째 트랜잭션
UPDATE #tranTest SET id = 111;
  SAVE TRAN [tranPoint1]
    BEGIN TRAN -- 2번째 트랜잭션
    UPDATE #tranTest SET id = 222;
    SELECT * FROM #tranTest
ROLLBACK TRAN [tranPoint1] -- 1번째 롤백
SELECT * FROM #tranTest
ROLLBACK TRAN -- 2번째 롤백
SELECT * FROM #tranTest

@@trancount, mssql, transaction, 트랜잭션

Trackback 0 And Comment 2

트랜 잭션의 개념

| DATABASE/MS SQL 2005 2009/05/10 13:02

Posted by 마야울

트랜잭션의 개념을 정리하면 하나의 논리적 작업 단위로 수행되는 일련의 작업이다. 정의에는 전부 되거나, 전부 안 되거나의 의미가 포함되어 있다.
트랜잭션의 가장 큰 특징은 ACID라고 불린다.

원자성(Atomicity)
트랜잭션은 분리할 수 없는 하나의 단위로서, 작업이 모두 수행되거나 하나도 수행되지 않아야 한다.
일관성(Consistency)
트랜잭션에서 사용되는 모든 데이터는 일관되어야 한다. 이 일관성은 잠금과 관련이 있다.
격리성(Isolation)
현재 트랜잭션이 접근하고 있는 데이터는 다른 트랜잭션으로부터 격리되어야 한다. 트랜잭션이 발생되기 이전 상태나 완료된 이후의 상태를 볼 수는 있지만, 트랜잭션이 진행 중인 데이터를 볼수 없다.
영속성(Durabiliity)
트랜잭션이 정상적으로 종료된다면 그 결과는 시스템 오류가 발생하더라도 시스템에 영구적으로 적용된다.

그럼 이제 간단한 트랜잭션을 테스트를 해보자.

-- DB생성
CREATE DATABASE TestDB;

-- 테이블 생성
USE TestDB;
CREATE TABLE dbo.BankBook (
  name VARCHAR(10) ,
  money INT   ,
  CONSTRAINT CK_money CHECK ( money >= 0)
  -- money값이 0 이하로 내려가서는 안된다.
       )
GO

-- 테스트 값 INSERT
INSERT INTO dbo.BankBook VALUES( '마야울', 1000);
INSERT INTO dbo.BankBook VALUES( '비익조', 0);

우선 간단한 DB와 테이블을 생성 후 테스트값을 입력을 해보았다. 값이 제대로 들어갔는지 테스트를 해보면,

SELECT를 해보니 잘 나왔다.

간단히 UPDATE 문을 테스트를 해보면,

UPDATE dbo.BankBook SET money = money - 500 WHERE NAME = '마야울';
UPDATE dbo.BankBook SET money = money + 500 WHERE NAME = '비익조';
SELECT * FROM dbo.BankBook;

정상적으로 되었다.

그럼,

BEGIN TRAN -- 1번 트랜잭션
UPDATE dbo.BankBook SET money = money - 600 WHERE NAME = '마야울';
COMMIT TRAN

BEGIN TRAN -- 2번 트랜잭션
UPDATE dbo.BankBook SET money = money + 600 WHERE NAME = '비익조';
COMMIT TRAN

SELECT * FROM dbo.BankBook

이것을 실행하면 결과는 어떻게 나올까?? 간단히 생각해도 1번 트랜잭션에서 오류가 날것이다. 현재 잔고가 500인데 -600을 해주니, 테이블 생성시 제약조건에서 설정해준 것때문에 오류가 날것이다. 그러므로 1번 트랜잭션은 오류가 나지만, 2번 트랜잭션은 오류가 나지 않고 정상적으로 수행된다. 1번 트랜잭션과 2번 트랜잭션은 별개의 논리적인 작업이기 때문에 1번 트랜잭션이 오류가 나도 2번 트랜잭션을 롤백 시키지는 않는다.

그럼 여기서 간단히 ERROR를 수정하는 방법을 생각해보면,

BEGIN TRAN
UPDATE dbo.BankBook SET money = money - 600 WHERE NAME = '마야울';
UPDATE dbo.BankBook SET money = money + 600 WHERE NAME = '비익조';
COMMIT TRAN

SELECT * FROM dbo.BankBook

이런 방법이 생각 날 수도 있지만, 이 방법은 위와 똑같은 결과를 가져온다.
-- 제약조건의 논리적 오류는 록백이 되지 않는다. 첫번째 '마야울' 계좌에서 600원을 차감할때, 오류가 발생하여 실행이 되지 않은 것일 뿐, 롤백이 수행된 것은 아니다. 그럼 오류 발생시 강제로 롤백 시키는 방법은 무엇일까?

BEGIN TRY
BEGIN TRAN
UPDATE dbo.BankBook SET money = money - 600 WHERE NAME = '마야울';
UPDATE dbo.BankBook SET money = money + 600 WHERE NAME = '비익조';
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH

SELECT * FROM dbo.BankBook;

이렇게 하면 원하는 결과가 나온다.