트랜잭션

COMMIT;      -- 시작한 트랜잭션이 없는데
ROLLBACK;    -- 마찬가지

SELECT @@TRANCOUNT AS 시작;      -- 0
BEGIN TRAN;
SELECT @@TRANCOUNT AS 한번;      -- 1
BEGIN TRAN;
SELECT @@TRANCOUNT AS 두번;      -- 2
COMMIT;
SELECT @@TRANCOUNT AS 안쪽커밋뒤;  -- 1
COMMIT;
SELECT @@TRANCOUNT AS 바깥커밋뒤;  -- 0

BEGIN TRAN;
    UPDATE Board.POSTS SET hit_count = 99999 WHERE num = 250;
    BEGIN TRAN;
        UPDATE Board.POSTS SET hit_count = 88888 WHERE num = 251;
    COMMIT;                                  -- 안쪽을 커밋했습니다
ROLLBACK;                                    -- 바깥을 되돌립니다

SELECT num, hit_count FROM Board.POSTS WHERE num IN (250, 251) ORDER BY num;

BEGIN TRAN;
    UPDATE Board.POSTS SET hit_count = 99999 WHERE num = 250;
    BEGIN TRAN;
        UPDATE Board.POSTS SET hit_count = 88888 WHERE num = 251;
    ROLLBACK;                                -- 안쪽만 되돌리려 했습니다

SELECT @@TRANCOUNT AS 남은트랜잭션;
SELECT num, hit_count FROM Board.POSTS WHERE num IN (250, 251) ORDER BY num;

CREATE OR ALTER PROCEDURE Board.P_TRAN_BAD
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;
    UPDATE Board.POSTS SET hit_count = 33333 WHERE num = 250;
    -- COMMIT 을 잊었습니다
END
GO
EXEC Board.P_TRAN_BAD;
SELECT @@TRANCOUNT AS 프로시저를_나온뒤;

CREATE OR ALTER PROCEDURE Board.P_TRAN_BAD2
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;
    UPDATE Board.POSTS SET hit_count = 44444 WHERE num = 250;
    ROLLBACK;
END
GO
BEGIN TRAN;                    -- 부르는 쪽이 시작한 트랜잭션입니다
EXEC Board.P_TRAN_BAD2;
SELECT @@TRANCOUNT AS 바깥트랜잭션;
COMMIT;

BEGIN TRAN;
    UPDATE Board.POSTS SET hit_count = 11111 WHERE num = 250;

    SAVE TRANSACTION sp1;                    -- 여기에 표시를 남깁니다

    UPDATE Board.POSTS SET hit_count = 22222 WHERE num = 251;

    ROLLBACK TRANSACTION sp1;                -- 표시까지만 되돌립니다
    SELECT @@TRANCOUNT AS 부분롤백뒤;
    SELECT num, hit_count FROM Board.POSTS WHERE num IN (250,251) ORDER BY num;
ROLLBACK;

-- 같은 코드를 XACT_ABORT 만 바꿔 두 번 돌립니다.
BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = 111 WHERE num = 251;
SAVE TRANSACTION sp1;
BEGIN TRY
    UPDATE Board.POSTS SET hit_count = 222 WHERE num = 250;
    INSERT INTO Board.COMMENTS (post_num, user_num, content, reg_date)
    VALUES (99999, 1, N'x', '2026-03-01');      -- 547 로 막힙니다
END TRY
BEGIN CATCH
    SELECT XACT_STATE() AS 상태;
    IF XACT_STATE() = 1 ROLLBACK TRANSACTION sp1;
END CATCH
IF @@TRANCOUNT > 0 ROLLBACK;

BEGIN TRAN;
UPDATE Board.POSTS SET hit_count = hit_count WHERE board_num = 2;

SELECT resource_type AS 자원, request_mode AS 방식, COUNT(*) AS 개수
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID AND resource_type <> 'DATABASE'
GROUP BY resource_type, request_mode;

ROLLBACK;

CREATE OR ALTER PROCEDURE Board.P_HIT_SET
    @num int, @hit int
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- 들어올 때 바깥에 트랜잭션이 있었는지 기억합니다.
    DECLARE @outer bit = CASE WHEN @@TRANCOUNT > 0 THEN 1 ELSE 0 END;

    BEGIN TRY
        IF @outer = 0 BEGIN TRAN;

        UPDATE Board.POSTS SET hit_count = @hit WHERE num = @num;
        IF @hit < 0 THROW 50002, N'조회수는 음수가 될 수 없습니다', 1;

        IF @outer = 0 COMMIT;
    END TRY
    BEGIN CATCH
        -- 내가 시작한 것만 되돌립니다.
        IF @outer = 0 AND XACT_STATE() <> 0 ROLLBACK;
        THROW;                     -- 바깥이 있으면 바깥이 결정합니다
    END CATCH
END