[MSSQL] 트랜잭션 (Transaction) 실무 고급 주제 3가지

✅ MSSQL 트랜잭션 실무 고급 주제 3가지

---

📌 1. 트랜잭션 테스트 시나리오 생성법

🎯 목적

• 트랜잭션 중 락/데드락/동시성 문제를 사전에 재현하고 테스트
• 병렬 사용자 시나리오에 대비

---

🛠 준비 사항

• 테스트용 테이블 및 데이터
• 두 개 이상의 세션(SSMS 창 2개 또는 SQLCMD 2개)
• WAITFOR DELAY, BEGIN TRAN, COMMIT 등을 적절히 사용

---

🔁 기본 테스트 시나리오 예시

시나리오: 두 사용자가 같은 레코드를 UPDATE하려고 함

🧪 세션 1 (사용자 A)

BEGIN TRAN
UPDATE Users SET Age = Age + 1 WHERE UserID = 'A1';
WAITFOR DELAY '00:00:10';
COMMIT;

 

🧪 세션 2 (사용자 B)

WAITFOR DELAY '00:00:02';  -- 약간 뒤에 실행되게
BEGIN TRAN
UPDATE Users SET Age = Age + 2 WHERE UserID = 'A1';
COMMIT;

 

🔍 기대 결과

• 세션 2는 세션 1이 COMMIT할 때까지 기다리게 됨
• 즉, Blocking 테스트 성공

---

🔄 데드락 테스트 시나리오

세션 1

BEGIN TRAN
UPDATE TableA SET Val = 1 WHERE ID = 1;
WAITFOR DELAY '00:00:05';
UPDATE TableB SET Val = 1 WHERE ID = 1;
COMMIT;

 

세션 2

BEGIN TRAN
UPDATE TableB SET Val = 1 WHERE ID = 1;
WAITFOR DELAY '00:00:05';
UPDATE TableA SET Val = 1 WHERE ID = 1;
COMMIT;

 

🔥 순서가 다르므로 교착 상태 발생 → 한 세션은 자동 ROLLBACK됨

---

📌 2. 데드락 발생 로그 분석 실습

🎯 목표

• 데드락이 발생하면 자동으로 캡처하여 원인 분석 가능

---

✅ 1) 데드락 Extended Event 세션 생성

CREATE EVENT SESSION DeadlockTracker
ON SERVER
ADD EVENT sqlserver.deadlock_graph
ADD TARGET package0.ring_buffer
WITH (STARTUP_STATE = ON);

ALTER EVENT SESSION DeadlockTracker ON SERVER STATE = START;

 

---

✅ 2) 데드락 발생시키고 로그 확인

-- 로그 조회
SELECT CAST(event_data AS XML) AS DeadlockReport
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t
    ON s.address = t.event_session_address
JOIN sys.dm_xe_session_event_actions a
    ON s.address = a.event_session_address
WHERE s.name = 'DeadlockTracker';

📄 XML로 데드락의 주체, 기다리는 객체, 해결 방식 등을 파악할 수 있음

---

✅ 3) SSMS에서 시각적으로 확인

• SSMS → "Extended Events" → "Sessions" → DeadlockTracker
• 우클릭 → "Watch Live Data" → 이벤트 클릭
• Deadlock 그래프 확인 가능

---

📌 3. SP 내 트랜잭션 안전 템플릿

🎯 목표

• SP에서 트랜잭션 처리 중 오류 발생 시 자동 ROLLBACK하고, 정상 처리 시 COMMIT
• 실무에서 가장 안정적인 패턴

---

✅ 표준 템플릿

CREATE PROCEDURE SafeUpdateProc
    @UserID UNIQUEIDENTIFIER,
    @Points INT
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- 핵심 로직
        UPDATE Users
        SET Points = Points + @Points
        WHERE UserID = @UserID;

        -- 더 복잡한 로직이 있어도 계속 진행

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK;

        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        THROW 50000, @ErrorMessage, 1;
    END CATCH
END;

---

✅ 핵심 구성 요소 설명

요소설명

BEGIN TRY	오류 처리 영역
BEGIN TRANSACTION	명시적 트랜잭션 시작
@@TRANCOUNT > 0	트랜잭션이 열려있는 경우에만 ROLLBACK 수행
ERROR_MESSAGE()	오류 메시지 추출
THROW	사용자 정의 예외 다시 발생

---

💡 팁

• 다중 SP를 호출하는 경우, 최상위 SP만 트랜잭션을 관리하게 하세요 (중첩 트랜잭션 위험)
• 내부 SP에서는 IMPLICIT TRANSACTION을 피하고 명시적으로 처리

---

🧠 실무 종합 정리

주제핵심 요약

트랜잭션 테스트	블로킹, 데드락 재현 시나리오로 사전 테스트
데드락 분석	Extended Event로 XML 로그 추출 + 시각적 분석
SP 트랜잭션 템플릿	TRY/CATCH + TRAN/ROLLBACK/THROW 구조 필수