What is a Transaction in SQL?

A transaction is a single unit of work that consists of one or more SQL statements, executed together as a group.

• The database treats a transaction as all or nothing:
  • All statements succeed ➔ transaction is committed (saved permanently).
  • Any statement fails ➔ transaction is rolled back (undone completely).

✅ Think of a transaction like a package deal — either the whole deal goes through, or none of it does.

Key Properties of a Transaction

(These are the ACID properties we talked about earlier.)

Property	Meaning
Atomicity	All or nothing — no partial changes.
Consistency	Database must move from one valid state to another.
Isolation	Transactions don’t interfere with each other.
Durability	Once committed, the changes are permanent.

Typical Transaction Commands

Command	What It Does
BEGIN or START TRANSACTION	Start a new transaction.
COMMIT	Save all changes made in the transaction.
ROLLBACK	Undo all changes if something goes wrong.

Example

Let’s say you’re transferring money between two accounts:

START TRANSACTION;

UPDATE Accounts SET balance = balance - 100 WHERE account_id = 1;  -- Debit
UPDATE Accounts SET balance = balance + 100 WHERE account_id = 2;  -- Credit

COMMIT;

✅ If both updates succeed: the transaction is committed — money moves.

❌ If anything fails (e.g., system crash in the middle): the transaction can ROLLBACK — no money is moved.

Why Use Transactions?

• To ensure data integrity.
• To group multiple steps into a single, safe operation.
• To recover safely if something fails.
• To avoid partial updates (e.g., one account debited but the other not credited).

Real-Life Analogy

Buying an item online:

• Deduct payment from your account.
• Create an order record.
• Reduce inventory.
• Confirm shipment.

✅ Either all of this happens, or none of it happens — no half-bought products!

In Short

A transaction is a set of operations that must all succeed together or fail together — ensuring the database stays reliable and consistent.