Skip to main content

Chapter 5: Transaction Management and Concurrency Control

5.1 ACID Properties​

ACID is an acronym that stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably.

5.1.1 Atomicity​

  • A transaction is treated as a single, indivisible unit.
  • Either all operations in the transaction are completed successfully, or none are.

Example:

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
    -- If any operation fails, the entire transaction is rolled back
COMMIT;

5.1.2 Consistency​

  • A transaction brings the database from one valid state to another.
  • All data integrity constraints must be satisfied.

Example: Ensuring that a bank account never goes negative after a transaction.

5.1.3 Isolation​

  • Concurrent execution of transactions results in a state that would be obtained if transactions were executed sequentially.
  • Each transaction must appear to execute in isolation from other transactions.

5.1.4 Durability​

  • Once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.
  • Committed data is saved by the system in a way that even if there is a failure, the data will be available in its correct state when the system is restored.

5.2 Locking Mechanisms​

Locking is a mechanism used to control concurrent access to data.

5.2.1 Types of Locks​

  1. Shared Lock (S-Lock): Used for read operations.
  2. Exclusive Lock (X-Lock): Used for write operations.

5.2.2 Lock Granularity​

  1. Table-level locks
  2. Page-level locks
  3. Row-level locks

5.2.3 Two-Phase Locking Protocol​

  1. Growing Phase: Locks are acquired and no locks are released.
  2. Shrinking Phase: Locks are released and no locks are acquired.

5.3 Deadlock Prevention and Recovery​

A deadlock occurs when two or more transactions are waiting for each other to release locks.

5.3.1 Deadlock Prevention Techniques​

  1. Timeout: Abort a transaction if it has been waiting for a lock for too long.
  2. Wait-Die Scheme: If T1 wants a lock held by T2, T1 waits if it's older than T2; otherwise, T1 dies (rolls back).
  3. Wound-Wait Scheme: If T1 wants a lock held by T2, T1 wounds (rolls back) T2 if T1 is older; otherwise, T1 waits.

5.3.2 Deadlock Detection and Recovery​

  1. Maintain a wait-for graph
  2. Periodically check for cycles in the graph
  3. If a cycle is found, choose a victim transaction to abort

Example of a wait-for graph:

Transaction 1 → Transaction 2 → Transaction 3 → Transaction 1 (Deadlock!)

5.4 Isolation Levels​

SQL defines four isolation levels, each with different degrees of strictness:

  1. Read Uncommitted
  2. Read Committed
  3. Repeatable Read
  4. Serializable

Example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    -- Transaction operations
COMMIT;

5.5 Real-life Example: Ensuring Data Integrity in a Banking System​

Let's consider a banking system where multiple transactions can occur simultaneously. We'll implement transaction management and concurrency control to ensure data integrity.

Scenario: Transferring Money Between Accounts​

-- Function to transfer money between accounts
CREATE FUNCTION TransferMoney(
    @FromAccount INT,
    @ToAccount INT,
    @Amount DECIMAL(18,2)
) RETURNS INT
AS
BEGIN
    DECLARE @ReturnCode INT = 0;
    
    BEGIN TRY
        BEGIN TRANSACTION;
        
        -- Set appropriate isolation level
        SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
        
        -- Check if the FromAccount has sufficient balance
        IF EXISTS (SELECT 1 FROM Accounts WITH (UPDLOCK, ROWLOCK)
                   WHERE AccountID = @FromAccount AND Balance >= @Amount)
        BEGIN
            -- Deduct from the FromAccount
            UPDATE Accounts
            SET Balance = Balance - @Amount
            WHERE AccountID = @FromAccount;
            
            -- Add to the ToAccount
            UPDATE Accounts
            SET Balance = Balance + @Amount
            WHERE AccountID = @ToAccount;
            
            SET @ReturnCode = 1; -- Success
        END
        ELSE
        BEGIN
            SET @ReturnCode = -1; -- Insufficient funds
        END
        
        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        
        SET @ReturnCode = -2; -- Error occurred
    END CATCH
    
    RETURN @ReturnCode;
END

This example demonstrates several important concepts:

  1. ACID Properties:

    • Atomicity: The entire transfer is treated as one unit.
    • Consistency: The total balance of both accounts remains the same.
    • Isolation: Using SERIALIZABLE isolation level prevents interference from other transactions.
    • Durability: Once committed, the transfer is permanent.

  2. Locking:

    • UPDLOCK and ROWLOCK hints are used to acquire necessary locks.

  3. Deadlock Prevention:

    • By always accessing accounts in the same order (checking FromAccount first), we reduce the chance of deadlocks.

  4. Error Handling:

    • Using TRY-CATCH blocks to handle errors and rollback the transaction if necessary.

  5. Isolation Levels:

    • Using SERIALIZABLE, the strictest isolation level, to prevent any concurrency issues.

Understanding and implementing these concepts is crucial for maintaining data integrity in high-stakes environments like banking systems. As a job seeker in the database field, being able to design and implement such robust transaction management systems will make you a valuable asset to potential employers.