Managing Transaction Processing - MariaDB Crash Course - page 224

▼ 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 per-
formed to show that the table is not empty. Then a transaction is started, and
all the rows in ordertables 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.
Obviously, ROLLBACK can only be used within a transaction (after a START
TRANSACTION command has been issued).
Tip
Which Statements Can You Roll Back? Transaction processing is used to manage
INSERT , UPDATE , and DELETE statements. You cannot roll back SELECT state-
ments. (There would not be much point in doing so anyway.) You cannot roll back
CREATE or DROP operations. These statements may be used in a transaction block,
but if you perform a rollback they will not be undone.
Using COMMIT
MariaDB SQL statements are usually executed and written directly to the data-
base tables. This is known as an implicit commit —the commit (write or save)
operation happens automatically.
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;