Database Transactions — COMMIT and ROLLBACK

Many real business operations are not a single change but a group of changes that must all succeed or all fail together. The classic example is transferring money in a bank: the amount must be subtracted from one account and added to another. If the first change happens but the second fails (say the power goes off in between), the money would simply vanish — the database would be left in a wrong, inconsistent state. To prevent exactly this, a database groups such changes into a transaction.

A transaction is a logical unit of work made up of one or more SQL statements that is treated as a single, indivisible operation: either every statement in it takes effect, or none of them does. A transaction has two possible endings:

• COMMIT — makes all the changes of the transaction permanent in the database. Once committed, the changes are saved and cannot be undone by a rollback.
• ROLLBACK — undoes all the changes made since the transaction began, returning the database to the state it was in before the transaction started. It is used when something goes wrong, or when the user decides not to keep the changes.

In MySQL a transaction is usually begun with the statement START TRANSACTION; (the older keyword BEGIN also works). The changes are then made with ordinary DML statements, and the transaction is finished with either COMMIT; or ROLLBACK;. Here is a simple example that gives every employee in the Sales department a raise, checks the result, and only then makes it permanent:

START TRANSACTION;
UPDATE Employee
SET Salary = Salary + 2000
WHERE Dept = 'Sales';
-- if the change looks correct:
COMMIT;

If instead the change turned out to be wrong before committing, we could undo it completely:

START TRANSACTION;
DELETE FROM Employee
WHERE Dept = 'Sales';
-- oops, that was a mistake — undo it:
ROLLBACK;

After the ROLLBACK, the deleted Sales rows are restored, exactly as if the DELETE had never run. This ability to undo is only available before the transaction is committed; a COMMIT is final. …