트랜젝션이라고 하는 많이 듣는 이야기.. SQL서버는 내부적으로 

작업의 최소 단위인 이 트랜젝션을 세분화 해 두었습니다.

　

트랜젝션의 종류~~

Autocommit transactions
Explicit transactions
Implicit transactions

　

이렇게 세가지 입니다.

각각의 트랜젝션에 대해서 소개를 해 드리자면?

Autocommit : 자동적인 커밋

 

--수행 안됩니다.

update 계좌 set 금액 = 금액 - 100 where id like '코난'

update 계좌 set 금액 = 금액 + 100 where id like '수선'

　

이런 작업을 자동으로 다음처럼 바꿉니다.

 

--수행 안됩니다.

begin tran

update 계좌 set 금액 = 금액 - 100 where id like '코난'

commit tran

　

begin tran

update 계좌 set 금액 = 금액 + 100 where id like '수선'

commit tran

　

　

이렇게 변화시키고 수행되는 트랜젝션을 

Autocommit  이라고 합니다. - SQL서버가 위처럼 바꾸는 것이지요.

　

Explicit 트랜젝션 을 이야기 하자면.. 바로 다음처럼 명시적으로 사용자가 트랜젝션을

정의하고 수행하는 것을 의미 합니다.

 

--수행 안됩니다.

begin tran

update 계좌 set 금액 = 금액 - 100 where id like '코난'

update 계좌 set 금액 = 금액 + 100 where id like '수선'

commit tran

　

이런 식으로 사용자가 직접 정의하는 것을 의미 하지요.

　

끝으로 Implicit 트랜젝션 입니다.

이녀석은 약간 특이한 녀석으로.. 사용자 - 또는 관리자가 정의해 두어야 하며..

한번 Implicit  이 활성화가 되면 모든 데이터 변경 작업후 반드시 Commit 문을 날려야만

데이터의 변경이 적용 됩니다. 가끔 들어오는 질문으로..

Q. DBMS관리자가 휴가중이라.. 잠시 SQL서버를 관리하는 중입니다.

 

--질문 내용 

use pubs

go

update titles set price = price * 2

　

select * from titles  --가격이 분명 *2배 되어 있다.　

라는 구문을 수행했으나.. 이상하게도 데이터가 변경되지 않습니다.

잠시후에 누군가 데이터를 바꿔 두는것인지 어떤지 알 수 없으나 잠시후 다른 사용자가

로긴해 봐 보면 가격이 다시 원상태로 돌아와 있습니다. 아마도 SQL서버의 버그인듯 합니다.

어떻게 해결해야 하나요? - 서비스팩을 설치하면 될까요?

　

물론 실화이며 종종 올라오는 질문 입니다. 

이런 이유는 다음과 같은 설정이 되어 있기 때문입니다.

바로

Implicit transactions 설정이 on으로 되어 있기 때문입니다.

 

use pubs
go

update titles set price = price * 2

select * from titles --가격이 *2로 되어 있다.

　

　

 


use pubs
go

set IMPLICIT_TRANSACTIONS on

update titles set price = price * 2

select * from titles --가격이 *2로 되어 있다.

--새로운 커넥션을 연다 -> 화일메뉴 -> 연결

--데이터를 조회한다.
select * from titles

--가격이 *2가 안되어 있다.

　

즉 위에서 보시는 바와 같이 set IMPLICIT_TRANSACTIONS on 옵션이 설정되어 있다는

것이며 위의 경우는 해당하는 커넥션 - 연결된 세션에만 설정한 것이고

이 설정을 서버측에서 구성할수도 있습니다. - 이건 관리자가 할 수 있지요.

　

이렇게 EM -> 서버 등록정보 부분의 연결 탭에서 연결되는 모든 사용자는 기본적으로

implicit transaction으로 연결하게 할 수 있으며..

sp_configure 옵션으로는..

 

sp_configure 

　

해 보시면...user options 이라는 이름의 설정이 있는데요.. 이 값은..

여러개의 설정이 이진수로 모여 있는 것입니다.

여기서..  해당하는 구성값중에서 'user options 옵션' 으로 온라인 도움말을 보시면

상세한 정보를 보실 수 있으며.. 밸루값2가 IMPLICIT_TRANSACTIONS 설정입니다.

예를들어. 현재의 sp_configure의 구성값이 3이라면?

DISABLE_DEF_CNST_CHK과 IMPLICIT_TRANSACTIONS이 설정되어

있다는 의미겠지요. - 기본적으로는 설정 안되어 있습니다.

뭐 각설하고... 결론적으로.. 저렇게 set implicit transaction이 걸려 있다면?

이렇게 하시면 됩니다.

 

use pubs
go

--IMPLICIT_TRANSACTIONS 옵션이 서버측에 걸려 있을때만 수행됩니다.

--set IMPLICIT_TRANSACTIONS on

update titles set price = price * 2

select * from titles --가격이 *2로 되어 있다.

commit tran

--실제 데이터가 변경되었다.

　

어떻습니까? commit tran이라고 주어야만 데이터 변경이 일어 나지요.

이것은 실제 ANSI표준으로 implicit tran이 on이 되어야 표준이지만.. SQL서버는

기본적으로 OFF로 되어 있습니다. - 오라클은 ON이 기본설정

또한 어플리케이션에서 연결할때 ADO로 연결한다면? ADO의 기본 연결 속성으로

implicit transaction은 OFF이기 때문에.. 많은 분들이 저 옵션값을 모르고 계시지요.

참고하시구요. 그럼 언제 저 implicit transaction이 자동으로 begin tran을 시작하는가!!

입니다.

ALTER TABLE FETCH REVOKE
CREATE GRANT SELECT
DELETE INSERT TRUNCATE TABLE
DROP OPEN UPDATE

이러한 구문이 쿼리중에 나온다면? SQL서버는 자동으로 begin tran 구문을 넣는것과

같다라고 판단하며 반드시 commit tran을 해야만 수정이 반영되게 합니다.

　

그럼 저 복잡 짜증인 implicit transaction은 왜 쓰는것인가?

간단합니다. update titles set price = 0 또는 종종 올라오는

update 회원테이블 암호 = 'AAAA' 라고.. 

where절 없이 무의식중에 쳐버린다면?

말그대로 다이죠.. -_-;;  이런 상황을 막고자... 설정하는 것이며..

쿼리 분석기로 데이터 수정작업등을 할 경우 안정성을 확보하기 위해 관리자가

서버측에 설정하는 케이스도 있습니다. - 위의 질문과 같은 케이스 이지요.

단순히 commit tran 이라고 적어만 주면 되며.. 일반 사용자의 쿼리를 직접 날려

발생하는 실수를 막고자 하는 것입니다.

　

다음은 분산 트랜젝션 입니다.

예를들어.. 시스템이 두개가 있을 경우 물리적으로 떨어져 있는 시스템에서

트랜젝셔널한 처리르 하고자 할 경우지요. 

이렇게.. Distributed Transaction Coordinator가 활성화가 양쪽 서버에 되어 있어야

하며..  물론 나중에 배우실 linked Server가 있으면 더욱 좋습니다.

트랜젝션의 시작은.. 

 

BEGIN DISTRIBUTED TRANSACTION

update 시스템이름A.디비명.소유자명.titles set price= price*2

update 시스템이름B.디비명.소유자명.titles set price= price*2

COMMIT TRAN --또는 ROLLBACK TRAN

　

이렇게 BEGIN DISTRIBUTED TRANSACTION 으로 하시면 되구요.

나머지는 같다고 보시면 됩니다. 중요한 부분으로 DTC활성화가 잘 되어있는지를

항상 조사 하셔야 하며 적절한 권한으로 로긴한 후 객체를 핸들할 수 있는지 역시 잘 파악하셔야

합니다. - 대부분의 경우 이 두가지의 문제로 안될 수 있습니다.

　

다음으로는 SET XACT_ABORT 에 대해서 알아보도록 할까요?

이녀석을 수행하기 전에 퀴즈 시간입니다. ^_^

 

--SET XACT_ABORT를 이용한 런타임시의 오류라도 에러시 롤백
USE pubs
GO

CREATE TABLE konan_TR_Test(
c1 int not null PRIMARY KEY
, c2 int
)
GO

--데이터 삽입
insert into konan_TR_Test(c1,c2) VALUES(1, 1)

--틀렸죠?
insertT into konan_TR_Test(c1,c2) VALUES(1, 1)

--틀렸죠?
insert into konan_TR_TestTTT(c1,c2) VALUES(1, 1)

--제약 위반이죠?
insert into konan_TR_Test(c1,c2) VALUES(1, 1)

--데이터 조회
select * from konan_TR_Test

--수행
BEGIN TRAN
insert into konan_TR_Test(c1,c2) VALUES(2, 2)
insert into konan_TR_Test(c1,c2) VALUES(2, 2)
insert into konan_TR_Test(c1,c2) VALUES(3, 3)
COMMIT TRAN
GO

--데이터 조회
SELECT * FROM konan_TR_Test

--제가 원한건 이게 아니라..
--세개중에서 하나라도 실패하면? 에러와 함께 모두 ROLLBACK 하고 싶습니다.!!


--현업에서 문제.
--학생 테이블을 생성한다. 
--drop table 수강
--drop table 학생
--drop table 과목
CREATE TABLE 학생( 
학생ID varchar(10) primary key 
, 학번 varchar(10) not null 
) 
GO


CREATE TABLE 과목( 
과목ID varchar(5) primary key 
, 과목명 varchar(20) not null 
) 


CREATE TABLE 수강( 
수강ID int primary key 
, 학생ID varchar(10) not null 
FOREIGN KEY REFERENCES 학생 (학생ID) 
, 과목ID varchar(5) 
FOREIGN KEY REFERENCES 과목 (과목ID) 
) 


--학생 삽입
BEGIN TRAN
INSERT 학생 VALUES ('KONAN', '1111') 
INSERT 학생 VALUES ('JOMIRYO', '2222') 
COMMIT TRAN

--과목 삽입
BEGIN TRAN
INSERT 과목 VALUES ('공수', '공업수학') 
INSERT 과목 VALUES ('디비', '데이터베이스') 
INSERT 과목 VALUES ('게임', '게임프로그래밍') 
COMMIT TRAN

--데이터 조회
select * from 학생
select * from 과목

BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수') 
INSERT 수강 VALUES (2, 'KONAN', '디비') 
INSERT 수강 VALUES (3, 'KONAN', '겜') --이게 문제입니다. 수강 실패!!!
COMMIT TRAN

--하지만~~ 데이터를 조회해 본다면? 조회
select * from 학생
select * from 과목
select * from 수강

--겜 과목은??
--내가 원한것은.. 무엇이라도 하나가 실패하면 모두 롤백 시키려는게 나의 목적!!
--어떻게 하면 될까?

--데이터 삭제
delete from 수강

select * from 수강

--넣을 경우? - 에러
BEGIN TRAN

INSERT 수강 VALUES (1, 'KONAN', '공수') 
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION

INSERT 수강 VALUES (2, 'KONAN', '디비') 
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION

INSERT 수강 VALUES (3, 'KONAN', '겜') 
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
--성공
print '삽입이 성공했습니다.'
COMMIT TRAN
GOTO END_BATCH

ERROR_TRANSACTION:
print '삽입 실패'
ROLLBACK TRAN

END_BATCH:
print 'BATCH가 끝났습니다.'
GO


--넣을 경우? - 성공
BEGIN TRAN

INSERT 수강 VALUES (1, 'KONAN', '공수') 
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION

INSERT 수강 VALUES (2, 'KONAN', '디비') 
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION

INSERT 수강 VALUES (3, 'KONAN', '게임') --게임임.
IF @@ERROR <> 0 GOTO ERROR_TRANSACTION
--성공
print '삽입이 성공했습니다.'
COMMIT TRAN
GOTO END_BATCH

ERROR_TRANSACTION:
print '삽입 실패'
ROLLBACK TRAN

END_BATCH:
print 'BATCH가 끝났습니다.'
GO

--데이터 조회
SELECT * FROM 수강

--만약 BATCH가 1000건이라면??
--말그대로 생노가다에 고생문이 훤하지 않을까요?

--테이블 데이터 삭제
delete from 수강

--새롭게 바뀐 처리.
--런타임시의 오류라도 모두 롤백을 시켜 버립니다.!!!!
SET XACT_ABORT ON
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수') 
INSERT 수강 VALUES (2, 'KONAN', '디비') 
INSERT 수강 VALUES (3, 'KONAN', '겜') --에러
COMMIT TRAN

--데이터 조회  -- 런타임중 하나가 오류라도 모두 롤백 되었다.
SELECT * FROM 수강

--새롭게 바뀐 처리.
SET XACT_ABORT ON
BEGIN TRAN
INSERT 수강 VALUES (1, 'KONAN', '공수') 
INSERT 수강 VALUES (2, 'KONAN', '디비') 
INSERT 수강 VALUES (3, 'KONAN', '게임') --정상
COMMIT TRAN

--데이터 조회
SELECT * FROM 수강

　

자 이정도면 트래젝션의 종류도 말씀을 어느정도 드린듯...

별 특별한 내용은 아니지만.. 저런것이 있다는 것~~ 정도.. 참고하시길 바랍니다.

다음으로 트랜젝션과 체크포인트를 통해.. 안쪽의 데이터를 복구하는 중요 프로세스인

REDO와 UNDO에 대해서 상세히 알아 보도록 하지요.

　

　

11. 트랜젝션과 잠금처리 - 2. 트랜젝션의 종류 문서의 끝입니다.

이 게시물을...




profile

부족하지만, SQLER의 누군가와 함께한 나눔을 통해 제가 더 많이 즐거웠습니다.
SQLER와 함께 즐거워 할수록, 그 나눔을 통해 더 많은 기회와 가치를 발견하게 되었습니다.
나눔의 생각이 앞으로도 계속, SQLER를 움직일 것입니다.

코난, 김대우 / SQLER 운영자 / 골라먹는 SQLER RSS 정보 구독 / 실시간 SQLER 소식 uxkorea 트위터