MySQL Transactions Overview
- What Are Transactions?
- Transaction-Safe Table Types in MySQL
- Summary
- Workshop
This chapter is from the book
This chapter is from the book
This chapter is from the book
Transactions are a new addition to MySQL but not to relational database systems in general. If you have used an enterprise database system, such as Oracle or Microsoft SQL Server, the transactional concept should seem familiar. If this is your first venture into relational databases, this hour will bring you up to speed and provide an overview of using transactions in MySQL.
In this hour, you will learn about
-
The basic properties of transactions
-
Berkeley DB, InnoDB, and Gemini table types
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:
Check that the balance of the first account is greater than $100.
Deduct $100 from the first account.
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 a ROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.
COMMIT and ROLLBACK
When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect. If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.
NOTE
In MySQL as well as NuSphere's Enhanced MySQL, you can set the value of a session variable called AUTOCOMMIT. If AUTOCOMMIT is set to 1 (the default), then each SQL statement (within a transaction or not) is considered a complete transaction, committed by default when it finishes. When AUTOCOMMIT is set to 0, by issuing the SET AUTOCOMMIT=0 command, the subsequent series of statements acts like a transaction, and no activities are committed until an explicit COMMIT statement is issued.
If transactions were not used in application development, a large amount of programming time would be spent on intricate error checking. For example, suppose your application handles customer order information, with tables holding general order information as well as line items for that order. To insert an order into the system, the process would be something like the following:
Insert a master record into the master order table.
Retrieve the ID from the master order record you just entered.
Insert records into the line items table for each item ordered.
If you are not in a transactional environment, you will be left with some straggly data floating around your tables; if the addition of the record into the master order table succeeds, but steps 2 or 3 fail, you are left with a master order without any line items. The responsibility then falls on you to use programming logic and check that all relevant records in multiple tables have been added or go back and delete all the records that have been added and offer error messages to the user. This is extremely time-consuming, both in man-hours as well as in program-execution time.
In a transactional environment, you'd never get to the point of childless rows, as a transaction either fails completely or is completely successful.
Row-Level Locking
Transactional table types support row-level locking, which differs from the table-level locking that is enforced in MyISAM and other nontransactional table types. With tables that support row-level locking, only the row touched by an INSERT, UPDATE, or DELETE statement is inaccessible until a COMMIT is issued.
Rows affected by a SELECT query will have shared locks, unless otherwise specified by the programmer. A shared lock allows for multiple concurrent SELECT queries of the data. However, if you hold an exclusive lock on a row, you are the only one who can read or modify that row until the lock is released. Locks are released when transactions end through a COMMIT or ROLLBACK statement.
Setting an exclusive lock requires you to add the FOR UPDATE clause to your query. In the sequence below, you can see how locks are used to check available inventory in a product catalog before processing an order. This example builds on the previous example by adding more condition-checking.
NOTE
This sequence of events is independent of the programming language used; the logical path can be created in whichever language you use to create your application.
Begin transaction.
Check available inventory for a product with a specific ID, using a table called inventory and a field called qty.
If the result is less than the amount ordered, rollback the transaction to release the lock.
If the result is greater than the amount ordered, continue issuing a statement that reserves the required amount for the order.
Insert a master record into the master order table.
Retrieve the ID from the master order record you just entered.
Insert records into the line items table for each item ordered.
If steps 5 through 7 are successful, commit the transaction and release the lock.
BEGIN WORK;
SELECT qty FROM inventory WHERE id = 'ABC-001' FOR UPDATE;
ROLLBACK;
UPDATE inventory SET qty = qty - [amount ordered] WHERE id = 'ABC-001';
COMMIT;
While the transaction remains uncommitted and the lock remains in effect, no other users can access the record in the inventory table for the product with the ID of ABC-001. If a user requests the current quantity for the item with the ID of ABC-002, that row still operates under the shared lock rules and can be read.