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:
- Atomicity. Transactions are atomic. This means that they succeed or fail as a unit.
- Consistency. Your database table will be in a consistent state before after transactions execute.
- 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.
- 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:
- Read Uncommitted: A transaction can see row modifications made by other transactions even before they have been committed.
- Read Committed: A transaction can see row modifications made by
other transactions only if they were committed before the transaction
began.
- Repeatable Read: If a transaction performs a select statement
twice, the same result will be returned even if the table has been
modified.
- 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:
At this point, the sales table and the shipping table are empty and the inventory table contains the following:
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.
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
|
