/*******************************************************************************************
-- Title : [2k5] 트랜잭션에서의 TRY ~ CATCH 처리
-- Reference : hanbitbook.co.kr
-- Key word : try catch begin tran commit rollback
*******************************************************************************************/
use tempdb;
go
drop table bankbook;
go
create table bankbook
(name nvarchar(10),
money int ,
constraint ck_money
check (money >= 0)
);
go
insert into bankbook values ('우재남', 1000);
insert into bankbook values ('당탕이', 0);
go
select * from bankbook;
go
update bankbook set money = money - 500 where name = '우재남';
update bankbook set money = money + 500 where name = '당탕이';
go
select * from bankbook;
go
begin tran
update bankbook set money = money - 500 where name = '우재남';
commit tran
go
select * from bankbook;
go
begin tran
update bankbook set money = money + 500 where name = '당탕이';
commit tran
go
select * from bankbook;
go
update bankbook set money = money - 600 where name = '우재남'; --처리안됨
update bankbook set money = money + 600 where name = '당탕이'; --처리됨
go
select * from bankbook;
go
begin tran -- 1번트랜잭션
update bankbook set money = money - 600 where name = '우재남';
-- 오류가 발생되어 수행이 안됨. (롤백이 일어날 것으로 예상됨)
commit tran
go
select * from bankbook;
go
begin tran -- 2번트랜잭션
update bankbook set money = money + 600 where name = '당탕이';
-- 정상적으로수행됨
commit tran
go
select * from bankbook;
go
update bankbook set money = money - 600 where name = '당탕이';
go
select * from bankbook;
go
begin tran
update bankbook set money = money - 600 where name = '우재남'; --처리안됨
update bankbook set money = money + 600 where name = '당탕이'; --처리됨
commit tran
go
select * from bankbook;
go
update bankbook set money = money - 600 where name = '당탕이';
go
begin try --하나도 처리 안됨
begin tran
update bankbook set money = money - 600 where name = '우재남';
update bankbook set money = money + 600 where name = '당탕이';
commit tran
end try
begin catch
rollback tran
end catch;
go
begin tran;
begin try
update bankbook set money = money - 600 where name = '우재남';
update bankbook set money = money + 600 where name = '당탕이';
end try
begin catch
rollback tran
end catch
commit tran /* 여기에 맞는 begin tran이 없어서 에러남 */
go
/*
메시지 3902, 수준 16, 상태 1, 줄 9
COMMIT TRANSACTION 요청에 해당하는 BEGIN TRANSACTION이 없습니다.
*/
select * from bankbook;
go
[2k5] 트랜잭션에서의 TRY ~ CATCH 처리
2008. 9. 7. 01:03
반응형
반응형