Basic Transactions

Beginner

10 minutes

12 minutes

10 minutes

INSERT, UPDATE, DELETE Statements

Understand what transactions are and why they matter

Master BEGIN, COMMIT, and ROLLBACK commands

Use transactions to ensure data consistency

Understand ACID properties (Atomicity, Consistency, Isolation, Durability)

Prevent data loss with transaction safety

Handle errors with ROLLBACK

A bank transfer moves $100 from Account A to Account B. This requires two operations: (1) Subtract $100 from A, (2) Add $100 to B. If operation 1 succeeds but 2 fails, money disappears! Transactions ensure both operations succeed together or both fail - no partial updates.

Transactions are fundamental to data integrity. Every financial system, e-commerce platform, and critical application uses transactions. Understanding transactions prevents data corruption, ensures consistency, and enables safe error handling. Essential for backend developers and DBAs.

A transaction is a group of SQL statements that execute as a single unit. Either all statements succeed (COMMIT) or all fail (ROLLBACK). Transactions ensure data consistency and prevent partial updates.

**Transaction Basics:**

- BEGIN: Start a transaction

- COMMIT: Save all changes permanently

- ROLLBACK: Undo all changes since BEGIN

**ACID Properties:**

- **Atomicity**: All or nothing (no partial updates)

- **Consistency**: Data remains valid (constraints enforced)

- **Isolation**: Transactions don't interfere with each other

- **Durability**: Committed changes are permanent

**Why Use Transactions:**

1. **Data Integrity**: Ensure related changes happen together

2. **Error Recovery**: Rollback on failure

3. **Consistency**: Maintain valid state

4. **Safety**: Test changes before committing

**Transaction Flow:**

1. BEGIN TRANSACTION

2. Execute SQL statements (INSERT, UPDATE, DELETE)

3. If all succeed: COMMIT

4. If any fail: ROLLBACK

**Auto-commit Mode:**

- By default, each statement is a transaction

- Explicit transactions override auto-commit

- Use BEGIN to start multi-statement transaction

BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;