Transaction

A transaction in MySQL is a sequence of one or more SQL statements that are executed as a single unit of work. It ensures that either all operations succeed, or none of them do.

Transactions follow the ACID properties:

1. Atomicity → All statements in a transaction succeed or fail together.
2. Consistency → Data must remain valid before and after the transaction.
3. Isolation → Multiple transactions do not interfere with each other.
4. Durability → Once a transaction is committed, changes are permanent.

When Do We Use Transactions?

• Banking systems (transfer money from one account to another).
• Booking systems (seat reservation).
• Inventory management (stock adjustments).
• Any scenario where data integrity is critical.

Basic Transaction Commands

1. START TRANSACTION; → Begins a new transaction. (or BEGIN; as a shortcut)
2. COMMIT; → Saves all changes permanently.
3. ROLLBACK; → Cancels the transaction and restores the data to its state before START TRANSACTION.
4. SAVEPOINT name; → Sets a savepoint within a transaction.
5. ROLLBACK TO name; → Rolls back only up to the specified savepoint.
6. SET AUTOCOMMIT = 0; → Turns off automatic commit (MySQL normally commits after every statement by default).

Bank Transfer (Atomic Transaction)

Imagine you are transferring 1000 BDT from Account A to Account B.

Table

CREATE TABLE accounts (
    account_id INT PRIMARY KEY,
    name VARCHAR(50),
    balance DECIMAL(10,2)
);

INSERT INTO accounts VALUES
(1, 'Alice', 5000.00),
(2, 'Bob', 3000.00);

Transaction

START TRANSACTION;

-- Step 1: Deduct 1000 from Alice
UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

-- Step 2: Add 1000 to Bob
UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

-- Commit if both succeed
COMMIT;

• If both updates succeed → Changes are saved.
• If any step fails (e.g., system crash after deduction but before deposit) → You can ROLLBACK; to undo changes.

Using ROLLBACK

Suppose Alice tries to transfer 6000 BDT (but she only has 5000).

START TRANSACTION;

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

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

-- Now check Alice’s balance
SELECT balance FROM accounts WHERE account_id = 1;

-- If balance < 0, rollback
ROLLBACK;

Result → No money transferred, balances remain unchanged.

Using SAVEPOINT

You can partially undo operations.

START TRANSACTION;

-- Deduct from Alice
UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
SAVEPOINT after_deduct;

-- Add to Bob
UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

-- Oops, wrong account! Roll back only the second step
ROLLBACK TO after_deduct;

-- Commit only Alice’s deduction
COMMIT;

Alice’s balance is reduced by 500, but Bob didn’t get credited because we rolled back.

Key Notes on Transaction

• Transactions only work on transactional storage engines like InnoDB (not MyISAM).
• Always check if autocommit is ON (default in MySQL). If it is, use SET AUTOCOMMIT=0;.
• Use transactions in critical data operations where partial updates would cause inconsistencies.