Transaction

Motivation

Suppose we have a banking system and two clients Alice and Bob. Alice wants to send $100 to Bob. This action can be subdivided into two parts:

1. Deduct $100 from Alice's account.

2. Add $100 to Bob's account.

But here is the problem: if step 1 succeeds and step 2 fails, then Alice will lose $100 and Bob will get nothing. We need step 1 and step 2 to be an atomic unit: if step 2 fails, then step 1 must fail as well. To overcome this barrier, MySQL implemented transaction.

What is Transaction

Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

Since MySQL > 5.4, the default storage engine is InnoDB. InnoDB supports transaction.

Lab

Let there be a table tb with the following entries:

Table tb, before

Start a new transaction:

BEGIN;

Delete all entries from tb:

DELETE FROM tb;

Verify that all entries were deleted:

Table tb, after

Now, you can either revert the deletion:

ROLLBACK;

or make the deletion permanent:

COMMIT;

Auto Commit

Auto commit is turned on by default. Turn it off:

SET AUTOCOMMIT=0

This is same as starting a transaction session. Or turn it back on:

SET AUTOCOMMIT=1