|
MS-SQL > Transaction & Lock - 트랜잭션 처리
안냐세여. 오늘은 실제 예문을 보면서 하나하나 같이 보도록 하겠습니다.
9.2.1. @@trancount
먼저 다른 처리에 앞서서 @@trancount 라는 글로벌 변수에 대해서 알아야 할것 같네여.. ^^
이변수는 벌써 저장프로시져의 기본문법(변수선언) 에서 보구 넘어 왔던 내용이네요.
@@trancount
트랜잭션의 중첩된 상태를 가지고 있는 변수입니다.
트랜잭션 밖에 있으면 0, 트랜젝션이 진행중이면,,, 1 이란 값을 가지고 있답니다.
9.2.2. 트랜잭션의 시작과 끝.
그럼 예제를 하나 해 보져.??
위의 예제를 보면서 하나 하나 보도록 하겠습니다.
일단 위에서 보면 create table tran1(id int,trancnt int) tran1 테이블을 만들었습니다.
SELECT @@TRANCOUNT 명령을 통해서 @@TRANCOUNT 값을 뽑아봅니다... 값은 0이 출력됩니다.
직접해보세요.
확실한가여???
그런후
INSERT TRAN1 VALUES(1,@@TRANCOUNT) 를 통해 값을 입력했습니다.
테이블에 값은 모가 들어 갈까여? '0'값이 들어갈까여???? 아닙니다.
'1'값이 들어갑니다.
SELECT * FROM TRAN1를 통해 봑인해 보시면 알겠지만, 확실하시져?????
여러분이 보셔두 아시겠지만 쿼리 각각의 단계에서 확인해 본결과 0값을 가지고 있습니다.
하지만 TRAN1 테이블에 저장된 1의 값을 보면 모든 하나의 트랜잭션내에서 INSERT 되었음을
알수있습니다.
즉, SQL 서버에서는 기본적으로 데이터를 수정하는 모든 쿼리는 자체로 하나의 트랜잭션 단위가
되며 위의 예제와 같은 쿼리는 내부적으로 아래와 같은 형식으로 처리되어 집니다.
위의 예제를 보면서 하나 하나 보도록 하겠습니다.
일단 위에서 보면 create table tran1(id int,trancnt int) tran1 테이블을 만들었습니다.
SELECT @@TRANCOUNT 명령을 통해서 @@TRANCOUNT 값을 뽑아봅니다... 값은 0이 출력됩니다.
직접해보세요.
확실한가여???
그런후
INSERT TRAN1 VALUES(1,@@TRANCOUNT) 를 통해 값을 입력했습니다.
테이블에 값은 모가 들어 갈까여? '0'값이 들어갈까여???? 아닙니다.
'1'값이 들어갑니다.
SELECT * FROM TRAN1를 통해 봑인해 보시면 알겠지만, 확실하시져?????
여러분이 보셔두 아시겠지만 쿼리 각각의 단계에서 확인해 본결과 0값을 가지고 있습니다.
하지만 TRAN1 테이블에 저장된 1의 값을 보면 모든 하나의 트랜잭션내에서 INSERT 되었음을
알수있습니다.
즉, SQL 서버에서는 기본적으로 데이터를 수정하는 모든 쿼리는 자체로 하나의 트랜잭션 단위가
되며 위의 예제와 같은 쿼리는 내부적으로 아래와 같은 형식으로 처리되어 집니다.
왜 그림의 예제를 실행했을때 결과가 '0'이 아닌 '1'이 나왔는지 아시겠나여???
그럼, 이제 사용자가 직접 시작과 끝을 지정하고 싶다면 어케 해야 할까요.
즉, 명시적인 트랜잭션을 말하는 겁니다.
이제 명시적인 트랜잭션을 간단히 알아보도록하겠습니다.
9.2.3. 명시적인 트랜잭션
앞의 예제를 통해서 SQL서버에서 데이터에 변경을 가하는 작업은 모두 가각 하나의 트랜잭션으로
처리됨을 확인할수 있었습니다.
하지만 데이터의 일관성을 위해서는 업무 규칙상 서로 관련있는 작업은 모두 묶어서 하나의 트랜잭션
으로 처리해줘야 합니다.
자, 또하나의 예제를 보도록 하겠습니다.
여러분이 계좌 이체를 시킵니다.
최안규가 안규앤한테 용돈을 보내줍니다.
이케 두개의 작업을 해야겠지여?
이케 되야겠지여???
이게 바로 명시적인 트랜잭션 이랍니다. 이해 하시져??
근데여 위와 같은 경우 만약 1번 작업을 할때 에러가 없었어여. 근데 2번작업을 할때 에러가 생겼다면
데이타는 어케 될까여??? 제 통장에는 돈이 빠져 나갔지만 우리앤 통장에 입금이 될까여???
아~~ 이론...
이런 경우가 생기면 전 울앤한테 맞아 듁습니다... -.-
저야 확인할 길이 없겠지여??? 분명히 빠져 나갔으니까,,,, 그럼 전 어케 해야 하나여? 영수증가지고
은행가서 무쟈게 따지면 되지여... 캬캬캬,,,
저 성격 더럽습니다.....히히~~~
위의 경우도 약간의 문제점이 있습니다.
그렇지여?? 이런경우때문에 명시적인 트랜잭션은 반드시 필요한 겁니다.
그럼 어케 하면 될까여?
요렇게 합치면 된답니다... 하하하~~~~~
알겠지여?????
자 그럼 더 상세하게 이번에 롤백까지 곁드린 내용을 볼까여???
어떻습니까???
@@ERROR 변수는 저장프로시져에서 이미 보고 넘어온 내용이기 때문에 궁금하신분은,,, 다시 확인해 보시고
오세요.
위의 예제는 처음 UPDATE 하고 나서 에러가 있으면 롤백처리를 하고, 그렇지 않으면 우리앤 통장을 업데이트
시켜주지요. 이것도 또한 에러 없다면 모두 COMMIT 하지만 그렇지 않을때 ROLLBACK 시키면 되겠지요?
어떤가여??? 이해가 가시져??? 그리 어렵지 않은 내용들이니, 금방들 하실수 있을겁니다.
9.2.4. 내부적 트랜잭션(IMPLICIT)
지금까지는 트랜잭션을 처리하기 위해서 개발자가 명시적으로 BEGIN TRAN 문을 사용해서 트랜잭션이
시작되었음을 알리는 방법을 설명했습니다.
SET IMPLICIT_TRANSACTIONS 옵션 설정은 커넥션 레벨 설정으로 해당 커넥션에서 데이터를 수정하는 쿼리를
실행하면 자동으로 하나의 트랜잭션이 시작된 후 개발자가 명시적으로 COMMIT 또는 ROLLBACK 처리를 해줘야만
트랜잭션이 완료되도록 하는 것입니다.
이것은 기본 상태에서 데이터를 수정하는 쿼리를 실행하면 데이터가 수정된 후 자동으로 트랜잭션완료되어
데이터를 복구 할수 없는것과 비교되는 개념으로 실수로 데이터를 지웠을 때에도 ROLLBACK 처리가 가능하게
해 줍니다.
IMPLICIT_TRANSACTION 옵션이 설정되어 있을때는 다음과 같은 문장을 사용할때 내부적으로 트랜잭션이 자동으로
시작되며 트랜잭션을 종료하기 위해서는 명시적으로 COMMIT 이나 ROLLBACK 문을 사용해야 합니다.
위의 문장을 실행해 보시기 바랍니다.
위의 문장을 실행하면, INSERT TABEL_TBL VALUES(1) 문이 실행되면서 자동으로 트랜잭션이 시작됩니다.
그러나, 자동완료가 되지 않기 때문에 여러분이 ROLLBACK 이나 COMMIT 문을 이용해서 되돌리거나, 완료를 시킬수가
있습니다. 이러한 기능을 해 주는게 바로 SET IMPLICIT_TRANSACTIONS 문입니다.
이겨에서 DBCC OPENTRAN 명령은 현재의 트랜잭션의 상태를 보여줍니다.
어떤가여??? 헤헤 도움이 되셨으면 좋겠는뎅.. 너무 쉬운 내용들이였습니다.
그럼,
9.2.1. @@trancount
먼저 다른 처리에 앞서서 @@trancount 라는 글로벌 변수에 대해서 알아야 할것 같네여.. ^^
이변수는 벌써 저장프로시져의 기본문법(변수선언) 에서 보구 넘어 왔던 내용이네요.
@@trancount
트랜잭션의 중첩된 상태를 가지고 있는 변수입니다.
트랜잭션 밖에 있으면 0, 트랜젝션이 진행중이면,,, 1 이란 값을 가지고 있답니다.
9.2.2. 트랜잭션의 시작과 끝.
그럼 예제를 하나 해 보져.??
위의 예제를 보면서 하나 하나 보도록 하겠습니다.
일단 위에서 보면 create table tran1(id int,trancnt int) tran1 테이블을 만들었습니다.
SELECT @@TRANCOUNT 명령을 통해서 @@TRANCOUNT 값을 뽑아봅니다... 값은 0이 출력됩니다.
직접해보세요.
확실한가여???
그런후
INSERT TRAN1 VALUES(1,@@TRANCOUNT) 를 통해 값을 입력했습니다.
테이블에 값은 모가 들어 갈까여? '0'값이 들어갈까여???? 아닙니다.
'1'값이 들어갑니다.
SELECT * FROM TRAN1를 통해 봑인해 보시면 알겠지만, 확실하시져?????
여러분이 보셔두 아시겠지만 쿼리 각각의 단계에서 확인해 본결과 0값을 가지고 있습니다.
하지만 TRAN1 테이블에 저장된 1의 값을 보면 모든 하나의 트랜잭션내에서 INSERT 되었음을
알수있습니다.
즉, SQL 서버에서는 기본적으로 데이터를 수정하는 모든 쿼리는 자체로 하나의 트랜잭션 단위가
되며 위의 예제와 같은 쿼리는 내부적으로 아래와 같은 형식으로 처리되어 집니다.
위의 예제를 보면서 하나 하나 보도록 하겠습니다.
일단 위에서 보면 create table tran1(id int,trancnt int) tran1 테이블을 만들었습니다.
SELECT @@TRANCOUNT 명령을 통해서 @@TRANCOUNT 값을 뽑아봅니다... 값은 0이 출력됩니다.
직접해보세요.
확실한가여???
그런후
INSERT TRAN1 VALUES(1,@@TRANCOUNT) 를 통해 값을 입력했습니다.
테이블에 값은 모가 들어 갈까여? '0'값이 들어갈까여???? 아닙니다.
'1'값이 들어갑니다.
SELECT * FROM TRAN1를 통해 봑인해 보시면 알겠지만, 확실하시져?????
여러분이 보셔두 아시겠지만 쿼리 각각의 단계에서 확인해 본결과 0값을 가지고 있습니다.
하지만 TRAN1 테이블에 저장된 1의 값을 보면 모든 하나의 트랜잭션내에서 INSERT 되었음을
알수있습니다.
즉, SQL 서버에서는 기본적으로 데이터를 수정하는 모든 쿼리는 자체로 하나의 트랜잭션 단위가
되며 위의 예제와 같은 쿼리는 내부적으로 아래와 같은 형식으로 처리되어 집니다.
.. 트랜잭션 시작 INSERT TRAN1 트랜잭션 종료 .. 트랜잭션 시작 INSERT TRAN1 트랜잭션 종료 |
왜 그림의 예제를 실행했을때 결과가 '0'이 아닌 '1'이 나왔는지 아시겠나여???
그럼, 이제 사용자가 직접 시작과 끝을 지정하고 싶다면 어케 해야 할까요.
즉, 명시적인 트랜잭션을 말하는 겁니다.
이제 명시적인 트랜잭션을 간단히 알아보도록하겠습니다.
9.2.3. 명시적인 트랜잭션
앞의 예제를 통해서 SQL서버에서 데이터에 변경을 가하는 작업은 모두 가각 하나의 트랜잭션으로
처리됨을 확인할수 있었습니다.
하지만 데이터의 일관성을 위해서는 업무 규칙상 서로 관련있는 작업은 모두 묶어서 하나의 트랜잭션
으로 처리해줘야 합니다.
■ BEGIN TRANSACTION : 명시적 트랜잭션의 시작. ■ COMMIT TRANSACTION : 명시적 트랜잭션의 완료. ■ ROLLBACK TRAN : 명시적 트랜잭션의 취소 |
자, 또하나의 예제를 보도록 하겠습니다.
여러분이 계좌 이체를 시킵니다.
최안규가 안규앤한테 용돈을 보내줍니다.
1. UPDATE 안규_계좌번호_테이블 SET 금액=금액-50000 WHERE 계좌번호_필드='안규_계좌번호'
2. UPDATE 안규앤_계좌번호_테이블 SET 금액=금액+50000 WHERE 계좌번호_필드='안규앤_계좌번호'
이케 두개의 작업을 해야겠지여?
BEGIN TRAN
1. UPDATE 안규_계좌번호_테이블 SET 금액=금액-50000 WHERE 계좌번호_필드='안규_계좌번호'
COMMIT TRAN
BEGIN TRAN
2. UPDATE 안규앤_계좌번호_테이블 SET 금액=금액+50000 WHERE 계좌번호_필드='안규앤_계좌번호'
COMMIT TRAN
이케 되야겠지여???
이게 바로 명시적인 트랜잭션 이랍니다. 이해 하시져??
근데여 위와 같은 경우 만약 1번 작업을 할때 에러가 없었어여. 근데 2번작업을 할때 에러가 생겼다면
데이타는 어케 될까여??? 제 통장에는 돈이 빠져 나갔지만 우리앤 통장에 입금이 될까여???
아~~ 이론...
이런 경우가 생기면 전 울앤한테 맞아 듁습니다... -.-
저야 확인할 길이 없겠지여??? 분명히 빠져 나갔으니까,,,, 그럼 전 어케 해야 하나여? 영수증가지고
은행가서 무쟈게 따지면 되지여... 캬캬캬,,,
저 성격 더럽습니다.....히히~~~
위의 경우도 약간의 문제점이 있습니다.
그렇지여?? 이런경우때문에 명시적인 트랜잭션은 반드시 필요한 겁니다.
그럼 어케 하면 될까여?
BEGIN TRAN
1. UPDATE 안규_계좌번호_테이블 SET 금액=금액-50000 WHERE 계좌번호_필드='안규_계좌번호'
2. UPDATE 안규앤_계좌번호_테이블 SET 금액=금액+50000 WHERE 계좌번호_필드='안규앤_계좌번호'
COMMIT TRAN
요렇게 합치면 된답니다... 하하하~~~~~
알겠지여?????
자 그럼 더 상세하게 이번에 롤백까지 곁드린 내용을 볼까여???
BEGIN TRAN
UPDATE 안규_계좌번호_테이블 SET 금액=금액-50000 WHERE 계좌번호_필드='안규_계좌번호'
IF( @@error<>0)
ROLLBACK TRAN
ELSE
BEGIN
UPDATE 안규앤_계좌번호_테이블 SET 금액=금액+50000 WHERE 계좌번호_필드='안규앤_계좌번호'
IF(@@error<>0)
ROLLBACK TRAN
ELSE
COMMIT TRAN
END
어떻습니까???
@@ERROR 변수는 저장프로시져에서 이미 보고 넘어온 내용이기 때문에 궁금하신분은,,, 다시 확인해 보시고
오세요.
위의 예제는 처음 UPDATE 하고 나서 에러가 있으면 롤백처리를 하고, 그렇지 않으면 우리앤 통장을 업데이트
시켜주지요. 이것도 또한 에러 없다면 모두 COMMIT 하지만 그렇지 않을때 ROLLBACK 시키면 되겠지요?
어떤가여??? 이해가 가시져??? 그리 어렵지 않은 내용들이니, 금방들 하실수 있을겁니다.
9.2.4. 내부적 트랜잭션(IMPLICIT)
지금까지는 트랜잭션을 처리하기 위해서 개발자가 명시적으로 BEGIN TRAN 문을 사용해서 트랜잭션이
시작되었음을 알리는 방법을 설명했습니다.
SET IMPLICIT_TRANSACTIONS 옵션 설정은 커넥션 레벨 설정으로 해당 커넥션에서 데이터를 수정하는 쿼리를
실행하면 자동으로 하나의 트랜잭션이 시작된 후 개발자가 명시적으로 COMMIT 또는 ROLLBACK 처리를 해줘야만
트랜잭션이 완료되도록 하는 것입니다.
이것은 기본 상태에서 데이터를 수정하는 쿼리를 실행하면 데이터가 수정된 후 자동으로 트랜잭션완료되어
데이터를 복구 할수 없는것과 비교되는 개념으로 실수로 데이터를 지웠을 때에도 ROLLBACK 처리가 가능하게
해 줍니다.
IMPLICIT_TRANSACTION 옵션이 설정되어 있을때는 다음과 같은 문장을 사용할때 내부적으로 트랜잭션이 자동으로
시작되며 트랜잭션을 종료하기 위해서는 명시적으로 COMMIT 이나 ROLLBACK 문을 사용해야 합니다.
| ALTER TABLE , CREATE, DELETE,DROP,FETCH,GRANT,INSERT,OPEN,REVOKE,SELECT,TRUNCATE TABLE,UPDATE |
CREATE TABLE test_tbl(id int)
SET IMPLICIT_TRANSACTIONS ON
INSERT TABEL_TBL VALUES(1)
SELECT @@TRANCOUNT
DBCC OPENTRAN
위의 문장을 실행해 보시기 바랍니다.
위의 문장을 실행하면, INSERT TABEL_TBL VALUES(1) 문이 실행되면서 자동으로 트랜잭션이 시작됩니다.
그러나, 자동완료가 되지 않기 때문에 여러분이 ROLLBACK 이나 COMMIT 문을 이용해서 되돌리거나, 완료를 시킬수가
있습니다. 이러한 기능을 해 주는게 바로 SET IMPLICIT_TRANSACTIONS 문입니다.
이겨에서 DBCC OPENTRAN 명령은 현재의 트랜잭션의 상태를 보여줍니다.
어떤가여??? 헤헤 도움이 되셨으면 좋겠는뎅.. 너무 쉬운 내용들이였습니다.
그럼,
| Tip (오라클의 Set implicit_transactions) |
| 오라클의 Set implicit_transactions 설정과 같은 개념은 오라클의 SQL *plus에서는 기본 설정되어 있습니다. 그러므로 오라클을 사용하던 개발자들이 sql 서버를 사용할때 흔히 실수하는것이 데이터를 잘못 수정해도 rollback문으로 복구가 가능한것으로 알고 있다는것입니다. 중요한데여. SQL 서버의 기본설정은 트랜잭션의 자동 종료, 즉 AUTO COMMIT 설정으로 잘못 수정한 데이터 자체로는 복구가 불가능하며 데이터 백업과 리스토어 과정을 사용해야 합니다. |
|
|
||||
|
이메일 문의 : cak0280@nate.com Copyright 2000 By ENTERSOFT.KR All Rights Reserved. |