Sams Teach Yourself SQL in 24 Hours

Recommended Book 

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.

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.

   

   DELETE FROM PRODUCTS_TMP

   WHERE COST < 14;

   
8 rows deleted.

A COMMIT statement is issued to save the changes to the database, completing the transaction.

   
   

   COMMIT;

   
Commit complete.

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:

   
   
rollback [ work ];

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:

   

   SELECT * FROM PRODUCTS_TMP;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
90         LIGHTED LANTERNS                14.5
2345       OAK BOOKSHELF                  59.99

3 rows selected.

Next, you update the table, changing the product cost to $39.99 for the product identification number 11235:

   

   UPDATE PRODUCTS_TMP

   SET COST = 39.99

   WHERE PROD_ID = '11235';

   
1 row updated.

If you perform a quick query on the table, the change appears to have occurred:

   

   SELECT * FROM PRODUCTS_TMP;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                39.99
90         LIGHTED LANTERNS                14.5
2345       OAK BOOKSHELF                  59.99

3 rows selected.

Now, issue the ROLLBACK statement to undo the last change:

   
   

   ROLLBACK;

   
Rollback complete.

Finally, verify that the change was not committed to the database:

   

   SELECT * FROM PRODUCTS_TMP;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
90         LIGHTED LANTERNS                14.5
2345       OAK BOOKSHELF                  59.99

3 rows selected

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

   
   
SAVEPOINT SAVEPOINT_NAME

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:

   
   
ROLLBACK TO SAVEPOINT_NAME; 

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:

   
   

   SAVEPOINT SP1;

   
Savepoint created.


   DELETE FROM PRODUCTS_TMP WHERE PROD_ID = '11235';

   
1 row deleted.

   
   

   SAVEPOINT SP2;

   
Savepoint created.


   DELETE FROM PRODUCTS_TMP WHERE PROD_ID = '90';

   
1 row deleted.

   
   

   SAVEPOINT SP3;

   
Savepoint created.


   DELETE FROM PRODUCTS_TMP WHERE PROD_ID = '2345';

   
1 row deleted.

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:

   
   

   ROLLBACK TO SP2;

   
Rollback complete.

Notice that only the first deletion took place since you rolled back to SP2:

   

   SELECT * FROM PRODUCTS_TMP;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ -----
90         LIGHTED LANTERNS                14.5
2345       OAK BOOKSHELF                  59.99

2 rows selected.

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:

   
   

   ROLLBACK;

   
Rollback complete.


   SELECT * FROM PRODUCTS_TMP;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
90         LIGHTED LANTERNS                14.5
2345       OAK BOOKSHELF                  59.99

3 rows selected.

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.

   
   
RELEASE SAVEPOINT SAVEPOINT_NAME;

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,

   
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 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.