28. Transactions & Concurrency Control -- Data Integrity

Level: Advanced SQL Server for real projects

ℹ️ What You'll Learn

• Transaction syntax (BEGIN, COMMIT, ROLLBACK)
• ACID properties (Atomicity, Consistency, Isolation, Durability)
• Isolation levels (READ UNCOMMITTED, READ COMMITTED, etc.)
• Locks and blocking
• Deadlocks and prevention
• Dirty reads, phantom reads, lost updates
• Transaction best practices
• Common mistakes

One operation succeeds or fails entirely. Transactions ensure consistency. This article teaches data integrity through transactions.

Why Transactions Matter for Backend Developers

Data integrity requires all-or-nothing operations:

Fee payment transaction:
1. Deduct from StudentBalance
2. Add to FeeCollection
3. Update FeeAccount status

If step 2 fails after step 1: Inconsistent state (money lost)
    v
Transaction: All 3 steps succeed together, or none
    v
Data integrity: Always consistent

Transactions prevent partial updates.

Transaction Syntax

BEGIN TRANSACTION;
    -- SQL statements
    IF ERROR_CONDITION
        ROLLBACK TRANSACTION;
    ELSE
        COMMIT TRANSACTION;

States:

• BEGIN: Start transaction
• COMMIT: Save all changes
• ROLLBACK: Undo all changes

ACID Properties

Atomicity: All-or-nothing (entire transaction or none). Consistency: Data valid before and after. Isolation: Transaction doesn't affect others. Durability: Committed data survives crashes.

Example 1: Fee Payment Transaction

Process fee payment with balance check:

BEGIN TRANSACTION;
BEGIN TRY
    -- Check balance
    IF (SELECT SUM(DueAmount) FROM FeeAccount WHERE StudentId = @StudentId) < @PaymentAmount
    BEGIN
        THROW 50001, 'Insufficient fee due', 1;
    END;
    
    -- Deduct from account
    UPDATE FeeAccount 
    SET DueAmount = DueAmount - @PaymentAmount
    WHERE StudentId = @StudentId;
    
    -- Record payment
    INSERT INTO FeePayment (StudentId, Amount, PaidOn, PaymentMode)
    VALUES (@StudentId, @PaymentAmount, GETDATE(), @PaymentMode);
    
    -- Commit (all changes saved)
    COMMIT TRANSACTION;
    SELECT 'Payment successful' AS Result;
END TRY
BEGIN CATCH
    -- Error: Rollback (undo all changes)
    ROLLBACK TRANSACTION;
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Flow:

1. Check balance
2. Deduct (if check passes)
3. Record payment (if deduct succeeds)
4. COMMIT or ROLLBACK (all-or-nothing)

Example 2: Exam Result Insert with Duplicate Check

Prevent duplicate exam entries:

BEGIN TRANSACTION;
BEGIN TRY
    -- Check duplicate
    IF EXISTS (
        SELECT 1 FROM ExamResult 
        WHERE StudentId = @StudentId AND ExamId = @ExamId
    )
    BEGIN
        THROW 50002, 'Exam result already exists', 1;
    END;
    
    -- Insert
    INSERT INTO ExamResult (StudentId, ExamId, MarksObtained, IsAbsent)
    VALUES (@StudentId, @ExamId, @MarksObtained, @IsAbsent);
    
    COMMIT TRANSACTION;
    SELECT 'Result recorded' AS Result;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT ERROR_MESSAGE() AS ErrorMessage;
END CATCH

Transaction ensures: Either inserted or rolled back (no partial state).

Example 3: Batch Updates (Multiple Students)

Update multiple student records:

BEGIN TRANSACTION;
BEGIN TRY
    -- Promote all class 10-A students to next class
    UPDATE Student
    SET ClassName = '11-A'
    WHERE ClassName = '10-A' AND Status = 'Active';
    
    -- Record in audit log
    INSERT INTO AuditLog (Action, EntityName, ChangedAt, ChangedBy)
    VALUES ('PromotedClass', 'Student', GETDATE(), @UserId);
    
    COMMIT TRANSACTION;
    SELECT 'All students promoted' AS Result;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT 'Promotion failed. No changes made.' AS Result;
END CATCH

Multiple rows updated atomically. If any step fails, all rollback.

Isolation Levels

READ UNCOMMITTED: Reads uncommitted data (dirty reads).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

READ COMMITTED (default): Reads committed data only (prevents dirty reads).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ: Prevents dirty and non-repeatable reads.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

SERIALIZABLE: Strictest, prevents all anomalies (slowest).

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Default (READ COMMITTED): Balance between safety and performance.

Example 4: Transaction with Isolation Level

Account transfer with isolation:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRANSACTION;
BEGIN TRY
    DECLARE @BalanceBeforeTransfer DECIMAL(10,2);
    
    -- Read current balance
    SELECT @BalanceBeforeTransfer = Balance 
    FROM StudentAccount 
    WHERE StudentId = @StudentId;
    
    -- Check sufficient balance
    IF @BalanceBeforeTransfer < @TransferAmount
    BEGIN
        THROW 50003, 'Insufficient balance', 1;
    END;
    
    -- Deduct
    UPDATE StudentAccount 
    SET Balance = Balance - @TransferAmount 
    WHERE StudentId = @StudentId;
    
    -- Add to other account
    UPDATE StudentAccount 
    SET Balance = Balance + @TransferAmount 
    WHERE StudentId = @OtherStudentId;
    
    COMMIT TRANSACTION;
    SELECT 'Transfer complete' AS Result;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT 'Transfer failed' AS Result;
END CATCH

READ COMMITTED: Prevents reading uncommitted changes from other transactions.

Example 5: Deadlock Prevention

Nested transactions with consistent lock order:

-- Always lock in same order: StudentId first, then FeeAccountId
BEGIN TRANSACTION;
BEGIN TRY
    -- Lock student first
    SELECT * FROM Student WITH (XLOCK)  -- Exclusive lock
    WHERE StudentId = @StudentId;
    
    -- Then fee account
    SELECT * FROM FeeAccount WITH (XLOCK)
    WHERE StudentId = @StudentId;
    
    -- Update both
    UPDATE Student SET ... WHERE StudentId = @StudentId;
    UPDATE FeeAccount SET ... WHERE StudentId = @StudentId;
    
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT 'Deadlock prevention: Rolled back' AS Result;
END CATCH

Lock order: Always StudentId -> FeeAccount (consistent across code). Prevents circular waits.

---

Common Transaction Mistakes

Mistake 1: Not using transaction (partial updates)

Wrong:

UPDATE Student SET ClassName = '11-A' WHERE ClassName = '10-A';
INSERT INTO AuditLog (...) VALUES (...);
-- If INSERT fails after UPDATE: Inconsistent (no rollback)

Result: Student promoted, but audit log missing.

Fix: Use transaction:

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE Student SET ...;
    INSERT INTO AuditLog ...;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
END CATCH

Mistake 2: Long transactions holding locks

Wrong:

BEGIN TRANSACTION;
    -- Read data
    SELECT * FROM ExamResult WHERE StudentId = @StudentId;
    
    -- Long processing (1 minute)
    WAITFOR DELAY '00:01:00';
    
    -- Update
    UPDATE ExamResult SET ... WHERE StudentId = @StudentId;
COMMIT;
-- Locks held for 1 minute: Blocks other queries

Result: Other queries wait, blocking cascade.

Fix: Minimize transaction scope:

-- Process outside transaction
SELECT * INTO @LocalTable FROM ExamResult WHERE StudentId = @StudentId;
WAITFOR DELAY '00:01:00';  -- No lock held

-- Brief transaction
BEGIN TRANSACTION;
    UPDATE ExamResult SET ... WHERE StudentId = @StudentId;
COMMIT;

Mistake 3: No error handling (uncommitted changes)

Wrong:

BEGIN TRANSACTION;
    UPDATE Student SET ...;
    UPDATE FeeAccount SET ...;  -- Might fail
-- No ROLLBACK: Changes stay uncommitted in transaction (locks held)

Result: Deadlock, other queries blocked.

Fix: Always include ROLLBACK:

BEGIN TRANSACTION;
BEGIN TRY
    UPDATE ...;
    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;  -- Always rollback on error
    SELECT ERROR_MESSAGE();
END CATCH

Mistake 4: Isolation too strict (performance impact)

Wrong:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Strictest isolation: Serializes all transactions
-- 1000 concurrent users = huge queues

Result: Terrible performance.

Fix: Use READ COMMITTED (default):

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Balance: Safe from dirty reads, but allows concurrent updates

Mistake 5: Ignoring deadlock detection

Wrong:

-- No retry logic
BEGIN TRANSACTION;
    UPDATE Account1 SET Balance = ...;
    UPDATE Account2 SET Balance = ...;  -- Might deadlock
COMMIT;
-- Deadlock happens: Transaction fails, user gets error

Fix: Retry on deadlock:

DECLARE @Retries INT = 0;
WHILE @Retries < 3
BEGIN
    BEGIN TRY
        BEGIN TRANSACTION;
            UPDATE Account1 ...;
            UPDATE Account2 ...;
        COMMIT;
        BREAK;  -- Success
    END TRY
    BEGIN CATCH
        ROLLBACK;
        IF ERROR_NUMBER() = 1205  -- Deadlock
            SET @Retries = @Retries + 1
        ELSE
            THROW;
    END CATCH
END

---

Best Practices for Transactions

1. Keep transactions short -- Minimize lock duration
2. Use BEGIN-TRY-CATCH-END -- Always rollback on error
3. Process outside transaction -- Long operations before/after
4. Default isolation (READ COMMITTED) -- Usually sufficient
5. Consistent lock order -- Prevent deadlocks
6. Retry on deadlock -- Transient errors happen
7. Index foreign keys -- Reduce lock time
8. Test with concurrency -- Simulate multiple users

---

🎯 Q1: What are ACID properties?

Atomicity: All-or-nothing (entire transaction or none). Consistency: Data valid before and after. Isolation: Transactions don't affect each other. Durability: Committed data survives crashes.

Together: Guaranteed reliable transactions.

🎯 Q2: What's the difference between COMMIT and ROLLBACK?

COMMIT: Save all changes.

INSERT INTO Student ...;
COMMIT;  -- Insert saved

ROLLBACK: Undo all changes.

INSERT INTO Student ...;
ROLLBACK;  -- Insert undone

COMMIT: Permanent. ROLLBACK: Discard.

🎯 Q3: What are isolation levels?

READ UNCOMMITTED: Reads dirty (uncommitted) data. READ COMMITTED (default): Reads committed only. REPEATABLE READ: Committed data + prevents non-repeatable reads. SERIALIZABLE: Strictest (serializes transactions).

Default (READ COMMITTED) good for most cases.

Higher isolation = more safety but slower.

🎯 Q4: What's a deadlock?

Two transactions wait for each other:

Transaction A: Lock Table1, waiting for Table2
Transaction B: Lock Table2, waiting for Table1
Circular wait: Neither progresses (deadlock)

Prevention: Always lock in same order.

-- Always: Table1 first, then Table2

SQL Server detects, picks victim, rolls back.

🎯 Q5: Should I use transaction for every query?

No, only for multi-step operations:

-- No transaction needed:
SELECT * FROM Student;
INSERT INTO Student VALUES (...);

-- Transaction needed:
BEGIN TRANSACTION;
    UPDATE StudentAccount SET Balance = ...;
    INSERT INTO Transaction SET ...;
COMMIT;

Use transactions when: multiple changes must succeed together.

🎯 Q6: What does XLOCK mean?

XLOCK: Exclusive lock (no one else can read/write).

SELECT * FROM Student WITH (XLOCK) WHERE StudentId = 1;
-- Only this query can access this row

SHARED (default): Others can read, not write. EXCLUSIVE: Only this query.

Use XLOCK to prevent concurrent conflicts.

---

🤖Use AI to Learn Faster

⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on Transactions and concurrency in SQL Server. Try these prompts:

• "Show fee payment transaction: check balance, deduct, record payment"
• "What are ACID properties? Why do they matter?"
• "Explain isolation levels: READ UNCOMMITTED vs READ COMMITTED"
• "How do deadlocks happen? How do I prevent them?"
• "Quiz me: ask 5 questions about transactions, ACID, isolation levels"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

29. Triggers & Audit Logging -- Automated Actions ->