Performing transactions

Create dummy tables and sample data to understand this recipe:

mysql> CREATE DATABASE bank;
mysql> USE bank;
mysql> CREATE TABLE account(account_number varchar(10) PRIMARY KEY, balance int);
mysql> INSERT INTO account VALUES('A',600),('B',400);

How to do it...

To start a transaction (set of SQLs), execute the START TRANSACTION or BEGIN statement:

mysql> START TRANSACTION;
or 
mysql> BEGIN;

Then execute all the statements that you wish to be inside a transaction, such as transferring 100 from A to B:

mysql> SELECT balance INTO @a.bal FROM account WHERE account_number='A';

Programmatically check if @a.bal is greater than or equal to 100 
mysql> UPDATE account SET balance=@a.bal-100 WHERE account_number='A';
mysql> SELECT balance INTO @b.bal FROM account WHERE account_number='B';

Programmatically check if @b.bal IS NOT NULL 
mysql> UPDATE account SET balance=@b.bal+100 WHERE account_number='B';

After making sure that all the SQLs are executed...