What are SQL transactions? | SQL Interview Questions

What You'll Learn

What transactions are
ACID properties
Transaction commands

What is a Transaction?

A transaction is a sequence of operations performed as a single logical unit.

ACID Properties

Atomicity: All or nothing
Consistency: Valid state before and after
Isolation: Transactions don't interfere
Durability: Committed changes persist

Transaction Commands

query.sqlSQL

-- Start transaction
BEGIN TRANSACTION;  -- or START TRANSACTION;

-- Perform operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit if successful
COMMIT;

-- Or rollback if error
ROLLBACK;

Example: Bank Transfer

query.sqlSQL

BEGIN TRANSACTION;

-- Deduct from sender
UPDATE accounts SET balance = balance - 500
WHERE id = 1 AND balance >= 500;

-- Check if deduction succeeded
IF @@ROWCOUNT = 0
BEGIN
  ROLLBACK;
  RAISERROR('Insufficient funds', 16, 1);
  RETURN;
END

-- Add to receiver
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Savepoints

query.sqlSQL

BEGIN TRANSACTION;

INSERT INTO orders (user_id, total) VALUES (1, 100);
SAVEPOINT order_created;

INSERT INTO order_items (order_id, product_id) VALUES (1, 5);
-- If this fails:
ROLLBACK TO SAVEPOINT order_created;
-- Order still exists, only items rolled back

COMMIT;

Isolation Levels

query.sqlSQL

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE