Data Integrity (HL)

What Are SQL Transactions?

A transaction in SQL is a sequence of operations that are treated as a single unit. This sequence is called a Transaction Boundary.

Transactions ensure data integrity and consistency by using the ACID principles.

The ACID Properties of Transactions

Property	Description
Atomicity	The transaction is all or nothing; if one part fails, the entire transaction is `ROLLBACK`ed.
Consistency	The database remains in a valid state before and after the transaction.
Isolation	Concurrent transactions do not interfere with each other.
Durability	Once a transaction is `COMMIT`ted, it remains permanently in the database.

Transaction Control Language (TCL) Commands

TCL commands manage transactions in SQL, ensuring database consistency and integrity during multiple operations.

Begin Transaction

Marks the start of a transaction. All subsequent operations are executed as a single unit.

        BEGIN TRANSACTION;
    

Commit

Saves all changes made during the transaction (after BEGIN) permanently to the database.

        COMMIT;
    

Rollback

Undoes all changes made during the transaction if an error occurs or conditions are not met.

        ROLLBACK;
    

Example: Bank Transfer

Scenario:

Transfer 0 from Account 101 to Account 202.

        BEGIN TRANSACTION;

        UPDATE Accounts 
    SET Balance = Balance - 500 
    WHERE AccountID = 101;

        UPDATE Accounts 
    SET Balance = Balance + 500 
    WHERE AccountID = 202;

        COMMIT;

If an Error Occurs:

        BEGIN TRANSACTION;

        UPDATE Accounts 
    SET Balance = Balance - 500 
    WHERE AccountID = 101;

        -- Simulated error / failure

        ROLLBACK;

Example: Inventory Update with Error Check

Scenario:

Update stock levels and sales count for a product. If stock drops below zero, rollback the entire transaction.

        BEGIN TRANSACTION;

        UPDATE Products 
    SET Stock = Stock - 3, Sales = Sales + 3 
    WHERE ProductID = 501;


        IF (SELECT Stock FROM Products WHERE ProductID = 501) < 0 

          BEGIN
    ROLLBACK;
  END

        ELSE
  COMMIT;
    

Why Are Transactions Important?

Ensures Data Integrity: Prevents incomplete operations from affecting the database.
Prevents Data Loss: Transactions ensure that data remains accurate and reliable.
Handles System Failures: ROLLBACK prevents partial updates due to crashes.
Supports Concurrent Users: Ensures multiple users do not interfere with each other’s transactions.

Key Takeaways

SQL transactions ensure that database operations follow ACID principles.
TCL commands (BEGIN TRANSACTION, COMMIT, ROLLBACK) control transactions.
Atomicity ensures transactions are all or nothing, while consistency maintains database integrity.
Transactions protect against failures and ensure reliable data processing.