? MySQL COMMIT & ROLLBACK

? Quick Overview

In MySQL, COMMIT and ROLLBACK are transaction control statements. They ensure database consistency by allowing multiple queries to be treated as a single logical unit of work.

? Key Concepts

• Transactions group multiple SQL statements together
• Changes remain temporary until committed
• Rollback allows undoing uncommitted changes

? Syntax & Theory

Transactions work only with storage engines that support them, such as InnoDB. A transaction begins explicitly and ends with either a commit or rollback.

? COMMIT

The COMMIT statement permanently saves all changes made in the current transaction.

-- Saves all changes made in the current transaction
COMMIT;

↩️ ROLLBACK

The ROLLBACK statement reverts all changes made during the current transaction.

-- Undoes all uncommitted changes
ROLLBACK;

⚡ Example: COMMIT and ROLLBACK in Action

This example demonstrates starting a transaction, inserting data, and either committing or rolling back the changes.

-- Start a new transaction
START TRANSACTION;
-- Insert a new employee record
INSERT INTO employees (name, position, salary) VALUES ('John Doe', 'Developer', 60000);
-- Permanently save the changes
COMMIT;
-- Use ROLLBACK instead if an error occurs
-- ROLLBACK;

? Live Explanation

If all queries execute successfully, the commit makes them permanent. If an error occurs, rollback restores the database to its previous state.

? Interactive Concept

Think of transactions like a save point in a game — you can either save progress (COMMIT) or reload the last save (ROLLBACK).

? Use Cases

• Banking and financial transactions
• Multi-table inserts and updates
• Order processing systems