Transactions & Concurrency - Tổng quan

Phần này bao gồm các kiến thức về Giao dịch (Transactions) và Kiểm soát đồng thời (Concurrency Control) trong SQL Server — hai chủ đề cốt lõi trong phát triển ứng dụng enterprise và phỏng vấn kỹ thuật.

Các chủ đề con

Q&A Phỏng vấn

🟢 Junior Level

Q1: ACID là gì? Giải thích từng thuộc tính.

A:

• Atomicity (Tính nguyên tử): Tất cả các thao tác trong transaction phải thành công toàn bộ hoặc không có thao tác nào được thực hiện. Nếu một bước thất bại, toàn bộ transaction sẽ bị ROLLBACK.
• Consistency (Tính nhất quán): Transaction phải đưa database từ trạng thái hợp lệ này sang trạng thái hợp lệ khác. Các ràng buộc (constraints), triggers, cascade phải được đảm bảo.
• Isolation (Tính cô lập): Các transaction đang chạy đồng thời không được nhìn thấy dữ liệu lẫn nhau ở trạng thái trung gian. Mức độ cô lập được điều chỉnh qua Isolation Levels.
• Durability (Tính bền vững): Sau khi transaction COMMIT, dữ liệu phải được lưu trữ vĩnh viễn kể cả khi hệ thống bị sập. SQL Server đảm bảo điều này qua Write-Ahead Logging (WAL).

Q2: Cú pháp cơ bản của Transaction trong SQL Server là gì?

A:

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountId = 2;
COMMIT TRANSACTION;

-- Hoặc khi có lỗi:
BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
    -- Phát hiện lỗi...
ROLLBACK TRANSACTION;

Q3: Sự khác biệt giữa COMMIT và ROLLBACK là gì?

A:

• COMMIT: Xác nhận tất cả thay đổi trong transaction, ghi vĩnh viễn vào database.
• ROLLBACK: Hủy bỏ tất cả thay đổi trong transaction, đưa dữ liệu về trạng thái trước khi bắt đầu transaction.

Q4: Isolation Level mặc định của SQL Server là gì?

A: READ COMMITTED — ngăn chặn Dirty Read nhưng vẫn cho phép Non-repeatable Read và Phantom Read. Đây là mức cân bằng tốt giữa tính nhất quán và hiệu suất.

Q5: Dirty Read là gì? Cho ví dụ.

A: Dirty Read xảy ra khi một transaction đọc dữ liệu mà transaction khác đang sửa đổi nhưng chưa COMMIT. Nếu transaction kia sau đó ROLLBACK, dữ liệu đã đọc là không hợp lệ.

-- Session 1: Cập nhật nhưng chưa commit
BEGIN TRANSACTION;
UPDATE Products SET Price = 999 WHERE ProductId = 1;
-- Chưa COMMIT

-- Session 2 (READ UNCOMMITTED): Đọc được giá 999 - là Dirty Read
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT Price FROM Products WHERE ProductId = 1; -- Trả về 999

-- Session 1: Rollback
ROLLBACK; -- Giá thực tế vẫn là giá cũ

Q6: Blocking trong SQL Server là gì?

A: Blocking xảy ra khi một transaction đang giữ lock trên resource, khiến transaction khác phải chờ. Ví dụ: Session A đang UPDATE mà chưa COMMIT, Session B muốn SELECT cùng row đó sẽ bị block.

Q7: Làm thế nào để xem các blocking session hiện tại?

A:

-- Cách nhanh nhất
EXEC sp_who2;

-- Chi tiết hơn
SELECT 
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.status,
    t.text AS QueryText
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id > 0;

Q8: Deadlock là gì?

A: Deadlock xảy ra khi hai hoặc nhiều transaction đều đang chờ nhau giải phóng lock theo dạng vòng tròn (circular dependency). SQL Server tự động phát hiện deadlock, chọn một transaction làm deadlock victim và ROLLBACK nó, giải phóng deadlock.

Q9: NOLOCK hint là gì và có rủi ro gì không?

A: WITH (NOLOCK) tương đương với READ UNCOMMITTED — cho phép đọc dữ liệu chưa commit. Rủi ro nghiêm trọng:

• Đọc được dữ liệu sẽ bị ROLLBACK (Dirty Read)
• Có thể bỏ sót rows (skip rows)
• Có thể đọc trùng rows (duplicate rows)
• Không nên dùng trong hệ thống tài chính, báo cáo cần chính xác.

Q10: @@TRANCOUNT là gì?

A: @@TRANCOUNT trả về số lượng transaction đang mở trong session hiện tại. Mỗi BEGIN TRANSACTION tăng giá trị lên 1, mỗi COMMIT giảm 1, chỉ ROLLBACK mới đặt về 0.

🟡 Mid Level

Q11: Phantom Read là gì? Isolation level nào ngăn chặn nó?

A: Phantom Read xảy ra khi một transaction thực hiện cùng một query hai lần nhưng nhận được kết quả khác nhau vì transaction khác đã INSERT/DELETE rows phù hợp với điều kiện query giữa hai lần đọc.

Chỉ SERIALIZABLE isolation level mới ngăn chặn Phantom Read hoàn toàn (dùng key-range locks). SNAPSHOT ISOLATION cũng ngăn phantom read nhờ row versioning.

Q12: READ COMMITTED SNAPSHOT ISOLATION (RCSI) khác READ COMMITTED như thế nào?

A:

RCSI cho writers và readers không block nhau — tốt hơn cho concurrency cao. Bật bằng: ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON.

Q13: Giải thích Lock Escalation và khi nào nó xảy ra.

A: SQL Server bắt đầu với row-level locks. Khi số locks trong một statement vượt ngưỡng (~5000 locks), SQL Server escalate (nâng cấp) thành table lock để tiết kiệm bộ nhớ. Điều này giảm overhead quản lý lock nhưng giảm concurrency vì table bị lock toàn bộ.

Để hạn chế lock escalation:

-- Tắt lock escalation cho table cụ thể
ALTER TABLE dbo.BigTable SET (LOCK_ESCALATION = DISABLE);

-- Dùng ROWLOCK hint trong query
SELECT * FROM BigTable WITH (ROWLOCK) WHERE Id = 1;

Q14: TRY-CATCH với Transaction hoạt động như thế nào?

A:

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
    UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- Re-throw error
    THROW;
END CATCH;

Lưu ý: Luôn kiểm tra @@TRANCOUNT > 0 trước khi ROLLBACK để tránh lỗi khi transaction đã bị rollback do XACT_ABORT.

Q15: XACT_ABORT là gì và khi nào nên dùng?

A: SET XACT_ABORT ON khiến SQL Server tự động ROLLBACK toàn bộ transaction khi có bất kỳ lỗi runtime nào xảy ra (thay vì chỉ fail statement đó). Đây là best practice trong stored procedures.

SET XACT_ABORT ON;
BEGIN TRANSACTION;
    -- Nếu bất kỳ statement nào fail, toàn bộ transaction sẽ rollback tự động
    INSERT INTO Orders ...;
    INSERT INTO OrderDetails ...;
COMMIT TRANSACTION;

Q16: Savepoints là gì và dùng khi nào?

A: Savepoints cho phép ROLLBACK một phần của transaction về đến điểm lưu, không phải toàn bộ transaction.

BEGIN TRANSACTION;
    INSERT INTO Log VALUES ('Step 1');
    SAVE TRANSACTION step1; -- Đặt savepoint

    INSERT INTO Orders VALUES (...);
    -- Nếu có lỗi, rollback về step1, không phải về đầu transaction
    ROLLBACK TRANSACTION step1;

    INSERT INTO Log VALUES ('Step 1 still committed');
COMMIT TRANSACTION;

Q17: Làm thế nào để phát hiện và xử lý Deadlock?

A:

Phát hiện:

-- Bật trace flag để log deadlock vào SQL Server Error Log
DBCC TRACEON(1222, -1); -- Detailed deadlock info
DBCC TRACEON(1204, -1); -- Basic deadlock info

-- Hoặc dùng Extended Events (recommended)
-- Event: xml_deadlock_report

Xử lý:

-- Retry pattern trong application
-- Trong SQL: Bắt error 1205 (Deadlock victim)
BEGIN TRY
    -- Transaction code
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205 -- Deadlock victim
    BEGIN
        PRINT 'Deadlock detected, retry...';
        -- Application logic để retry
    END
    ELSE THROW;
END CATCH;

Q18: Sự khác biệt giữa Shared Lock (S) và Exclusive Lock (X)?

A:

• Shared Lock (S): Dùng cho READ. Nhiều shared locks có thể tồn tại cùng lúc trên cùng resource. Không cho phép Exclusive lock.
• Exclusive Lock (X): Dùng cho WRITE (INSERT, UPDATE, DELETE). Chỉ một exclusive lock được phép tại một thời điểm. Không compatible với bất kỳ lock nào khác.

Q19: Update Lock (U Lock) là gì? Tại sao cần thiết?

A: Update Lock (U) là bước trung gian khi SQL Server chuẩn bị UPDATE:

1. Đầu tiên acquire U lock để đọc row (scan)
2. Sau đó convert sang X lock khi thực sự ghi

Lý do cần thiết: Ngăn chặn deadlock trong trường hợp hai session cùng đọc rồi cùng update. Nếu dùng S lock rồi convert X lock, hai session có thể deadlock. U lock chỉ tương thích với S lock (không phải U), nên chỉ một session có thể giữ U lock tại một thời điểm.

Q20: Non-repeatable Read là gì? Khác Phantom Read như thế nào?

A:

• Non-repeatable Read: Cùng một row được đọc hai lần trong transaction cho kết quả khác nhau (row bị UPDATE/DELETE bởi transaction khác giữa hai lần đọc).
• Phantom Read: Cùng một query (với WHERE condition) trả về số lượng rows khác nhau (rows mới bị INSERT bởi transaction khác).

🔴 Senior Level

Q21: Giải thích Write-Ahead Logging (WAL) và vai trò của nó trong Durability.

A: WAL là nguyên tắc cốt lõi đảm bảo Durability:

1. Trước khi ghi data page vào disk, SQL Server phải ghi log record tương ứng vào Transaction Log trước.
2. Khi COMMIT, SQL Server chỉ cần đảm bảo log records được flush to disk (không cần data pages).
3. Nếu hệ thống crash, SQL Server dùng transaction log để REDO (áp lại các committed transactions) và UNDO (rollback các uncommitted transactions) trong quá trình recovery.

Checkpoint process: SQL Server định kỳ thực hiện checkpoint để flush dirty data pages từ buffer pool xuống disk, giảm thời gian recovery.

Q22: Tại sao Long-running Transactions lại nguy hiểm?

A:

1. Transaction Log không thể reuse: Log không thể truncate nếu transaction vẫn đang mở, gây log file phình to.
2. Blocking: Giữ locks lâu dài, blocking các session khác.
3. Version Store bloat (nếu dùng RCSI/SI): Row versions phải giữ trong tempdb cho đến khi transaction kết thúc.
4. Rollback time: Transaction chạy 2 giờ có thể mất 2 giờ để rollback.

Giải pháp: Chia nhỏ batch, dùng WAITFOR DELAY ‘00:00:00’ để nhường CPU, monitor với sys.dm_exec_requests.

Q23: Distributed Transaction và MSDTC hoạt động như thế nào?

A: Distributed Transaction span qua nhiều resource managers (nhiều SQL Server instances, hoặc SQL Server + Message Queue). Microsoft Distributed Transaction Coordinator (MSDTC) điều phối 2-Phase Commit:

1. Phase 1 - Prepare: MSDTC yêu cầu tất cả participants chuẩn bị commit và báo cáo trạng thái.
2. Phase 2 - Commit/Rollback: Nếu tất cả sẵn sàng, MSDTC ra lệnh commit. Nếu bất kỳ participant nào fail, toàn bộ rollback.

-- Linked server distributed transaction
BEGIN DISTRIBUTED TRANSACTION;
    UPDATE LocalDB.dbo.Table1 SET Col = 1;
    UPDATE [RemoteServer].RemoteDB.dbo.Table2 SET Col = 2;
COMMIT;

Nhược điểm: Latency cao, MSDTC là single point of failure. Modern architecture thường dùng Saga pattern thay thế.

Q24: Giải thích SNAPSHOT ISOLATION và sự khác biệt với RCSI.

A:

SNAPSHOT ISOLATION hay phát hiện update conflicts: Nếu row đã bị modify bởi transaction khác kể từ khi snapshot được tạo, transaction hiện tại sẽ fail với lỗi.

Q25: Làm thế nào để tune performance khi có nhiều Blocking?

A:

1. Tìm blocking chain:

SELECT 
    blocking.session_id AS blocker_session,
    blocked.session_id AS blocked_session,
    blocked.wait_time / 1000 AS wait_seconds,
    blocked.wait_type,
    SUBSTRING(sqltext.text, (blocked.statement_start_offset/2)+1, 
        ((CASE blocked.statement_end_offset WHEN -1 THEN DATALENGTH(sqltext.text)
          ELSE blocked.statement_end_offset END - blocked.statement_start_offset)/2)+1) AS blocked_query
FROM sys.dm_exec_requests blocked
INNER JOIN sys.dm_exec_sessions blocking 
    ON blocked.blocking_session_id = blocking.session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) sqltext
WHERE blocked.blocking_session_id > 0;

2. Giải pháp:
   • Bật RCSI để readers không block writers
   • Tối ưu index để giảm thời gian lock
   • Rút ngắn transaction
   • Đúng isolation level cho use case
   • SET LOCK_TIMEOUT để fail fast thay vì chờ mãi

Q26: Chiến lược ngăn chặn Deadlock là gì?

A:

1. Access objects theo cùng thứ tự: Nếu T1 và T2 đều lock Table A rồi Table B theo cùng thứ tự, deadlock không xảy ra.
2. Giữ transaction ngắn: Giảm thời gian giữ locks.
3. Thêm đúng indexes: Giảm số rows phải lock, tránh table scans.
4. Dùng UPDLOCK hint: Lấy U lock từ đầu thay vì S -> X conversion.
5. Dùng RCSI/SNAPSHOT: Readers không lấy S locks, loại bỏ Read-Write deadlocks.
6. Retry logic: Viết application code bắt error 1205 và retry.

Q27: Giải thích Version Store trong tempdb và vấn đề tiềm ẩn.

A: Khi RCSI hoặc SNAPSHOT ISOLATION được bật, mỗi khi row được UPDATE, SQL Server ghi phiên bản cũ của row vào Version Store trong tempdb. Long-running transactions giữ versions này cho đến khi kết thúc, khiến tempdb phình to.

Monitor Version Store:

SELECT 
    SUM(version_store_reserved_page_count) * 8 / 1024 AS version_store_MB
FROM sys.dm_db_file_space_usage;

-- Xem oldest active transaction
SELECT 
    transaction_id,
    transaction_begin_time,
    DATEDIFF(MINUTE, transaction_begin_time, GETDATE()) AS minutes_open
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY transaction_begin_time;

Giải pháp: Kết thúc long-running transactions, monitor tempdb growth, ensure tempdb autogrowth được cấu hình đúng.

Q28: Tại sao nên dùng sp_executesql thay vì EXEC với dynamic SQL trong context bảo mật transaction?

A:

-- BAD: Dễ bị SQL Injection
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Users WHERE Name = ''' + @input + '''';
EXEC(@sql);

-- GOOD: Parameterized, safe against SQL Injection
DECLARE @sql NVARCHAR(500) = 'SELECT * FROM Users WHERE Name = @name';
EXEC sp_executesql @sql, N'@name NVARCHAR(100)', @name = @input;

sp_executesql cũng giúp plan caching tốt hơn và an toàn trong nested transactions vì không tạo scope mới cho @@TRANCOUNT.

Q29: Giải thích cơ chế chọn Deadlock Victim.

A: SQL Server dùng thuật toán để chọn transaction nào sẽ bị kill (deadlock victim) dựa trên:

1. DEADLOCK_PRIORITY: Transaction có priority thấp hơn bị chọn trước.

SET DEADLOCK_PRIORITY LOW; -- Tự nguyện làm victim
SET DEADLOCK_PRIORITY HIGH; -- Ít có khả năng làm victim
SET DEADLOCK_PRIORITY NORMAL; -- Default

2. Transaction cost: Nếu priority bằng nhau, transaction nào tốn ít cost hơn để rollback (log records ít hơn) sẽ bị chọn.

3. DEADLOCK_PRIORITY nhận giá trị từ -10 (LOWEST) đến 10 (HIGHEST).

Q30: Thiết kế retry mechanism cho Deadlock ở application level như thế nào?

A:

// C# example với Polly hoặc custom retry
public async Task ExecuteWithDeadlockRetry(Func<Task> action, int maxRetries = 3)
{
    int attempt = 0;
    while (true)
    {
        try
        {
            await action();
            return;
        }
        catch (SqlException ex) when (ex.Number == 1205) // Deadlock victim
        {
            attempt++;
            if (attempt >= maxRetries) throw;
            
            // Exponential backoff với jitter
            int delay = (int)(Math.Pow(2, attempt) * 100 + Random.Shared.Next(0, 100));
            await Task.Delay(delay);
        }
    }
}

Nguyên tắc: Retry deadlock là hợp lệ vì deadlock là transient error. Nhưng không retry lỗi business logic hay constraint violation.