What is rollback and commit?

In SQL, `ROLLBACK` and `COMMIT` are fundamental commands for managing transactions, ensuring data integrity and consistency. They are part of the Transaction Control Language (TCL) and allow database operations to be grouped into logical units that either fully succeed or completely fail.

What is a SQL Transaction?

A SQL transaction is a single logical unit of work that comprises one or more SQL statements. The primary goal of transactions is to maintain the ACID properties (Atomicity, Consistency, Isolation, Durability) in a database. Transactions begin implicitly with the first DML statement (like INSERT, UPDATE, DELETE) or explicitly with a BEGIN TRANSACTION (or similar, depending on the RDBMS).

COMMIT Command

The COMMIT command is used to permanently save the changes made during the current transaction to the database. Once a transaction is committed, all changes become visible to other users and applications, and they cannot be undone by a ROLLBACK.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

INSERT INTO Transactions (AccountID, Amount, Type)
VALUES (1, -100, 'Withdrawal');

COMMIT;

• Makes all changes made during the transaction permanent.
• Releases any locks held by the transaction.
• Ends the current transaction.
• Makes the changes visible to other transactions.

ROLLBACK Command

The ROLLBACK command is used to undo all changes made during the current transaction since the last COMMIT or BEGIN TRANSACTION. It effectively reverts the database to the state it was in before the transaction began. This is crucial for error handling, allowing you to discard partial or incorrect operations.

BEGIN TRANSACTION;

INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (101, GETDATE(), 250.00);

-- An error occurs here, or a condition is not met
-- For example, if inventory check fails

ROLLBACK;

• Undoes all uncommitted changes made during the transaction.
• Releases any locks held by the transaction.
• Ends the current transaction.
• Restores the database to its state prior to the transaction's start.

Why are they important?

COMMIT and ROLLBACK are vital for maintaining data integrity and consistency, especially in multi-user environments or complex operations. They ensure that either all parts of a transaction complete successfully, or none of them do. This prevents data corruption from incomplete operations (Atomicity) and ensures that the database remains in a valid state (Consistency).

Example: Transferring Funds

Consider a bank transfer from Account A to Account B. This involves two updates: debiting A and crediting B. If the system crashes or an error occurs after debiting A but before crediting B, ROLLBACK ensures that Account A is not permanently debited without Account B being credited. If both updates succeed, COMMIT makes the changes permanent.

BEGIN TRANSACTION;

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

-- Check for sufficient balance or other business rules
IF @@ROWCOUNT = 0 OR (SELECT Balance FROM Accounts WHERE AccountID = 1) < 0
BEGIN
    ROLLBACK; -- Undo the debit if A's balance insufficient or update failed
    PRINT 'Transaction failed: Insufficient funds or invalid account.';
END
ELSE
BEGIN
    UPDATE Accounts
    SET Balance = Balance + 100
    WHERE AccountID = 2; -- Credit Account B

    IF @@ERROR <> 0 OR @@ROWCOUNT = 0
    BEGIN
        ROLLBACK; -- Undo both if crediting B failed
        PRINT 'Transaction failed: Error crediting recipient account.';
    END
    ELSE
    BEGIN
        COMMIT; -- Both operations successful, make permanent
        PRINT 'Transaction successful.';
    END
END;