SQL Transactions

A Transaction is a sequence of one or more SQL operations treated as a single unit of work. Transactions are essential for ensuring data integrity, particularly when multiple related changes must succeed or fail together (adhering to the ACID properties).

Database Preparation

Run this block to set up the Accounts table.

DROP TABLE IF EXISTS Accounts;

CREATE TABLE Accounts (
    AccountID INTEGER PRIMARY KEY,
    AccountName VARCHAR(50),
    Balance DECIMAL(10, 2)
);

INSERT INTO Accounts (AccountID, AccountName, Balance)
VALUES (1, 'Alice', 500.00),
       (2, 'Bob', 200.00);

SELECT * FROM Accounts;

Transaction Control Commands

Using COMMIT (Successful Scenario)

BEGIN TRANSACTION;

-- Step 1: Debit Alice
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;

-- Step 2: Credit Bob
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;

-- If we reached here without errors, save everything
COMMIT;

-- Check the result
SELECT * FROM Accounts;

Using ROLLBACK (Error Scenario)

If an error occurs after Step 1 but before Step 2, we issue a ROLLBACK.

BEGIN TRANSACTION;

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

-- Imagine an error happens here!
-- The system or application logic detects it.

ROLLBACK;
-- Alice's balance is restored to its original value.

-- Check the result
SELECT * FROM Accounts;

Auto-Commit Mode

By default, many databases operate in Auto-Commit mode. This means every individual SQL statement is treated as its own mini-transaction and is saved immediately. When you explicitly use BEGIN, you turn off auto-commit for that block of code, giving you control over when the data is actually saved.

Hands-on Exercise

Task: Start a transaction, update Alice's balance to 450.00 and Bob's to 250.00, then commit the changes.

Your Query:

-- Write your query here

Expected Results: