Home arrow MySQL arrow Page 2 - Using Transactions In MySQL (Part 1)

Money, Money, Money... - MySQL

mysqlOne 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.

  1. Using Transactions In MySQL (Part 1)
  2. Money, Money, Money...
  3. The Acid Test
  4. Turning the Tables
  5. A Question of Commitment
  6. Rules of the Game
  7. Artificial Intelligence
  8. Time Out
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 74
November 03, 2003

print this article


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
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: