SQL-Transaction

A transaction in SQL Server is a logical unit of work that contains one or more SQL operations (such as INSERT, UPDATE, DELETE, or SELECT) that should be executed as a single unit. A transaction ensures that either all operations are successful or none are applied, maintaining the consistency of the database.

Why Use Transactions?

Transactions are used to ensure the ACID properties:-

• Atomicity - Either all operations are performed, or none are.
• Consistency - The database remains in a valid state.
• Isolation - Operations in one transaction are not visible to others until committed.
• Durability - Once committed, changes are permanent.

Without transactions, individual queries might leave the database in an inconsistent state if one part of the operation fails.

Example Without Using Transactions (Potential Problem)

Let’s say you have a bank account transfer operation where you want to transfer money from Account A to Account B. Here’s an example of how you might attempt it without a transaction:

-- Step 1: Debit from Account A
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Step 2: Credit to Account B
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

Potential issues with above Query

• If the update to Account A succeeds, but the update to Account B fails (e.g., due to a system crash or network issue), the money would be deducted from Account A but never credited to Account B. This leads to an inconsistent state where the total money in the system has decreased by $100, but it is not reflected in both accounts.
• There is no way to rollback the debit action once the credit operation fails.

Example Using Transactions (Ensures Consistency)

Now let’s perform the same operation, but with transactions to ensure that both updates succeed together or fail together.

BEGIN TRANSACTION;

-- Step 1: Debit from Account A
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Step 2: Credit to Account B
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

-- If both steps are successful, commit the transaction
COMMIT TRANSACTION;

What happens with the transaction:

• BEGIN TRANSACTION starts a new transaction.
• COMMIT TRANSACTION commits the changes to the database if both operations are successful.
• If any error occurs (for example, the second update fails), you can ROLLBACK TRANSACTION, which undoes all changes made within the transaction. The debit from Account A will also be rolled back, leaving both accounts unchanged.

In case of failure, we can handle it like this:

BEGIN TRY
    BEGIN TRANSACTION;

    -- Step 1: Debit from Account A
    UPDATE Accounts
    SET Balance = Balance - 100
    WHERE AccountID = 1;

    -- Step 2: Credit to Account B
    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE AccountID = 2;

    -- If both steps are successful, commit the transaction
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- If an error occurs, rollback the transaction
    ROLLBACK TRANSACTION;
    -- You can also capture the error here and log it
    PRINT ERROR_MESSAGE();
END CATCH;