Transactions in SQL

Transactions in SQL

🔹 What is a Transaction?

A transaction is a sequence of one or more SQL statements executed as a single unit of work.
It ensures the ACID properties:

  • Atomicity → all or nothing
  • Consistency → maintains data integrity
  • Isolation → transactions don’t interfere with each other
  • Durability → once committed, changes are permanent

🔹 Transaction Control Commands

  1. BEGIN / START TRANSACTION → starts a new transaction.
  2. COMMIT → saves all changes made in the transaction permanently.
  3. ROLLBACK → undoes all changes made in the current transaction.

🔹 Example Scenario

Let’s say we have a bank_accounts table:

CREATE TABLE bank_accounts (
    account_id INT PRIMARY KEY,
    account_name VARCHAR(50),
    balance DECIMAL(10,2)
);

INSERT INTO bank_accounts VALUES (1, 'Alice', 1000.00);
INSERT INTO bank_accounts VALUES (2, 'Bob', 500.00);

✅ Successful Transaction (COMMIT)

Alice transfers 200 to Bob:

BEGIN;

UPDATE bank_accounts
SET balance = balance - 200
WHERE account_id = 1;   -- Alice

UPDATE bank_accounts
SET balance = balance + 200
WHERE account_id = 2;   -- Bob

COMMIT;

➡ Both updates are applied permanently.

❌ Failed Transaction (ROLLBACK)

Alice tries to transfer 2000 (more than her balance):

BEGIN;

UPDATE bank_accounts
SET balance = balance - 2000
WHERE account_id = 1;   -- Alice (now -1000, invalid)

UPDATE bank_accounts
SET balance = balance + 2000
WHERE account_id = 2;   -- Bob

ROLLBACK;

➡ Both updates are undone, balances stay unchanged.

🔹 Key Notes

  • Many databases (PostgreSQL, MySQL with InnoDB, Oracle, SQL Server) support transactions.
  • Some operations (like CREATE TABLE) auto-commit and can’t be rolled back.
  • You can also use SAVEPOINT to roll back partially within a transaction.

Leave a Reply