[MSSQL] BEGIN ... END
안녕하세요.
이 글은 제가 예전에 작성하였던 네이버 블로그의 글을 다시 올린 글입니다. 다소 기존의 글보다 내용의 퀄리티가 떨어질 수 있으니 이점 유의해주시기 바랍니다. 이 글을 작성 하였을 당시의 연차가 1년 차여서 글 쓰는 실력도 없었고 배우면서 정리던 단계라서 많이 부족합니다.
이 글을 읽기 전에 트랜잭션의 개념을 알아두면 정말 좋습니다. RDB에서 트랜잭션이란 작업의 단위를 뜻 합니다. RDB에서는 트랜잭션을 통해서 작업이 묶이고 해당 트랜잭션의 작업은 모두 성공 또는 모두 실패해야 합니다. 그렇기에 Begin End 구문을 통해서 작업을 묶어줘야 합니다. 관련하여 잘 정리된 블로그를 한번 읽어보시고 이 글을 읽으면 많은 도움이 됩니다.
https://csj000714.tistory.com/623
[DB] RDB 트랜잭션 (feat. 트랜잭션 격리 수준)
💡 본 문서는 'RDB 트랜잭션'에 대해 정리해놓은 글입니다. RDB는 관계가 중요하기에 일련의 과정 중 일부만 성공하면 안되기에 트랜잭션을 통해 관리합니다. 이러한 트랜잭션의 특성 및 격리성
csj000714.tistory.com
1) 테스트 준비하기
과거의 글이므로 글이 조금 이상합니다. 이 테스트는 MSSQL 2008에서 수행하였습니다.
프로시저를 만들다 보면 BEGIN... END 구문을 자주 접할 수 있습니다. (요즘은 잘 모르겠네요...?)
하지만 그냥 아무 이유 없이 BEGIN...END 를 사용 하는 인원이 많은데요.
어떤 경우에 BEGIN ... END로 묶어야 할까요?
프로시저를 만들다 보면 BEGIN ... END가 많은데 다음과 같은 상황이 있다고 가정해 봅시다.
예제 테이블 만들기
CREATE TABLE CLOTHING_BEY_LOG
(
IDX INT IDENTITY(1,1)
,UserID VARCHAR(20)
,Phone VARCHAR(13)
,Product_Num INT
,Product_Name VARCHAR(30)
,Regdate DATETIME
CONSTRAINT PK_CLOTHING_BEY_LOG PRIMARY KEY (IDX)
)
GO
CREATE TABLE COMPUTER_BUY_LOG
(
IDX INT IDENTITY(1,1)
,UserID VARCHAR(20)
,Phone VARCHAR(13)
,Product_Num INT
,Product_Name VARCHAR(30)
,Regdate DATETIME
CONSTRAINT PK_COMPUTER_BUY_LOG PRIMARY KEY (IDX)
)
GO
CREATE TABLE PRODUCT
(
Product_Num INT
,Product_Name VARCHAR(30)
,Product_Price INT
CONSTRAINT PK_PRODUCT PRIMARY KEY (Product_Num)
)
GO
CREATE TABLE USE_Money
(
UserID VARCHAR(20)
,Product_Num INT
,USE_Money INT
CONSTRAINT PK_USE_Money PRIMARY KEY (UserID,Product_Num)
)
GO
각각의 테이블의 역할은 다음과 같습니다.
1. CLOTHING_BEY_LOG 유저가 의류상품을 구매하면 상품 로그를 남김
2. COMPUTER_BUY_LOG 유저가 컴퓨터 상품을 구매하면 상품 로그를 남김
3. PRODUCT 상품에 관한 정보가 들어 있음
4. USE_Money 유저가 특정 상품을 구매하는데 총얼마를 구매하였는지 보여줌
보통 CLOTHING_BEY_LOG, COMPUTER_BUY_LOG를 합쳐서 타입으로 구분하여 나눠줍니다. 하지만 이 경우는 테스트를 위해서 2개의 테이블로 나누었습니다.
테스트 데이터 입력
테스트를 위해서 2건의 상품을 입력합니다. 한 개는 의류 상품이고 한개는 컴퓨터 상품입니다.
INSERT INTO DBO.PRODUCT VALUES (1001,'코트(의류)',1000)
INSERT INTO DBO.PRODUCT VALUES (2001,'노트북(컴퓨터)',100000)
유저가 구매가 가능하도록 프로시저를 만들어 보도록 합니다.
프로시저 생성
CREATE PROC USER_BUY
@Type TINYINT -- 1번의류,2번컴퓨터
,@UserID VARCHAR(20)
,@Phone VARCHAR(13)
,@Product_Num INT
AS
SET NOCOUNT ON
-- 상품정보를담아올변수선언
DECLARE @Product_Price INT
DECLARE @Product_Name VARCHAR(30)
-- 상품정보가져오기
SELECT @Product_Name = Product_Name , @Product_Price = Product_Price
FROM DBO.PRODUCT WITH(NOLOCK)
WHERE Product_Num = @Product_Num
-- 트랜잭션으로처리내용묶음
BEGIN TRAN
BEGIN TRY
-- 의류구매유저정보INSERT
IF @Type = 1
INSERT INTO DBO.CLOTHING_BEY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())
-- 유저사용금액+
UPDATE DBO.USE_Money
SET USE_Money = USE_Money + @Product_Price
WHERE UserID = @UserID
IF @@ROWCOUNT = 0
INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)
-- 컴퓨터구매유저정보INSERT
ELSE IF @Type = 2
INSERT INTO DBO.COMPUTER_BUY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())
-- 유저사용금액+
UPDATE DBO.USE_Money
SET USE_Money = USE_Money + @Product_Price
WHERE UserID = @UserID
IF @@ROWCOUNT = 0
INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)
COMMIT TRAN
RETURN 0
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN 1
END CATCH
GO
2) 테스트해보기
유저가 1번 상품 즉, 의류에서 코트를 구매한다고 생각해 보겠습니다.
DECLARE @VAL INT
EXEC @VAL = USER_BUY 1,'Joo Hyoung Kwon','010-1234-5678',1001
SELECT @VAL
'Joo Hyoung Kwon' 유저가 1번 코트를 구매하였습니다. 데이터를 확인해보겠습니다.
SELECT * FROM CLOTHING_BEY_LOG
SELECT * FROM COMPUTER_BUY_LOG
SELECT * FROM PRODUCT
SELECT * FROM USE_Money
위의 SELECT 문의 순서와 실행결과 순서가 같습니다.
1번에 CLOTHING_BEY_LOG 테이블에는 로그가 잘 남았다.
2번에 COMPUTER_BUY_LOG 테이블에는 정상적으로 로그가 남지 않았다.
3번에 PRODUCT의 Product_price (상품가격)대로 3번 USE_Money에 USE_Money(사용금액)이 잘 남았나 봤는데 2000원이다.
무엇이 잘못되었을까요? 문제는 IF문이 인식하는 범위입니다.
IF문은 @Type = 1에 조건에 걸리고 다음 구문입니다.
INSERT INTO DBO.CLOTHING_BEY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())
INSERT 구문까지 밖에 인식하지 못하고 그다음에 구문은 조건에 같이 있는 구문이라고 생각하지 못합니다.
그러므로 다음 아래의 쿼리가 2번 실행되는 것입니다.
UPDATE DBO.USE_Money
SET USE_Money = USE_Money + @Product_Price
WHERE UserID = @UserID
이것을 방지하기 위해서 필요한 것이 바로 BEGIN... END 입니다.
3) 문제 해결하기
아래와 같이 프로시저에 BEGIN ... END을 입력하여 트랜잭션으로 묶어줄 수 있습니다.
CREATE PROC USER_BUY
@Type TINYINT -- 1번의류,2번컴퓨터
,@UserID VARCHAR(20)
,@Phone VARCHAR(13)
,@Product_Num INT
AS
SET NOCOUNT ON
-- 상품정보를담아올변수선언
DECLARE @Product_Price INT
DECLARE @Product_Name VARCHAR(30)
-- 상품정보가져오기
SELECT @Product_Name = Product_Name , @Product_Price = Product_Price
FROM DBO.PRODUCT WITH(NOLOCK)
WHERE Product_Num = @Product_Num
-- 트랜잭션으로처리내용묶음
BEGIN TRAN
BEGIN TRY
-- 의류구매유저정보INSERT
IF @Type = 1
BEGIN -- BEGIN 추가
INSERT INTO DBO.CLOTHING_BEY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())
-- 유저사용금액+
UPDATE DBO.USE_Money
SET USE_Money = USE_Money + @Product_Price
WHERE UserID = @UserID
IF @@ROWCOUNT = 0
INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)
END -- END 추가
-- 컴퓨터구매유저정보INSERT
ELSE IF @Type = 2
BEGIN -- BEGIN 추가
INSERT INTO DBO.COMPUTER_BUY_LOG ( UserID, Phone, Product_Num, Product_Name, Regdate)
VALUES (@UserID,@Phone,@Product_Num,@Product_Name,GETDATE())
-- 유저사용금액+
UPDATE DBO.USE_Money
SET USE_Money = USE_Money + @Product_Price
WHERE UserID = @UserID
IF @@ROWCOUNT = 0
INSERT INTO DBO.USE_Money VALUES (@UserID,@Product_Num,@Product_Price)
END -- END 추가
COMMIT TRAN
RETURN 0
END TRY
BEGIN CATCH
ROLLBACK TRAN
RETURN 1
END CATCH
GO
프로시저를 만들고 데이터를 전부 초기화해줍니다.
TRUNCATE TABLE CLOTHING_BEY_LOG
TRUNCATE TABLE USE_Money
다시 한번 구매를 합니다.
DECLARE @VAL INT
EXEC @VAL = USER_BUY 1,'Joo Hyoung Kwon','010-1234-5678',1001
SELECT @VAL
구매한 결과를 다시 한번 확인합니다.
테이블의 SELECT 순서는 위의 SELECT와 똑같습니다.
여기서 눈여겨볼 것은 마지막의 USE_Money 테이블입니다.
우리는 1개의 코트를 구매하였고, 그 기록이 잘 적립되었습니다.
이렇듯 BEGIN... END는 상당히 중요한 역할을 해줍니다.
프로세스가 만약에 1개만 동작한다면 (INSERT나 UPDATE가 둘 중 하나만 동작하면) 그렇게 할 필요는 없습니다.
하지만 여러 개의 액션이 일어나는 경우 BEGIN... END로 작업의 단위 (처리가 일어나는 단위)로 묶어 줘야 합니다.
출처
https://blog.naver.com/dba_burning/60205275811
Ms SQL_ Begin ... End
프로시저를 만들다 보면 BEGIN ... END 구문을 자주 접할 수 있다. 하지만 그...
blog.naver.com