The Database Design Resource Center

MySQL Transactions:
The ACID concept applied on securing complex updates

MySQL transactions has been supported since version 3.23-max, which came out in 2002.

Soon after, transaction support was included in the community version of MySQL as well.

The addition of MySQL transactions was a major step towards making MySQL a serious option for mission critical database applications, because it allowed developers to ensure consistency in accounting type database tables in the way that they were accustomed to doing it in the large commercial databases like Oracle and DB2, and Microsoft SQL.

Using transactions, queries that modify interdependent values in one or more database tables can be bound together as a unit, and can either succeed or fail as a unit. The most desirable aspect of this is that if any query in the set fails, all tables involved are restored, or “rolled back” to their original state (before the transaction began).

Transactions are widely used in the banking and financial industry to maintain the integrity of financial records in applications that may be performing many thousands or millions of operations at any given moment. Transactions ensure that if there is a power outage or a server crashes, those thousands of interrupted operations will not leave the bank's database tables in an unresolvable mess.

As is the case with many operations that take place within the database server, such as triggers and stored procedures, it would be possible to code this functionality into the application itself. However, used correctly, transactions offer advantages over application code. They need only be written once, and then can be called quickly from any place in the application code.

They are portable and platform independent, and can execute more quickly than application code.

MySQL transactions can be rolled back when MySQL errors are detected at the application level. Transactions can also be rolled back in response to conditional statements within the application code, as we will demonstrate in our PHP example.

Before looking at the PHP code, lets talk a bit about how transactions work in MySQL. Transactions in MySQL, used with InnoDB tables, are ACID compliant. This means that transaction have the following properties:

  1. Atomicity. Transactions are atomic. This means that they succeed or fail as a unit.
  2. Consistency. Your database table will be in a consistent state before after transactions execute.
  3. Isolation. Transactions occur in isolation from one another. InnoDB tables use row level locking so that data is not modified as a transaction is taking place.
  4. Durability. Once a transaction is committed, it is recorded permanently in the database.
There are two codicils that should be mentioned at this point:

Regarding atomicity, it is possible to insert savepoints into transactions. These are points at which the rollback will stop, and are placed at the discretion of the developer. Transaction statements which precede the savepoint will written to the database, even if a transaction fails.

The other point is that InnoDB tables actually support 4 different levels of isolation. In order of increasing isolation, they are:

  1. Read Uncommitted: A transaction can see row modifications made by other transactions even before they have been committed.
  2. Read Committed: A transaction can see row modifications made by other transactions only if they were committed before the transaction began.
  3. Repeatable Read: If a transaction performs a select statement twice, the same result will be returned even if the table has been modified.
  4. This isolation level is similar to Repeatable Read but isolates transactions more completely: Rows that are being “viewed” by one transaction cannot be seen by another until the first transaction is complete. This isolates transactions effectively but also incurs the largest performance hit on the server. This setting is not necessary in most applications.
The default isolation setting for InnoDB is number 3, Repeatable Read. The isolation level can be set at runtime with the SET TRANSACTION statement.

MySQL transactions are initiated by the START TRANSACTION statement and end either with a COMMIT statement or a ROLLBACK statement. As you might expect, the COMMIT statement executes the intervening SQL statements on the database server, and the ROLLBACK statement nullifies them.

For our illustration of transactions, we'll create an adapted version of events similar to those that might occur inside an online shopping cart . We'll create a sales table, a shipping table, and an inventory table that will be modified depending on the outcome of approval of the customer's credit card.

If the sale is approved, the sale will be committed to the sales table, the shipment will be scheduled, and the inventory table will be decremented for the product purchased.

If approval fails, the MySQL transaction will be rolled back and each table will revert to its state before the transaction began.

Here is the structure of the tables:

MySQL Transactions

At this point, the sales table and the shipping table are empty and the inventory table contains the following:

MySQL Transactions

Executed from the MySQL command line, transactions are not responsive to events in the server. That is, the rollback or commit must be entered at the prompt to complete the transaction.

To use MySQL transactions in a meaningful way, they must be integrated with your application code.

In the following PHP code snippet, the transaction in our sample application is either rolled back or committed depending on the value of a variable ($approval) that indicates whether a customer received credit approval.

MySQL Transactions MySQL Transactions

In the preceding code, in the highlighted line, if the value of $approval were set to “no”, all the preceding transactions would be rolled back; the entry would disappear from the shipping table, the product count in the inventory table would revert to 255, and the sale entry would be gone from the sales table.

Another way that transactions are commonly used is to recover from internal server errors. Each API has a somewhat different method for capturing errors, but in general, they are not particularly difficult to implement. This can be accomplished in PHP 5 with the PEAR DB module.

Using the transaction abstraction methods supplied by this module, exceptions can be thrown when MySQL errors occur, and these exceptions can be used to trigger a rollback.

In this way your application data has a good deal more protection from software malfunctions than it would have if transactions were not in place.

Hopefully the example has provided you with a general understanding of how transactions work and will get started on using them in your own work.

There is good information about MySQL transactions on the MySQL website.

Consult the website of your favorite API for language-specific adaptations.

Return to MySQL DBA

Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant

Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:

What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:

Database Normalization eBook

Copyright © / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.