Transaction in MYSQL

Posted: February 15, 2010 in Uncategorized

Do not forget to use Transaction, when you are writing any Stored-Procedure or function in MYSQL. By Transactionm you can make save points, so that, when a problem exists, it will be rolled back. Saying simply, your sql statements are not committed, till it reaches the last line.

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summary=@A WHERE type=1;
COMMIT;
Transaction is well described at http://www.informit.com/articles/article.aspx?p=29312

What Are Transactions?

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail. A good example would be a banking transaction, specifically a transfer of $100 between two accounts. In order to deposit money into one account, you must first take money from another account. Without using transactions, you would have to write SQL statements that do the following:
  1. Check that the balance of the first account is greater than $100.
  2. Deduct $100 from the first account.
  3. Add $100 to the second account.
Additionally, you would have to write your own error-checking routines within your program, specifically to stop the sequence of events should the first account not have more than $100 or should the deduction statement fail. This all changes with transactions, for if any part of the operation fails, the entire transaction is rolled back. This means that the tables and the data inside them revert to their previous state.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:
  • Atomicity ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  • Consistency ensures that the database properly changes states upon a successfully committed transaction.
  • Isolation enables transactions to operate independently of and transparent to each other.
  • Durability ensures that the result or effect of a committed transaction persists in case of a system failure.
In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or aROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s