Day 12: Transactions and Error Handling

Day 12: Transactions and Error Handling

1. What are Transactions?

A transaction is a sequence of SQL operations performed as a single logical unit of work. Transactions ensure data consistency and integrity.

2. ACID Properties of Transactions

1. Atomicity: All operations within the transaction succeed or none do.
2. Consistency: The database remains consistent before and after the transaction.
3. Isolation: Transactions do not interfere with each other.
4. Durability: Once committed, the transaction’s changes are permanent.

3. Transaction Control Statements

    1. BEGIN TRANSACTION: Starts a transaction.

            BEGIN TRANSACTION;

    2. COMMIT: Saves all changes made during the transaction.

            COMMIT;

    3. ROLLBACK: Undoes all changes made during the transaction.

            ROLLBACK;

    4. SAVEPOINT: Sets a point within a transaction to roll back to.

            SAVEPOINT SavePointName;

    5. RELEASE SAVEPOINT: Deletes a savepoint.

        RELEASE SAVEPOINT SavePointName;

4. Example of a Transaction

SQL

BEGIN TRANSACTION;

-- Deduct from sender's account
UPDATE Accounts
SET Balance = Balance - 1000
WHERE AccountID = 1;

-- Add to receiver's account
UPDATE Accounts
SET Balance = Balance + 1000
WHERE AccountID = 2;

-- Check for errors
IF @@ERROR <> 0
BEGIN
    ROLLBACK;
    PRINT 'Transaction Failed';
END
ELSE
BEGIN
    COMMIT;
    PRINT 'Transaction Successful';
END;

5. Error Handling

1. TRY...CATCH: Handle errors and ensure proper cleanup in case of failure.

Syntax:

SQL

BEGIN TRY
    -- SQL statements
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

2. Example with TRY...CATCH:

SQL

BEGIN TRY
    BEGIN TRANSACTION;

    -- Insert operation
    INSERT INTO Employees (Name, Salary) VALUES ('John', 5000);

    -- Error-prone operation
    INSERT INTO Employees (Name, Salary) VALUES (NULL, NULL);

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;

3. @@ERROR:

A system function that returns the error code of the last T-SQL statement.


6. Isolation Levels

Control how transactions interact with each other.

1. Read Uncommitted: Allows dirty reads.
2. Read Committed: Prevents dirty reads.
3. Repeatable Read: Prevents non-repeatable reads.
4. Serializable: Prevents dirty, non-repeatable, and phantom reads.

Syntax:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;
        -- SQL operations
    COMMIT;

Action Steps

1. Write a transaction with BEGIN TRANSACTION, COMMIT, and ROLLBACK.
2. Implement error handling using TRY...CATCH.
3. Experiment with different isolation levels in test scenarios.