Giao dịch (Transactions)

Transaction (Giao dịch) là đơn vị công việc logic gồm một hoặc nhiều thao tác được thực thi như một khối hoàn chỉnh — hoặc tất cả thành công, hoặc không có gì được thực hiện. Đây là nền tảng của tính toàn vẹn dữ liệu trong SQL Server.

1. ACID Properties

ACID là bốn thuộc tính cơ bản mà mọi transaction phải đảm bảo:

Atomicity (Tính nguyên tử)

“All or nothing” — Tất cả thao tác trong transaction thực hiện thành công toàn bộ, hoặc nếu bất kỳ thao tác nào thất bại, toàn bộ transaction bị ROLLBACK về trạng thái ban đầu.

Ví dụ thực tế: Chuyển tiền ngân hàng — trừ tiền tài khoản A và cộng tiền tài khoản B phải là một đơn vị. Không thể trừ mà không cộng.

BEGIN TRANSACTION;
    UPDATE BankAccounts SET Balance = Balance - 1000000 WHERE AccountId = 1;
    UPDATE BankAccounts SET Balance = Balance + 1000000 WHERE AccountId = 2;
    -- Nếu dòng trên fail, toàn bộ rollback
COMMIT TRANSACTION;

Consistency (Tính nhất quán)

Transaction phải đưa database từ trạng thái hợp lệ (valid state) này sang trạng thái hợp lệ khác. Tất cả constraints (PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE), triggers, và business rules phải được thỏa mãn sau khi transaction hoàn thành.

Ví dụ: Tổng số dư của tất cả tài khoản ngân hàng trước và sau khi chuyển tiền phải bằng nhau.

Isolation (Tính cô lập)

Các transaction đang thực thi đồng thời phải độc lập với nhau — transaction này không được nhìn thấy kết quả trung gian (chưa commit) của transaction khác. Mức độ cô lập được điều chỉnh qua Isolation Levels.

Durability (Tính bền vững)

Sau khi transaction được COMMIT, dữ liệu phải được lưu trữ vĩnh viễn kể cả khi hệ thống crash, mất điện. SQL Server đảm bảo điều này thông qua Write-Ahead Logging (WAL).

2. Transaction Syntax

BEGIN TRANSACTION

-- Tường minh
BEGIN TRANSACTION;
-- Hoặc viết tắt
BEGIN TRAN;

-- Đặt tên transaction (hỗ trợ nested transaction với savepoints)
BEGIN TRANSACTION TransferMoney;

COMMIT TRANSACTION

COMMIT TRANSACTION;
-- Hoặc
COMMIT TRAN;
-- Hoặc chỉ
COMMIT;

ROLLBACK TRANSACTION

ROLLBACK TRANSACTION;
-- Hoặc
ROLLBACK TRAN;
-- Hoặc
ROLLBACK;

Ví dụ hoàn chỉnh

BEGIN TRANSACTION;
BEGIN TRY
    INSERT INTO Orders (CustomerId, OrderDate, TotalAmount)
    VALUES (101, GETDATE(), 500000);

    DECLARE @OrderId INT = SCOPE_IDENTITY();

    INSERT INTO OrderDetails (OrderId, ProductId, Quantity, UnitPrice)
    VALUES (@OrderId, 5, 2, 250000);

    COMMIT TRANSACTION;
    PRINT 'Order created successfully';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    PRINT 'Error: ' + ERROR_MESSAGE();
    THROW; -- Re-throw để caller biết
END CATCH;

3. @@TRANCOUNT — Đếm Transaction lồng nhau

@@TRANCOUNT trả về số lượng transaction đang mở trong session hiện tại:

• BEGIN TRANSACTION → @@TRANCOUNT + 1
• COMMIT → @@TRANCOUNT - 1 (chỉ commit khi về 0)
• ROLLBACK → @@TRANCOUNT = 0 (rollback toàn bộ, bất kể nested level)

PRINT @@TRANCOUNT; -- 0

BEGIN TRANSACTION; -- @@TRANCOUNT = 1
    BEGIN TRANSACTION; -- @@TRANCOUNT = 2
        UPDATE Products SET Price = 100 WHERE ProductId = 1;
    COMMIT; -- @@TRANCOUNT = 1 (chưa thực sự commit!)
COMMIT; -- @@TRANCOUNT = 0, NOW actually committed

-- Nguy hiểm: ROLLBACK ở bất kỳ level nào cũng rollback toàn bộ
BEGIN TRANSACTION; -- @@TRANCOUNT = 1
    BEGIN TRANSACTION; -- @@TRANCOUNT = 2
        BEGIN TRANSACTION; -- @@TRANCOUNT = 3
        ROLLBACK; -- @@TRANCOUNT = 0, TOÀN BỘ bị rollback!

Lưu ý quan trọng: Trong stored procedures, nên kiểm tra @@TRANCOUNT để tránh rollback nhầm outer transaction:

CREATE PROCEDURE dbo.SafeInsert
AS
BEGIN
    DECLARE @TranCount INT = @@TRANCOUNT;
    
    IF @TranCount = 0
        BEGIN TRANSACTION; -- Chỉ mở nếu chưa có transaction
    ELSE
        SAVE TRANSACTION SavePoint1; -- Dùng savepoint nếu đã trong transaction
    
    BEGIN TRY
        -- Business logic
        INSERT INTO ...
        
        IF @TranCount = 0
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @TranCount = 0
            ROLLBACK TRANSACTION;
        ELSE
            ROLLBACK TRANSACTION SavePoint1;
        THROW;
    END CATCH;
END;

4. Savepoints

Savepoints cho phép đặt “điểm lưu” bên trong transaction để có thể rollback về điểm đó mà không rollback toàn bộ transaction.

BEGIN TRANSACTION;
    INSERT INTO AuditLog VALUES ('Process started', GETDATE());
    
    SAVE TRANSACTION BeforeOrder; -- Đặt savepoint
    
    BEGIN TRY
        INSERT INTO Orders (CustomerId) VALUES (999); -- 999 có thể không tồn tại
        SAVE TRANSACTION AfterOrder;
        
        INSERT INTO OrderDetails (OrderId, ProductId) VALUES (SCOPE_IDENTITY(), 1);
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() = 547 -- Foreign key violation
        BEGIN
            ROLLBACK TRANSACTION BeforeOrder; -- Chỉ rollback đến savepoint
            -- AuditLog INSERT vẫn còn!
            INSERT INTO AuditLog VALUES ('Order failed: FK violation', GETDATE());
        END
        ELSE
            THROW;
    END CATCH;
    
    INSERT INTO AuditLog VALUES ('Process completed', GETDATE());
COMMIT TRANSACTION; -- Commit tất cả những gì còn lại

Giới hạn của Savepoints:

• ROLLBACK TO SAVEPOINT không giảm @@TRANCOUNT
• Không thể dùng savepoint để commit một phần — chỉ có thể rollback về điểm đó
• Không hỗ trợ trong Distributed Transactions

5. Implicit vs Explicit Transactions

Explicit Transactions (Mặc định)

Mọi statement là auto-commit trừ khi bạn tường minh BEGIN TRANSACTION.

-- Auto-commit mode (mặc định):
INSERT INTO Table1 VALUES (1); -- Commit ngay lập tức
UPDATE Table2 SET Col = 1 WHERE Id = 1; -- Commit ngay lập tức

-- Explicit:
BEGIN TRANSACTION;
    INSERT INTO Table1 VALUES (1);
    UPDATE Table2 SET Col = 1 WHERE Id = 1;
COMMIT; -- Cả hai commit cùng lúc

Implicit Transactions

Khi SET IMPLICIT_TRANSACTIONS ON, SQL Server tự động mở transaction trước các DML/DDL statements. Bạn phải tường minh COMMIT hoặc ROLLBACK.

SET IMPLICIT_TRANSACTIONS ON;

INSERT INTO Products VALUES ('Widget', 100); -- SQL tự BEGIN TRAN
SELECT * FROM Products; -- Vẫn trong transaction
COMMIT; -- Bạn phải commit tường minh

-- Tiếp tục, SQL tự mở transaction mới
UPDATE Products SET Price = 200 WHERE ProductId = 1;
ROLLBACK; -- Rollback update

Cảnh báo: IMPLICIT_TRANSACTIONS ON thường gây ra vấn đề — DBA hay developer quên COMMIT gây ra long-running transactions. Không nên dùng trong ứng dụng production.

6. Auto-commit Mode

SQL Server mặc định chạy ở auto-commit mode: mỗi statement là một transaction riêng biệt, tự động commit khi thành công hoặc rollback khi thất bại.

-- Không có BEGIN TRAN:
INSERT INTO T1 VALUES (1); -- Tự động commit
UPDATE T2 SET C = 1; -- Tự động commit (riêng biệt)
DELETE FROM T3 WHERE Id = 5; -- Tự động commit (riêng biệt)

-- Nếu UPDATE fail vì constraint, chỉ UPDATE bị rollback, INSERT vẫn commit

7. Transaction Log & Write-Ahead Logging (WAL)

Transaction Log

SQL Server dùng Transaction Log (.ldf file) để ghi lại tất cả thay đổi dữ liệu trước khi áp dụng lên data pages.

Cấu trúc:

• Log file chia thành Virtual Log Files (VLFs)
• Mỗi VLF chứa nhiều Log Records
• Log records được ghi tuần tự (sequential I/O — nhanh hơn random I/O)

Write-Ahead Logging (WAL)

Quy tắc WAL: Log record phải được ghi vào disk trước khi data page tương ứng được ghi xuống disk.

Luồng hoạt động:

1. Transaction bắt đầu → SQL Server tạo log record "BEGIN TRAN"
2. UPDATE row → Ghi log record (Before Image + After Image) vào Log Buffer
3. COMMIT → SQL Server flush Log Buffer xuống disk (log flush)
4. COMMIT hoàn thành → User nhận được kết quả
5. (Nền) Checkpoint → Data pages được ghi xuống disk

Checkpoint

Checkpoint là quá trình ghi tất cả dirty pages (pages đã modify trong memory nhưng chưa ghi xuống disk) xuống disk:

-- Manual checkpoint
CHECKPOINT;

-- Xem thời gian recovery estimate
SELECT recovery_model_desc, log_reuse_wait_desc
FROM sys.databases WHERE name = DB_NAME();

Recovery sau crash:

1. REDO (Roll forward): Áp lại tất cả log records của committed transactions sau checkpoint
2. UNDO (Roll back): Hủy tất cả log records của uncommitted transactions

8. Long-running Transactions — Tác hại và Xử lý

Tác hại

1. Transaction Log không thể truncate (reuse)
   → Log file phình to, hết disk space
   
2. Giữ locks trong thời gian dài
   → Blocking nhiều session khác
   
3. Version Store bloat (nếu dùng RCSI/Snapshot)
   → tempdb phình to
   
4. Rollback time = Run time
   → Transaction chạy 2h có thể rollback 2h

Phát hiện Long-running Transactions

-- Tìm transactions đang chạy lâu
SELECT 
    ses.session_id,
    ses.login_name,
    ses.host_name,
    ses.status,
    req.command,
    req.wait_type,
    req.wait_time / 1000 AS wait_seconds,
    DATEDIFF(MINUTE, req.start_time, GETDATE()) AS running_minutes,
    tran.open_transaction_count,
    SUBSTRING(sql.text, (req.statement_start_offset/2)+1, 200) AS current_statement
FROM sys.dm_exec_sessions ses
INNER JOIN sys.dm_exec_requests req ON ses.session_id = req.session_id
INNER JOIN sys.dm_exec_session_wait_stats tran ON ses.session_id = tran.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) sql
WHERE req.open_transaction_count > 0
ORDER BY running_minutes DESC;

-- Kiểm tra oldest active transaction
SELECT 
    transaction_id,
    transaction_begin_time,
    DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS minutes_open,
    name AS transaction_name
FROM sys.dm_tran_active_transactions
WHERE transaction_type = 1 -- User transaction
ORDER BY transaction_begin_time;

Best Practices xử lý long-running transactions

-- Thay vì một transaction lớn xóa 1 triệu rows:
-- BAD:
BEGIN TRANSACTION;
DELETE FROM OldLogs WHERE LogDate < '2020-01-01'; -- 1 triệu rows
COMMIT;

-- GOOD: Chia thành batches
DECLARE @BatchSize INT = 10000;
DECLARE @Deleted INT = 1;

WHILE @Deleted > 0
BEGIN
    DELETE TOP (@BatchSize) FROM OldLogs WHERE LogDate < '2020-01-01';
    SET @Deleted = @@ROWCOUNT;
    PRINT 'Deleted ' + CAST(@Deleted AS VARCHAR) + ' rows';
    -- Nhường CPU/IO cho session khác
    WAITFOR DELAY '00:00:01';
END;

9. Distributed Transactions & MSDTC

Distributed Transaction span qua nhiều SQL Server instances hoặc resource managers khác nhau.

Microsoft Distributed Transaction Coordinator (MSDTC)

MSDTC điều phối 2-Phase Commit Protocol:

1. Prepare Phase: MSDTC hỏi tất cả participants “bạn sẵn sàng commit chưa?”
2. Commit/Rollback Phase: Nếu tất cả OK → Commit. Một người fail → tất cả Rollback.

-- Explicit distributed transaction
BEGIN DISTRIBUTED TRANSACTION;
    -- Update trên server local
    UPDATE LocalDB.dbo.Inventory 
    SET Quantity = Quantity - 10 
    WHERE ProductId = 1;

    -- Update trên linked server (remote)
    UPDATE [RemoteWarehouse].WMS.dbo.Stock 
    SET OnHand = OnHand - 10 
    WHERE ProductId = 1;

COMMIT;

Khi nào tự động trở thành Distributed Transaction

-- SQL Server tự động promote thành distributed transaction khi:
-- 1. Query qua Linked Server trong transaction hiện tại
BEGIN TRANSACTION;
    UPDATE LocalTable SET ...; -- Local transaction
    SELECT * FROM [LinkedServer].RemoteDB.dbo.Table; -- Tự động promote!
COMMIT;

Vấn đề với MSDTC:

• Latency cao (network round trips cho handshake)
• MSDTC là single point of failure
• Khó debug khi có sự cố
• Modern architecture (microservices) thường dùng Saga Pattern với compensating transactions thay thế.

10. Error Handling với Transactions

Pattern chuẩn: TRY-CATCH-ROLLBACK

SET XACT_ABORT ON; -- Best practice: tự động rollback khi có lỗi
BEGIN TRANSACTION;
BEGIN TRY
    -- Step 1: Validate
    IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerId = @CustomerId)
        THROW 50001, 'Customer not found', 1;

    -- Step 2: Create Order
    INSERT INTO Orders (CustomerId, CreatedAt)
    VALUES (@CustomerId, GETDATE());
    
    DECLARE @OrderId INT = SCOPE_IDENTITY();

    -- Step 3: Add Order Lines
    INSERT INTO OrderLines (OrderId, ProductId, Qty)
    SELECT @OrderId, ProductId, Qty
    FROM @OrderItems;

    -- Step 4: Update Inventory
    UPDATE Products 
    SET Stock = Stock - ol.Qty
    FROM Products p
    INNER JOIN OrderLines ol ON p.ProductId = ol.ProductId
    WHERE ol.OrderId = @OrderId;

    -- Commit nếu tất cả OK
    COMMIT TRANSACTION;
    SELECT @OrderId AS NewOrderId;
END TRY
BEGIN CATCH
    -- Rollback nếu có lỗi bất kỳ
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    
    -- Log error
    INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorLine, ErrorTime)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE(), GETDATE());
    
    -- Re-throw để caller xử lý
    THROW;
END CATCH;

XACT_ABORT và ảnh hưởng

-- Không có XACT_ABORT:
BEGIN TRANSACTION;
    INSERT INTO T1 VALUES (1); -- OK
    INSERT INTO T1 VALUES (1); -- Duplicate KEY error → chỉ statement này fail
    INSERT INTO T1 VALUES (2); -- Statement này vẫn chạy!
COMMIT; -- @@TRANCOUNT = 1, commit T1=1 và T1=2 (không có duplicate)

-- Với XACT_ABORT ON:
SET XACT_ABORT ON;
BEGIN TRANSACTION;
    INSERT INTO T1 VALUES (1); -- OK
    INSERT INTO T1 VALUES (1); -- Error → TOÀN BỘ transaction bị rollback, @@TRANCOUNT = 0
    INSERT INTO T1 VALUES (2); -- Không được chạy
-- @@TRANCOUNT đã = 0, không cần ROLLBACK

XACT_STATE() — kiểm tra trạng thái transaction trong CATCH block:

BEGIN CATCH
    DECLARE @XactState INT = XACT_STATE();
    IF @XactState = -1
        ROLLBACK; -- Transaction không thể commit, PHẢI rollback
    ELSE IF @XactState = 1
        COMMIT; -- Transaction có thể commit (uncommittable = false)
    -- @XactState = 0: Không có transaction đang mở
END CATCH;

11. Best Practices

✅ Nên làm

-- 1. Giữ transaction càng ngắn càng tốt
BEGIN TRANSACTION;
    -- Chỉ DML statements cần thiết
    UPDATE Orders SET Status = 'Processed' WHERE OrderId = @Id;
    INSERT INTO Audit VALUES (@Id, GETDATE());
COMMIT;

-- 2. Luôn dùng SET XACT_ABORT ON trong stored procedures
CREATE PROCEDURE dbo.ProcessOrder @OrderId INT
AS
BEGIN
    SET XACT_ABORT ON;
    SET NOCOUNT ON;
    BEGIN TRANSACTION;
    -- ...
    COMMIT;
END;

-- 3. Kiểm tra @@TRANCOUNT trước ROLLBACK
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

-- 4. Dùng THROW thay vì RAISERROR để re-throw
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW; -- Giữ nguyên error info gốc
END CATCH;

❌ Không nên làm

-- 1. Tương tác người dùng bên trong transaction
BEGIN TRANSACTION;
    UPDATE Products SET Price = @NewPrice WHERE ProductId = @Id;
    -- ĐỪNG: Chờ user confirm trước khi commit
    -- Lock giữ trong khi chờ → blocking!
COMMIT;

-- 2. Transaction quá lớn
BEGIN TRANSACTION;
    DELETE FROM Logs WHERE CreatedAt < '2020-01-01'; -- 10 triệu rows!
COMMIT;
-- → Log file phình to, block other sessions, rollback time lâu

-- 3. Quên ROLLBACK trong error handling
BEGIN TRANSACTION;
    UPDATE ...;
    IF @@ERROR <> 0
        RETURN; -- NGUY HIỂM: Transaction vẫn đang mở!
COMMIT;

-- 4. Dùng SELECT ... NOLOCK trong financial queries
SELECT SUM(Balance) FROM Accounts WITH (NOLOCK); -- Có thể sai vì dirty read!

Tóm tắt nhanh