Introduction

In the following recipes, we will discuss the transactions and various isolation levels in MySQL. Transaction means a set of SQL statements that should succeed or fail together. Transactions should also satisfy Atomicity, Consistency, Isolation, and Durability(ACID) properties. Take a very basic example of a money transfer from account A to account B. Assume that A has $600, B has $400, and B wishes to transfer $100 from A to itself.

The bank would deduct $100 from A and add to B using the following SQL code (for illustration):

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

Programmatically, check whether @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...