HomeMySQL Page 2 - Using Transactions In MySQL (Part 1)
Money, Money, Money... - MySQL
One of the most-requested MySQL features - transactions - is finally available in MySQL 4.0. In this first segment of a two-part article, learn about the theory behind the transactional model, find out how it can make your SQL applications more robust, and find out how to implement a transactional environment with MySQL's InnoDB table handler.
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.
