What Is Transactional Control?

Team-Fly    

 
Sams Teach Yourself SQL in 24 Hours, Third Edition
By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour  6.  Managing Database Transactions


graphics/newterm_icon.gif

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.

graphics/note_icon.gif

Transactional control commands are only used with the DML commands INSERT, UPDATE, and DELETE. For example, you do not issue a COMMIT statement after creating a table. When the table is created, it is automatically committed to the database. Likewise, you cannot issue a ROLLBACK to replenish a table that was just dropped.


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.

graphics/06fig01.gif

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

 graphics/syntax_icon.gif graphics/mysql_icon.gif 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:

 graphics/input_icon.gif  SELECT * FROM PRODUCTS_TMP;  graphics/output_icon.gif 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.

 graphics/input_icon.gif  DELETE FROM PRODUCTS_TMP   WHERE COST < 14;  graphics/output_icon.gif 8 rows deleted. 

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

 graphics/mysql_icon.gif graphics/input_icon.gif  COMMIT;  graphics/output_icon.gif Commit complete. 
graphics/cautions_icon.gif

Frequent COMMITs in large loads or unloads of the database are highly recommended; however, too many COMMITs cause the job running to take a lot of extra time to complete. Remember that all changes are sent to the temporary rollback area first. 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.


graphics/note_icon.gif

In some implementations , transactions are committed without issuing the COMMIT commandinstead, merely signing out of the database causes a commit to occur.

MySQL does not currently support the COMMIT command. Remember this when performing hands-on exercises throughout the book.


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 :

 graphics/syntax_icon.gif graphics/mysql_icon.gif rollback [ work ]; 

Once again, as in the COMMIT statement, the WORK keyword is an optional part of the ROLLBACK syntax.

graphics/note_icon.gif

MySQL does not currently support the ROLLBACK command. Remember this when performing hands-on exercises throughout the book.


In the following example, you begin by selecting all records from the PRODUCTS_TMP table since the previous deletion of 14 records:

 graphics/input_icon.gif  SELECT * FROM PRODUCTS_TMP;  graphics/output_icon.gif 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:

 graphics/input_icon.gif  UPDATE PRODUCTS_TMP   SET COST = 39.99   WHERE PROD_ID = '11235';  graphics/output_icon.gif 1 row updated. 

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

 graphics/input_icon.gif  SELECT * FROM PRODUCTS_TMP;  graphics/output_icon.gif 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:

 graphics/mysql_icon.gif graphics/input_icon.gif  ROLLBACK;  graphics/output_icon.gif Rollback complete. 

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

 graphics/input_icon.gif  SELECT * FROM PRODUCTS_TMP;  graphics/output_icon.gif 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

 graphics/syntax_icon.gif graphics/mysql_icon.gif 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.

graphics/note_icon.gif

The SAVEPOINT name must be unique to the associated group of transactions. However, the SAVEPOINT can have the same name as a table or other object. Refer to specific implementation documentation for more details on naming conventions. Otherwise, SAVEPOINT names are a matter of personal preference, and used only by the database application developer to manage groups of transactions.

MySQL does not currently support the SAVEPOINT command. Remember this when performing hands-on exercises throughout the book.


The ROLLBACK TO SAVEPOINT Command

The syntax for rolling back to a SAVEPOINT is as follows:

 graphics/syntax_icon.gif graphics/mysql_icon.gif 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:

 graphics/mysql_icon.gif graphics/input_icon.gif  SAVEPOINT SP1;  graphics/output_icon.gif Savepoint created. graphics/input_icon.gif  DELETE FROM PRODUCTS_TMP WHERE PROD_ID = '11235';  graphics/output_icon.gif 1 row deleted. 
 graphics/mysql_icon.gif graphics/input_icon.gif  SAVEPOINT SP2;  graphics/output_icon.gif Savepoint created. graphics/input_icon.gif  DELETE FROM PRODUCTS_TMP WHERE PROD_ID = '90';  graphics/output_icon.gif 1 row deleted. 
 graphics/mysql_icon.gif graphics/input_icon.gif  SAVEPOINT SP3;  graphics/output_icon.gif Savepoint created. graphics/input_icon.gif  DELETE FROM PRODUCTS_TMP WHERE PROD_ID = '2345';  graphics/output_icon.gif 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:

 graphics/mysql_icon.gif graphics/input_icon.gif  ROLLBACK TO SP2;  graphics/output_icon.gif Rollback complete. 

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

 graphics/input_icon.gif  SELECT * FROM PRODUCTS_TMP;  graphics/output_icon.gif 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:

 graphics/mysql_icon.gif graphics/input_icon.gif  ROLLBACK;  graphics/output_icon.gif Rollback complete. graphics/input_icon.gif  SELECT * FROM PRODUCTS_TMP;  graphics/output_icon.gif 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.

 graphics/syntax_icon.gif graphics/mysql_icon.gif 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,

 graphics/mysql_icon.gif 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.


Team-Fly    
Top
 


Sams Teach Yourself SQL in 24 Hours
Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)
ISBN: 0672335417
EAN: 2147483647
Year: 2002
Pages: 275

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net