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, andDurability(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 [email protected] WHERE account_number='A'; mysql> SELECT balance INTO @b.bal FROM account WHERE account_number='B';
Programmatically, check whether @b.bal
is NOT NULL
:
mysql> UPDATE account SET [email protected]+100 WHERE account_number='B';
These four SQL lines should be part of...