CC SQL 26-managing transaction processing

Card Set Information

CC SQL 26-managing transaction processing
2012-04-09 17:33:17
CC SQL 26 managing transaction processing

CC SQL 26-managing transaction processing
Show Answers:

  1. using rollback
    • Input

    • SELECT * FROM ordertotals;
    • DELETE FROM ordertotals;
    • SELECT * FROM ordertotals;
    • SELECT * FROM ordertotals;

    • Analysis

    This example starts by displaying the contents of the ordertotals table (this table was populated in Chapter 24, "Using Cursors"). First a SELECT is performed to show that the table is not empty. Then a transaction is started, and all of the rows inordertables are deleted with a DELETE statement. Another SELECT verifies that, indeed, ordertotals is empty. Then a ROLLBACK statement is used to roll back all statements until the START TRANSACTION, and the final SELECT shows that the table is no longer empty.
  2. using commit
    Within a transaction block, however, commits do not occur implicitly. To force an explicit commit, the COMMIT statement is used, as seen here:

    • Input

    • DELETE FROM orderitems WHERE order_num = 20010;
    • DELETE FROM orders WHERE order_num = 20010;
    • COMMIT;

    • Analysis

    In this example, order number 20010 is deleted entirely from the system. Because this involves updating two database tables, orders and orderitems, a transaction block is used to ensure that the order is not partially deleted. The final COMMITstatement writes the change only if no error occurred. If the first DELETE worked, but the second failed, the DELETE would not be committed (it would effectively be automatically undone).
  3. using save point
    For example, the process of adding an order described previously is a single transaction. If an error occurs, you only want to roll back to the point before theorders row was added. You do not want to roll back the addition to the customerstable (if there was one).

    To support the rollback of partial transactions, you must be able to put placeholders at strategic locations in the transaction block. Then, if a rollback is required, you can roll back to one of the placeholders.

    • These placeholders are called savepoints, and to create one use the SAVEPOINTstatement, as follows:
    • • Input

    SAVEPOINT delete1;

    Each savepoint takes a unique name that identifies it so that, when you roll back, MySQL knows where you are rolling back to. To roll back to this savepoint, do the following:

    • Input

    ROLLBACK TO delete1;