TCL (Transaction Control Language) SQL

TCL (Transaction Control Language) SQL commands manage transactions within a relational database. These commands allow you to control the behavior of transactions to ensure data integrity and consistency.

 A transaction in SQL is a sequence of one or more SQL statements that are executed as a single unit of work. Transactions ensure that either all the operations within the transaction are completed successfully or none of them are, providing consistency and reliability to the database.

Key Properties of Transactions (ACID):

1. Atomicity: All operations in a transaction are treated as a single unit, which either succeeds completely or fails completely.
2. Consistency: A transaction transforms the database from one consistent state to another, ensuring data integrity.
3. Isolation: Transactions are isolated from each other until completed, so their operations don’t interfere with each other.
4. Durability: Once a transaction is committed, the changes are permanent, even in the case of a system failure.

SQL Transaction Commands:

1. BEGIN TRANSACTION: Marks the start of a transaction.
2. COMMIT: Commits the current transaction, saving the changes permanently to the database.
3. ROLLBACK: Rolls back the current transaction, undoing any changes made in the transaction.
4. SAVEPOINT: Sets a savepoint within a transaction to which a transaction can be rolled back.

Syntax:

BEGIN TRANSACTION;

-- SQL Queries (Insert, Update, Delete) that make up the transaction.

COMMIT;  -- If all operations succeed, save the changes.

-- OR

ROLLBACK;  -- If any operation fails, undo all changes.

Example:

BEGIN TRANSACTION;

-- Balance transfer between two accounts
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;  -- Deduct amount from Account 1
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;  -- Add amount to Account 2

IF @@ERROR <> 0  -- Check if any error occurred during the transaction
BEGIN
    ROLLBACK;  -- If there's an error, undo all changes.
    PRINT 'Transaction Failed';
END
ELSE
BEGIN
    COMMIT;  -- If no error, save the changes.
    PRINT 'Transaction Successful';
END

In this example, money is transferred from one account to another. If any part of the process fails, the ROLLBACK statement can be used to undo the entire transaction.

Handling Errors in Transactions:

To ensure that errors during a transaction are properly handled, you can use TRY...CATCH blocks in SQL.

Example with Error Handling:

BEGIN TRY
    BEGIN TRANSACTION;

    -- Update operation
    UPDATE Accounts
    SET Balance = Balance - 1000
    WHERE AccountID = 1;

    -- Another update operation
    UPDATE Accounts
    SET Balance = Balance + 1000
    WHERE AccountID = 2;

    -- If everything goes well, commit the transaction
    COMMIT;
END TRY
BEGIN CATCH
    -- If an error occurs, rollback the transaction
    ROLLBACK;
    -- Optionally, raise an error to notify the application
    THROW;
END CATCH;

In this code, if an error occurs in the TRY block, the transaction is rolled back, and the CATCH block can handle the error.