? MySQL Transactions

? Quick Overview

A transaction in MySQL is a sequence of one or more SQL statements executed as a single unit. Transactions ensure the ACID properties — Atomicity, Consistency, Isolation, Durability.

? When to Use Transactions?

Banking operations (money transfers)
Order processing systems
Critical multi-step data updates

? Transaction Control Statements

START TRANSACTION or BEGIN – Starts a new transaction
COMMIT – Saves all changes permanently
ROLLBACK – Undoes changes since last COMMIT
SAVEPOINT name – Creates a rollback checkpoint
ROLLBACK TO SAVEPOINT name – Rolls back to a specific checkpoint
SET AUTOCOMMIT = 0 – Disables automatic commit

? Example: Bank Transfer

? View Code Example

// Creating table and transferring funds using a transaction
CREATE TABLE accounts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);
INSERT INTO accounts (name, balance) VALUES
('Alice', 1000.00),
('Bob', 500.00);
START TRANSACTION;
UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';
COMMIT;
ROLLBACK;

? Isolation Levels

READ UNCOMMITTED – Dirty reads allowed
READ COMMITTED – Prevents dirty reads
REPEATABLE READ – Prevents non-repeatable reads (MySQL default)
SERIALIZABLE – Highest isolation level

? Tips & Best Practices

Always finish a transaction with COMMIT or ROLLBACK.
Turn off autocommit when executing related operations.
Use transactions for sensitive or multi-step data operations.

? Try It Yourself

Create an orders table and a payments table.
Write a transaction that inserts both an order and a payment.
Use SAVEPOINT and ROLLBACK TO for partial undo behavior.