Sams Teach Yourself SQL in 24 Hours
- Table of Contents
- Copyright
- About the Authors
- Acknowledgments
- Tell Us What You Think!
- Introduction
- Part I: A SQL Concepts Overview
- Hour 1. Welcome to the World of SQL
- Part II: Building Your Database
- Hour 2. Defining Data Structures
- Hour 3. Managing Database Objects
- Hour 4. The Normalization Process
- Hour 5. Manipulating Data
- Hour 6. Managing Database Transactions
- What Is a Transaction?
- What Is Transactional Control?
- Transactional Control and Database Performance
- Summary
- Q&A
- Workshop
- Part III: Getting Effective Results from Queries
- Hour 7. Introduction to the Database Query
- Hour 8. Using Operators to Categorize Data
- Hour 9. Summarizing Data Results from a Query
- Hour 10. Sorting and Grouping Data
- Hour 11. Restructuring the Appearance of Data
- Hour 12. Understanding Dates and Times
- Part IV: Building Sophisticated Database Queries
- Hour 13. Joining Tables in Queries
- Hour 14. Using Subqueries to Define Unknown Data
- Hour 15. Combining Multiple Queries into One
- Part V: SQL Performance Tuning
- Hour 16. Using Indexes to Improve Performance
- Hour 17. Improving Database Performance
- Part VI: Using SQL to Manage Users and Security
- Hour 18. Managing Database Users
- Hour 19. Managing Database Security
- Part VII: Summarized Data Structures
- Hour 20. Creating and Using Views and Synonyms
- Hour 21. Working with the System Catalog
- Part VIII: Applying SQL Fundamentals in Today's World
- Hour 22. Advanced SQL Topics
- Hour 23. Extending SQL to the Enterprise, the Internet, and the Intranet
- Hour 24. Extensions to Standard SQL
- Part IX: Appendixes
- Appendix A. Common SQL Commands
- Appendix B. Using MySQL for Exercises
- Appendix C. Answers to Quizzes and Exercises
- Appendix D. CREATE TABLE Statements for Book Examples
- Appendix E. INSERT Statements for Data in Book Examples
- Appendix F. Glossary
- Appendix G. Bonus Exercises
What Is Transactional Control?
|
Transactional control is the ability to manage various transactions that may occur within a relational database management system. When you speak of transactions, you are referring to the INSERT, UPDATE, and DELETE commands, which were covered during the last hour. |
When a transaction is executed and completes successfully, the target table is not immediately changed, although it may appear so according to the output. When a transaction successfully completes, there are transactional control commands that are used to finalize the transaction, either saving the changes made by the transaction to the database or reversing the changes made by the transaction.
There are three commands used to control transactions:
- COMMIT
- ROLLBACK
- SAVEPOINT
Each of these is discussed in detail in the following sections.
When a transaction has completed, the transactional information is stored either in an allocated area or in a temporary rollback area in the database. All changes are held in this temporary rollback area until a transactional control command is issued. When a transactional control command is issued, changes are either made to the database or discarded; then, the temporary rollback area is emptied. Figure 6.1 illustrates how changes are applied to a relational database.
)
Figure 6.1 Rollback area.
The COMMIT Command
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all transactions to the database since the last COMMIT or ROLLBACK command.
The syntax for this command is
COMMIT [ WORK ];
The keyword COMMIT is the only mandatory part of the syntax, along with the character or command used to terminate a statement according to each implementation. WORK is a keyword that is completely optional; its only purpose is to make the command more user-friendly.
In the following example, you begin by selecting all data from the PRODUCT_TMP table:
SELECT * FROM PRODUCTS_TMP;
PROD_ID PROD_DESC COST ---------- ------------------------------ ------ 11235 WITCHES COSTUME 29.99 222 PLASTIC PUMPKIN 18 INCH 7.75 13 FALSE PARAFFIN TEETH 1.1 90 LIGHTED LANTERNS 14.5 15 ASSORTED COSTUMES 10 9 CANDY CORN 1.35 6 PUMPKIN CANDY 1.45 87 PLASTIC SPIDERS 1.05 119 ASSORTED MASKS 4.95 1234 KEY CHAIN 5.95 2345 OAK BOOKSHELF 59.99 11 rows selected.
Next, you delete all records from the table where the product cost is less than $14.00.
A COMMIT statement is issued to save the changes to the database, completing the transaction.
The ROLLBACK Command
The ROLLBACK command is the transactional control command used to undo transactions that have not already been saved to the database. The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
The syntax for the ROLLBACK command is as follows:
Once again, as in the COMMIT statement, the WORK keyword is an optional part of the ROLLBACK syntax.
In the following example, you begin by selecting all records from the PRODUCTS_TMP table since the previous deletion of 14 records:
Next, you update the table, changing the product cost to $39.99 for the product identification number 11235:
If you perform a quick query on the table, the change appears to have occurred:
Now, issue the ROLLBACK statement to undo the last change:
Finally, verify that the change was not committed to the database:
The SAVEPOINT Command
A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
The syntax for the SAVEPOINT command is
This command serves only in the creation of a SAVEPOINT among transactional statements. The ROLLBACK command is used to undo a group of transactions. The SAVEPOINT is a way of managing transactions by breaking large numbers of transactions into smaller, more manageable groups.
The ROLLBACK TO SAVEPOINT Command
The syntax for rolling back to a SAVEPOINT is as follows:
In this example, you plan to delete the remaining three records from the PRODUCTS_TMP table. You want to create a SAVEPOINT before each delete, so that you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state:
Now that the three deletions have taken place, say you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP2. Because SP2 was created after the first deletion, the last two deletions are undone:
Notice that only the first deletion took place since you rolled back to SP2:
Remember, the ROLLBACK command by itself will roll back to the last COMMIT or ROLLBACK. You have not yet issued a COMMIT, so all deletions are undone, as in the following example:
The RELEASE SAVEPOINT Command
The RELEASE SAVEPOINT command is used to remove a SAVEPOINT that you have created. Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the SAVEPOINT. You may want to release a SAVEPOINT to avoid the accidental rollback to a SAVEPOINT that is no longer needed.
The SET TRANSACTION Command
The SET TRANSACTION command can be used to initiate a database transaction. This command is used to specify characteristics for the transaction that follows. For example, you can specify a transaction to be read only or read write. For example,
READ WRITE is used for transactions that are allowed to query and manipulate data in the database. READ ONLY is used for transactions that require query-only access. READ ONLY is useful for report generation and for increasing the speed at which transactions are accomplished. If a transaction is READ WRITE, the database must create locks on database objects to maintain data integrity in case multiple transactions are happening concurrently. If a transaction is READ ONLY, no locks are established by the database, thereby improving transaction performance.
There are other characteristics that can be set for a transaction that are out of the scope of this book. For more information, see the documentation for your implementation of SQL.
Transactional Control and Database Performance | Next Section
Account Sign In
View your cart