MySQL - transaction control statements

1. COMMIT

• Saves all changes made in the current transaction.

• Once committed, changes become permanent and visible to other users.

Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Here, the transfer is only finalized when COMMIT is executed.

2. ROLLBACK

• Cancels all changes made in the current transaction.

• Returns the database to the state it was in before the transaction started.

Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

ROLLBACK;

Here, both updates are undone (no money moves).

3. SAVEPOINT

• Sets a checkpoint within a transaction.

• Lets you roll back only part of the transaction, instead of the entire thing.

• You can have multiple savepoints in one transaction.

Example:

START TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
SAVEPOINT step1;

UPDATE accounts SET balance = balance + 300 WHERE id = 2;
SAVEPOINT step2;

UPDATE accounts SET balance = balance + 200 WHERE id = 3;

-- Something went wrong, undo only last update
ROLLBACK TO step2;

COMMIT;

Result:

• Account 1 loses 500.

• Account 2 gains 300.

• Account 3 does not change (last step was rolled back).

Quick Summary

• COMMIT → make all changes permanent.

• ROLLBACK → undo all changes since last START TRANSACTION.

• SAVEPOINT → mark a point you can roll back to, without undoing everything.