-
using rollback
• Input
- SELECT * FROM ordertotals;
- START TRANSACTION;
- DELETE FROM ordertotals;
- SELECT * FROM ordertotals;
- ROLLBACK;
- 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.
-
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
- START TRANSACTION;
- 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).
-
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;
|
|
