Managing Transaction Processing for SQL Database Integrity
- Understanding Transaction Processing
- Controlling Transactions
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
In this lesson, you'll learn what transactions are and how to use COMMIT and ROLLBACK statements to manage transaction processing.
Understanding Transaction Processing
Transaction processing is used to maintain database integrity by ensuring that batches of SQL operations execute completely or not at all.
As explained back in Lesson 12, "Joining Tables," relational databases are designed so that data is stored in multiple tables to facilitate easier data manipulation, management, and reuse. Without going in to the hows and whys of relational database design, take it as a given that well-designed database schemas are relational to some degree.
The Orders tables that you've been using in the past 18 lessons are a good example of this. Orders are stored in two tables: Orders stores actual orders, and OrderItems stores the individual items ordered. These two tables are related to each other using unique IDs called primary keys (as discussed in Lesson 1, "Understanding SQL"). These tables, in turn, are related to other tables containing customer and product information.
The process of adding an order to the system is as follows:
-
Check if the customer is already in the database. If not, add him or her.
-
Retrieve the customer's ID.
-
Add a row to the Orders table associating it with the customer ID.
-
Retrieve the new order ID assigned in the Orders table.
-
Add one row to the OrderItems table for each item ordered, associating it with the Orders table by the retrieved ID (and with the Products table by product ID).
Now imagine that some database failure (for example, out of disk space, security restrictions, table locks) prevents this entire sequence from completing. What would happen to your data?
Well, if the failure occurred after the customer was added and before the Orders table was added, there is no real problem. It is perfectly valid to have customers without orders. When you run the sequence again, the inserted customer record will be retrieved and used. You can effectively pick up where you left off.
But what if the failure occurred after the Orders row was added, but before the OrderItems rows were added? Now you'd have an empty order sitting in your database.
Worse, what if the system failed during adding the OrderItems rows? Now you'd end up with a partial order in your database, but you wouldn't know it.
How do you solve this problem? That's where Transaction Processing comes in. Transaction Processing is a mechanism used to manage sets of SQL operations that must be executed in batches so as to ensure that databases never contain the results of partial operations. With Transaction Processing, you can ensure that sets of operations are not aborted mid-processing—they either execute in their entirety or not at all (unless explicitly instructed otherwise). If no error occurs, the entire set of statements is committed (written) to the database tables. If an error does occur, then a rollback (undo) can occur to restore the database to a known and safe state.
So, looking at the same example, this is how the process would work:
-
Check if the customer is already in the database; if not add him or her.
-
Commit the customer information.
-
Retrieve the customer's ID.
-
Add a row to the Orders table.
-
If a failure occurs while adding the row to Orders, roll back.
-
Retrieve the new order ID assigned in the Orders table.
-
Add one row to the OrderItems table for each item ordered.
-
If a failure occurs while adding rows to OrderItems, roll back all the OrderItems rows added and the Orders row.
When working with transactions and transaction processing, there are a few keywords that'll keep reappearing. Here are the terms you need to know:
Transaction A block of SQL statements
Rollback The process of undoing specified SQL statements
Commit Writing unsaved SQL statements to the database tables
Savepoint A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)
TIP
Which Statements Can You Roll Back? Transaction processing is used to manage INSERT, UPDATE, and DELETE statements. You cannot roll back SELECT statements. (There would not be much point in doing so anyway.) You cannot roll back CREATE or DROP operations. These statements may be used in a transaction block, but if you perform a rollback they will not be undone.