Transactions

In real-world applications, database operations rarely happen one statement at a time.

A transaction allows multiple SQL operations to be executed as a single logical unit, ensuring data remains safe and consistent.

What is a Transaction?

A transaction is a sequence of one or more SQL statements that are executed together.

All statements inside a transaction must:

• Either succeed together
• Or fail together

This prevents partial updates and data corruption.

Why Transactions Matter

Transactions protect databases from:

• System crashes
• Application errors
• Network failures
• Incomplete operations

They are essential in banking, e-commerce, and enterprise systems.

Real-World Example

Consider transferring money between two bank accounts:

• Deduct money from Account A
• Add money to Account B

If one step fails, the entire operation must be reversed. This is exactly what transactions handle.

Transaction Control Statements

Basic Transaction Example

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 2;

COMMIT;
  

If all statements succeed, changes are saved.

ROLLBACK Example

If something goes wrong, rollback cancels the transaction.

START TRANSACTION;

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 1;

-- Error occurs here

ROLLBACK;
  

The database returns to its previous state.

Using SAVEPOINT

SAVEPOINT allows partial rollback within a transaction.

START TRANSACTION;

INSERT INTO orders VALUES (1, 'Laptop');
SAVEPOINT sp1;

INSERT INTO orders VALUES (2, 'Phone');
ROLLBACK TO sp1;

COMMIT;
  

Only the first insert is committed.

ACID Properties

Transactions follow the ACID principles:

• Atomicity – All or nothing
• Consistency – Data remains valid
• Isolation – Transactions don’t interfere
• Durability – Committed data is permanent

Implicit vs Explicit Transactions

Some SQL statements auto-commit by default.

• Implicit: Each statement commits automatically
• Explicit: Controlled using START TRANSACTION

Explicit transactions give full control.

When Transactions Are Not Used

Transactions may not apply when:

• Using read-only SELECT queries
• Tables use non-transactional engines
• Performance trade-offs are required

In MySQL, use InnoDB for transaction support.

Common Beginner Mistakes

• Forgetting to COMMIT
• Using transactions on non-transactional tables
• Not handling errors properly
• Mixing auto-commit and manual transactions

Why Transactions Are Critical

Transactions ensure:

• Data accuracy
• System reliability
• Safe multi-step operations

They are essential for professional database systems.

What’s Next?

In the next lesson, we will build an Intermediate SQL Project that applies joins, subqueries, views, indexes, constraints, and transactions together.