Hour 11
Managing Database Transactions
What You’ll Learn in This Hour:
▶ The definition of a transaction
▶ The commands used to control transactions
▶ The syntax and examples of transaction commands
▶ When to use transactional commands
▶ The consequences of poor transactional control
So far, this book has discussed all-or-nothing scenarios for manipulating data inside a database. However, in more complicated processes, you need the capability to isolate changes so that they can be applied or rolled back to an original state at will. This is where transactions come in. Transactions give you the additional flexibility to isolate database changes into discrete batches and undo those changes if something goes wrong. In this hour, you learn the concepts behind managing database transactions, how to implement them, and how to properly control transactions.
Defining Transactions
A transaction is a unit of work that is performed against a database. Transactions are units or sequences of work accomplished in a logical order, whether manually by a user or automatically by some sort of a database program. In a relational database that uses SQL, transactions are accomplished using the Data Manipulation Language (DML) commands that were discussed during Hour 10, “Manipulating Data” (INSERT, UPDATE, and DELETE). A transaction is the propagation of one or more changes to the database. For instance, you are performing a transaction if you perform an UPDATE statement on a table to change an individual’s name.
A transaction can be either one DML statement or a group of statements. When managing transactions, each designated transaction (group of DML statements) must be successful as one entity, or none of them will be successful.
The following list describes the nature of transactions:
▶ All transactions have a beginning and an end.
▶ A transaction can be saved or undone.
▶ If a transaction fails in the middle, no part of the transaction can be saved to the database.
Controlling Transactions
Transactional control is the capability to manage various transactions that might occur within a relational database management system (RDBMS). When you speak of transactions, you are referring to the INSERT, UPDATE, and DELETE commands, which you looked at during the previous hour.
Note
Transactions Are Implementation Specific
Starting or executing transactions is implementation specific. Check your particular implementation for how to begin transactions.
When a transaction is executed and completes successfully, the target table is not immediately changed (even though this might appear to be the case, according to the output). When a transaction successfully completes, transactional control commands 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. During the transaction execution, the 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 11.1 illustrates how changes are applied to a relational database.
FIGURE 11.1
Rollback area
Three commands are used to control transactions:
▶
COMMIT▶
ROLLBACK▶
SAVEPOINT
Each of these is discussed in detail in the following sections.
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 follows:
commit [ work ];
The keyword COMMIT is the only mandatory part of the syntax, along with the character or command that terminates a statement, according to each implementation. WORK is a keyword that is completely optional; its only purpose is to make the command user friendly.
In the following example, you begin by creating a copy of the MIGRATION table, called the MIGRATION_TEST_DELETE table, and querying the table for all records, to familiarize yourself with the data.
SQL> create table migration_test_delete as
2 select * from migration;
Table created.
SQL> select * from migration_test_delete;
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
3 Central America
2 Mexico
5 No Significant Migration
6 Partial, Open Water
4 South America
1 Southern United States
6 rows selected.
Next, you delete all rows of data from the MIGRATION_TEST_DELETE table for which the MIGRATION_ID is greater than 3. In other words, you remove half of the records from this table, for example purposes.
SQL> delete from migration_test_delete 2 where migration_id > 3; 3 rows deleted.
A COMMIT statement is issued to save the changes to the database, completing the transaction:
SQL> commit; Commit complete.
Finally, you query all records within the MIGRATION_TEST_DELETE table to verify the results of the transaction.
SQL> select * from migration_test_delete;
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
3 Central America
2 Mexico
1 Southern United States
3 rows selected.
As a reminder, throughout the book, some implementations have minor variations. Oracle is used for examples in this book, for consistency, but an example of a variation occurs with Microsoft SQL Server. For example, if you issue a COMMIT statement and use SQL Server, you get the following error:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
This is because SQL Server uses an autocommit. This simply means that it treats any statement as a transaction—SQL Server automatically issues a commit if the statement is successful and issues a rollback if it is not. To change this, you need to issue a SET IMPLICIT_TRANSACTIONS command and set the mode to ON:
SET IMPLICIT_TRANSACTIONS ON; Command(s) completed successfully.
If you want your current connection to go back to autocommit mode, you simply issue the same statement and set the mode to OFF:
SET IMPLICIT_TRANSACTIONS OFF; Command(s) completed successfully.
Frequent COMMIT statements in large loads or unloads of the database are highly recommended; however, too many COMMIT statements cause the job to take a lot of extra time to complete. Remember that all changes are first sent to the temporary rollback area. If this temporary rollback area runs out of space and cannot store information about changes made to the database, the database will probably halt, disallowing further transactional activity.
When an UPDATE, INSERT, or DELETE is issued, most RDBMSs use a form of transaction in the background so that if the query is canceled or runs into an error, changes are not committed. Therefore, issuing a transaction is more of an action to ensure that a set of transactions is run. This set is commonly referred to as a unit of work. In a real-world example, you might process a bank transaction at an ATM with a client that wants to withdraw money. In such a situation, you need to both insert a transaction for the money being withdrawn and update the client’s balance to reflect the new total. Obviously, you want either both of these statements to succeed or both of them to fail. Otherwise, the system’s data integrity is compromised. In this instance, you wrap your unit of work in a transaction to ensure that you can control the outcome of both statements.
Caution
Some Implementations Treat COMMIT Differently
In some implementations, transactions are committed without issuing the COMMIT command; merely signing out of the database causes a commit to occur. However, in some implementations, such as MySQL, after you perform a SET TRANSACTION command, the autocommit functionality does not resume until it has received a COMMIT or ROLLBACK statement. In addition, in other implementations, such as Microsoft SQL Server, statements are autocommitted unless a transaction is specifically used. Check the documentation of your particular RDBMS to understand exactly how transactions and statement commits are handled.
The ROLLBACK Command
The ROLLBACK command is the transactional control command that undoes transactions that have not already been saved to the database. You can use the ROLLBACK command to undo transactions only since the last COMMIT or ROLLBACK command was issued.
The syntax for the ROLLBACK command follows:
rollback [ work ];
Again, as in the COMMIT statement, the WORK keyword is an optional part of the ROLLBACK syntax.
For the rest of the exercise, if you use SQL Server, you need to turn on IMPLICIT_TRANSACTIONS to make the examples easier to follow:
SET IMPLICIT_TRANSACTIONS ON; Command(s) completed successfully.
The following examples demonstrate the behavior of the ROLLBACK command using a table called BIG_BIRDS that is derived from the BIRDS table.
SQL> create table big_birds as
2 select bird_id, bird_name, height, weight, wingspan, eggs
3 from birds
4 where wingspan > 48;
Table created.
SQL> select * from big_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN EGGS
---------- ------------------------------ ---------- ---------- ---------- --------
1 Great Blue Heron 52 5.5 78 5
3 Common Loon 36 18 54 2
4 Bald Eagle 37 14 84 2
5 Golden Eagle 40 15 90 3
7 Osprey 24 3 72 4
9 Canadian Goose 43 14 72 10
13 Ring-billed Gull 19 1.1 50 4
14 Double-crested Cormorant 33 5.5 54 4
16 Turkey Vulture 32 3.3 72 2
19 Mute Swan 60 26 94.8 8
20 Brown Pelican 54 7.6 90 4
21 Great Egret 38 3.3 67.2 3
12 rows selected.
In the previous output from the BIG_BIRDS table, you can see that the number of records from the BIRDS table was reduced to 12. The BIG_BIRDS table also consists of fewer columns than the BIRDS table. Less data makes it easier to see the results as you make changes to this table and study the behavior of the ROLLBACK command.
Next, let’s create a table called LOCATIONS2 that is based on the original locations table. This table has six records.
SQL> create table locations2 as
2 select * from locations;
Table created.
SQL> select * from locations2;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
3 Eagle Creek
6 Fort Lauderdale Beach
1 Heron Lake
2 Loon Creek
5 Sarasota Bridge
4 White River
6 rows selected.
After you create the two tables, you issue a ROLLBACK command. However, you can see that all records still exist in the LOCATIONS2 table that was created prior to the rollback. The ROLLBACK did not undo that transaction because the command issued before the ROLLBACK was a data definition command. COMMIT and ROLLBACK commands apply to Data Manipulation Language, not Data Definition Language.
SQL> rollback;
Rollback complete.
SQL> select * from locations2;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
3 Eagle Creek
6 Fort Lauderdale Beach
1 Heron Lake
2 Loon Creek
5 Sarasota Bridge
4 White River
6 rows selected.
Next, you insert a new row of data into the LOCATIONS2 table for Lake Michigan. The following query verifies that a new row of data was successfully inserted into the table:
SQL> insert into locations2
2 values (7, 'Lake Michigan');
1 row created.
SQL> select * from locations2;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
3 Eagle Creek
6 Fort Lauderdale Beach
1 Heron Lake
7 Lake Michigan
2 Loon Creek
5 Sarasota Bridge
4 White River
7 rows selected.
Next, you issue the COMMIT command to save the previous transaction to the database:
SQL> commit; Commit complete.
Now you can insert another row of data into the LOCATIONS2 table for the Gulf of Mexico. The query that follows shows that the Gulf of Mexico was successfully added and the previous entry for Lake Michigan is there as well:
SQL> insert into locations2
2 values (8, 'Gulf of Mexico');
1 row created.
SQL> select * from locations2;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
3 Eagle Creek
6 Fort Lauderdale Beach
8 Gulf of Mexico
1 Heron Lake
7 Lake Michigan
2 Loon Creek
5 Sarasota Bridge
4 White River
8 rows selected.
Now let’s issue the ROLLBACK command and see what happens:
SQL> rollback; Rollback complete.
In the results, the entry for Lake Michigan still exists in the LOCATIONS2 table, but the most recent record for the Gulf of Mexico is no longer there. The previous ROLLBACK command negated any transactions since the last COMMIT. Because a COMMIT command was issued after Lake Michigan was added to the table, the only transaction that was rolled back involved the entry of the Gulf of Mexico.
SQL> select * from locations;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
3 Eagle Creek
6 Fort Lauderdale Beach
1 Heron Lake
7 Lake Michigan
2 Loon Creek
5 Sarasota Bridge
4 White River
7 rows selected.
In the next example, let’s delete Lake Michigan from the LOCATIONS2 table. The LOCATION_ID for Lake Michigan is 7, so you can simply refer to the LOCATION_ID of 7 in the DELETE statement that follows:
SQL> delete from locations 2 where location_id = 7; 1 row deleted. SQL> delete from locations; 6 rows deleted.
After deleting the record for Lake Michigan, you issue another DELETE statement to delete the remaining rows of data from the LOCATIONS2 table. The following query shows that no rows exist any longer in the LOCATIONS2 table:
SQL> select * from locations; no rows selected
Finally, let’s issue another ROLLBACK command and perform a query from the LOCATIONS2 table. Take a minute to study the results:
SQL> rollback;
Rollback complete.
SQL> select * from locations;
LOCATION_ID LOCATION_NAME
----------- ------------------------------
3 Eagle Creek
6 Fort Lauderdale Beach
1 Heron Lake
7 Lake Michigan
2 Loon Creek
5 Sarasota Bridge
4 White River
7 rows selected.
The previous ROLLBACK command rolled back, or undid, all transactions since the last COMMIT. This includes both previous DELETE statements. Because no COMMIT commands were issued, all rows that were deleted remain in the LOCATIONS2 table.
The SAVEPOINT Command
A savepoint is a point in a transaction where you can roll back the transaction without rolling back the entire transaction.
The syntax for the SAVEPOINT command follows:
savepoint savepoint_name
This command serves only to create a savepoint among transactional statements. The ROLLBACK command undoes a group of transactions. The savepoint is a way of managing transactions by breaking large numbers of transactions into smaller, more manageable groups.
Microsoft SQL Server uses a slightly different syntax. In SQL Server, you use the statement SAVE TRANSACTION instead of SAVEPOINT, as shown in the following statement:
save transaction savepoint_name
Otherwise, the procedure works exactly the same as in the other implementations.
The ROLLBACK TO SAVEPOINT Command
The syntax for rolling back to a savepoint follows:
ROLLBACK TO SAVEPOINT_NAME;
In this example, you are working with the BIG_BIRDS table again. Review the table data:
SQL> select * from big_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN EGGS
---------- ------------------------------ ---------- ---------- ---------- --------
1 Great Blue Heron 52 5.5 78 5
3 Common Loon 36 18 54 2
4 Bald Eagle 37 14 84 2
5 Golden Eagle 40 15 90 3
7 Osprey 24 3 72 4
9 Canadian Goose 43 14 72 10
13 Ring-billed Gull 19 1.1 50 4
14 Double-crested Cormorant 33 5.5 54 4
16 Turkey Vulture 32 3.3 72 2
19 Mute Swan 60 26 94.8 8
20 Brown Pelican 54 7.6 90 4
21 Great Egret 38 3.3 67.2 3
12 rows selected.
This table has 12 records. Next, the DELETE statement is issued to delete the row of data from BIG_BIRDS corresponding to Great Blue Heron. The following result provides feedback that one row of data was deleted from the table:
SQL> delete from big_birds 2 where bird_name = 'Great Blue Heron'; 1 row deleted.
Next, you create a SAVEPOINT to create a reference point within this transaction as a whole. The following feedback indicates that the SAVEPOINT was successfully created:
SQL> savepoint sp1; Savepoint created.
Now you delete the record from the BIG_BIRDS table that corresponds to the BIRD_NAME of Bald Eagle and create a SAVEPOINT called SP2 after that DELETE statement. After that DELETE, you also update the BIG_BIRDS table and set the value for the EGGS column to 20 for every row of data in that table. As you can see, this affects 10 rows. A SAVEPOINT was also created after that update, called SP3.
SQL> delete from big_birds 2 where bird_name = 'Bald Eagle'; 1 row deleted. SQL> savepoint sp2; Savepoint created. SQL> update big_birds 2 set eggs = 0; 10 rows updated. SQL> savepoint sp3 2 ; Savepoint created.
Note
SAVEPOINT Names Must Be Unique
A savepoint’s name must be unique to the associated group of transactions. However, it can have the same name as a table or other object. Refer to your specific implementation documentation for more details on naming conventions. Otherwise, savepoint names are a matter of personal preference and are used by the database application developer to manage groups of transactions.
Now that the three deletions have taken place, let’s say that you change your mind and decide to issue a ROLLBACK command to the savepoint that you identify as SP2. Because SP2 was created after the first deletion, the last two deletions are undone. In Oracle, you use the following syntax:
ROLLBACK TO sp2; Rollback complete.
In SQL Server, you use the following syntax:
ROLLBACK TRANSACTION sp2; Command(s) completed successfully.
Now you can apply the syntax of the ROLLBACK to a SAVEPOINT to the previous examples in which you created three different SAVEPOINTS after deleting and updating rows of data in the BIG_BIRDS table. First, you query the BIG_BIRDS table to review the data that was affected. You can see that the records for Great Blue Heron and Bald Eagle are no longer in the BIG_BIRDS table. Also notice that every value in the EGGS column has been updated to zero.
SQL> select * from big_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN EGGS
---------- ------------------------------ ---------- ---------- ---------- --------
3 Common Loon 36 18 54 0
5 Golden Eagle 40 15 90 0
7 Osprey 24 3 72 0
9 Canadian Goose 43 14 72 0
13 Ring-billed Gull 19 1.1 50 0
14 Double-crested Cormorant 33 5.5 54 0
16 Turkey Vulture 32 3.3 72 0
19 Mute Swan 60 26 94.8 0
20 Brown Pelican 54 7.6 90 0
21 Great Egret 38 3.3 67.2 0
10 rows selected.
Let’s issue the ROLLBACK command back to SAVEPOINT SP2. SP2 was a SAVEPOINT created after both the Great Blue Heron and Bald Eagle records were removed, but before the value of every row of data in the EGGS column was set to zero. Therefore, the only transaction rolled back is the update to the EGGS column. You can still see in the output of the following SELECT statement that the Great Blue Heron and Bald Eagle records still no longer exist in that table:
SQL> rollback to sp2;
Rollback complete.
SQL> select * from big_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN EGGS
---------- ------------------------------ ---------- ---------- ---------- --------
3 Common Loon 36 18 54 2
5 Golden Eagle 40 15 90 3
7 Osprey 24 3 72 4
9 Canadian Goose 43 14 72 10
13 Ring-billed Gull 19 1.1 50 4
14 Double-crested Cormorant 33 5.5 54 4
16 Turkey Vulture 32 3.3 72 2
19 Mute Swan 60 26 94.8 8
20 Brown Pelican 54 7.6 90 4
21 Great Egret 38 3.3 67.2 3
10 rows selected.
Let’s issue a basic ROLLBACK command to see what happens. Take a minute to examine the following output from the query of the BIG_BIRDS table:
SQL> rollback;
Rollback complete.
SQL> select * from big_birds;
BIRD_ID BIRD_NAME HEIGHT WEIGHT WINGSPAN EGGS
---------- ------------------------------ ---------- ---------- ---------- --------
1 Great Blue Heron 52 5.5 78 5
3 Common Loon 36 18 54 2
4 Bald Eagle 37 14 84 2
5 Golden Eagle 40 15 90 3
7 Osprey 24 3 72 4
9 Canadian Goose 43 14 72 10
13 Ring-billed Gull 19 1.1 50 4
14 Double-crested Cormorant 33 5.5 54 4
16 Turkey Vulture 32 3.3 72 2
19 Mute Swan 60 26 94.8 8
20 Brown Pelican 54 7.6 90 4
21 Great Egret 38 3.3 67.2 3
12 rows selected.
After reviewing the output, you can see that the Great Blue Heron and Bald Eagle entries are both back in the BIG_BIRDS table. The ROLLBACK worked in this situation because a COMMIT was never performed after those records were deleted; the ROLLBACK thus negated any transactions that occurred since the last COMMIT.
Remember, the ROLLBACK command by itself rolls back to the last COMMIT or ROLLBACK statement.
The RELEASE SAVEPOINT Command
The RELEASE SAVEPOINT command removes a savepoint that you have created. After a savepoint has been released, you can no longer use the ROLLBACK command to undo transactions performed since the savepoint. You might want to issue a RELEASE SAVEPOINT command to avoid an accidental rollback to a savepoint that is no longer needed:
RELEASE SAVEPOINT savepoint_name;
Microsoft SQL Server does not support the RELEASE SAVEPOINT syntax; instead, all SAVEPOINTs are released when the transaction is completed, either by the COMMIT or the ROLLBACK of the transaction. Remember this point when you structure your transactions within your environment.
The SET TRANSACTION Command
You can use the SET TRANSACTION command to initiate a database transaction. This command specifies characteristics for the transaction that follows. For example, you can specify a transaction to be read-only or read/write:
SET TRANSACTION READ WRITE; SET TRANSACTION READ ONLY;
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 generating reports and 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 if multiple transactions are happening concurrently. If a transaction is READ ONLY, no locks are established by the database, thereby improving transaction performance.
Dealing with Poor Transactional Control
Poor transactional control can hurt database performance and even bring the database to a halt. Repeated poor database performance might result from a lack of transactional control during large inserts, updates, or deletes. Large batch processes also cause temporary storage for rollback information to grow until either a COMMIT or a ROLLBACK command is issued.
When a COMMIT is issued, rollback transactional information is written to the target table and the rollback information in temporary storage is cleared. When a ROLLBACK is issued, no changes are made to the database and the rollback information in the temporary storage is cleared. If neither a COMMIT nor ROLLBACK is issued, the temporary storage for rollback information continues to grow until no more space is left, thus forcing the database to stop all processes until space is freed. Although the database administrator (DBA) ultimately controls space usage, a lack of transactional control can still cause database processing to stop, sometimes forcing the DBA to take action that involves killing running user processes.
Summary
During this hour, you learned the preliminary concepts of transactional management through the use of three transactional control commands: COMMIT, ROLLBACK, and SAVEPOINT. You use COMMIT to save a transaction to the database. You use ROLLBACK to undo a transaction you performed. You use SAVEPOINT to break a transaction or transactions into groups, which enables you to roll back to specific logical points in transaction processing.
Remember that you should frequently use the COMMIT and ROLLBACK commands when running large transactional jobs, to keep space free in the database. Also keep in mind that these transactional commands are used only with the three DML commands (INSERT, UPDATE, and DELETE).
Q&A
Q. Is it necessary to issue a commit after every INSERT statement?
A. No, absolutely not. Some systems, such as SQL Server, automatically issue a commit after your INSERT statement. However, if you have large inserts or updates, you might consider doing them in batches; large updates to tables can negatively affect performance.
Q. How does the ROLLBACK command undo a transaction?
A. The ROLLBACK command clears all changes from the rollback area.
Q. If I issue a transaction and 99% of the transaction completes but the other 1% errs, can I redo only the error part?
A. No, the entire transaction must succeed; otherwise, data integrity is compromised. Therefore, you should always perform a ROLLBACK on an error unless there is a compelling reason not to.
Q. A transaction is permanent after I issue a COMMIT, but can I change data with an UPDATE command?
A. The word permanent in this case means that it is now a part of the database. You can always use the UPDATE statement to make modifications or corrections to the data.
Workshop
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. True or false: If you have committed several transactions, you have several more transactions that have not been committed, and you issue a
ROLLBACKcommand, all your transactions for the same session are undone.2. True or false: A
SAVEPOINTorSAVE TRANSACTIONcommand saves transactions after a specified number of transactions have executed.3. Briefly describe the purpose of each one of the following commands:
COMMIT,ROLLBACK, andSAVEPOINT.4. What are some differences in the implementation of transactions in Microsoft SQL Server?
5. What are some performance implications when using transactions?
6. When using several
SAVEPOINTorSAVE TRANSACTIONcommands, can you roll back more than one?
Exercises
1. For the following exercises, create the following tables based on the
BIRDSdatabase:Use the SQL statement
CREATE TABLE table_name AS SELECT...to create a table calledBIG_BIRDSthat is based on the originalBIRDStable. Include only the following columns in theBIG_BIRDStable:BIRD_ID,BIRD_NAME,HEIGHT,WEIGHT, andWINGSPAN. Use theWHEREclause to include only records for birds that have a wingspan greater than 48 inches.Create a table called
LOCATIONS2that is based on the originalLOCATIONStable.
2. Write a simple query to display all records in the
BIG_BIRDStable, to familiarize yourself with the data.3. Write a simple query to display all records in the
LOCATIONS2table, to familiarize yourself with the data.4. Modify the
BIG_BIRDStable to change the name of the columnWINGSPANtoAVG_WINGSPAN.5. Manually compute the average wingspan of birds in the
BIG_BIRDStable, and use theUPDATEstatement to update the value of all birds’ wingspans to the average wingspan value that you calculated.6. Issue the
ROLLBACKcommand.7. Query all data in the
BIG_BIRDStable using theSELECTstatement. You should see in the output from the query that all the values forWINGSPANhave been restored to their original values; however, the name of the column is still the updated value ofAVG_WINGSPAN.8. Why did the
ROLLBACKnegate the update to data values in theAVG_WINGSPANcolumn, but not theUPDATE TABLEstatement to rename theWINGSPANcolumn?9. Insert a new row of data into the
LOCATIONS2table for a location calledLake Tahoe.10. Issue the
COMMITcommand.11. Query the
LOCATIONS2table to verify the changes you made.12. Insert another new row of data into the
LOCATIONS2table for a location ofAtlantic Ocean.13. Create a
SAVEPOINTcalled SP1.14. Update the value of
Atlantic OceantoPacific Ocean.15. Create a
SAVEPOINTcalledSP2.16. Update the value of
Lake Tahoethat you previously added toLake Erie.17. Create a
SAVEPOINTcalledSP3.18. Issue the
ROLLBACKcommand back toSAVEPOINT SP2.19. Query the
LOCATIONS2table and study the behavior of theROLLBACKcommand toSAVEPOINT.20. Get creative with some transactions of your own on these two tables. Remember that these tables are copies of the original tables, so anything you do should not affect the original data. Also remember that, at any point during your progression through this book, you can rerun the scripts provided called
tables.sqland, subsequently,data.sqlto restore your tables and the data for theBIRDSdatabase back to its original state.