| 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. | 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. 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. | 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. | | 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 : rollback [ work ]; Once again, as in the COMMIT statement, the WORK keyword is an optional part of the ROLLBACK syntax. | 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: 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 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: 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. |