Money, Money, Money... - MySQL

We'll start, as always, with getting the terminology down: what the heck is a transaction when it's at home?

In the SQL world, the term "transaction" refers to a series of SQL statements which are treated as a single unit by the RDBMS. Typically, a transaction is used to group together SQL statements which are interdependent on each other; a failure in even one of them is considered a failure of the group as a whole. Thus, a transaction is said to be successful only if *all* the individual statements within it are executed successfully.

You might find it hard to think of situations where this "all-for-one, one-for-all" approach would be useful. In reality, transactions abound all around us, in bank transfers, stock trades, Web-based shopping carts, inventory control... the list goes on and on. In all these cases, the success of the transaction depends on a number of interdependent actions executing successfully and in harmony with each other; a failure in any of them must cancel the transaction and return the system back to its earlier, pre-transaction state.

Consider, for example, the simple example of a bank account transfer. Let's assume Joe's just moved to a new city and his Mum wants to give him $2000 to help him get settled. She strolls down to her neighborhood bank and asks them to transfer $2000 to Joe's account, debiting her own account for the same. The bank, suspicious like all good banks are, first checks to make sure she has $2000 in her account. When they find that she does, they subtract $2000 from her account balance and add $2000 to Joe's account.

If you were to diagram the transaction above, it might look like this.

A failure in any of the steps above - say, for example, if the bank debited Joe's Mum's account but wasn't able to credit the same amount to Joe's account - would imply the failure of the entire transaction. In the event of such a failure, the transaction would be cancelled, and the system (in this example, the bank balances of Joe and his mother) would be returned to its earlier, pre-transaction state.

Thus, by offering a way to group a series of database actions together into a single unit, and by adding intelligence at the database level to "undo" the effects of failed database operations and revert the system to a stable state, transactions play an important role in helping SQL developers build more robust applications.

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus