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);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 [email protected] 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 [email protected]+100 WHERE account_number='B';
After making sure that all the SQLs are executed...