Managing Transaction Processing - MariaDB Crash Course - page 225

▼ 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 COMMIT statement 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).
Note
Implicit Transaction Closes After a COMMIT or ROLLBACK statement has been exe-
cuted, the transaction is automatically closed (and future changes will implicitly commit).
Using Savepoints
Simple ROLLBACK and COMMIT statements enable you to write or undo an
entire transaction. Although this works for simple transactions, more complex
transactions might require partial commits or rollbacks.
For example, the process of adding an order described previously is a single
transaction. If an error occurs, you want to roll back only to the point before
the orders row was added. You do not want to roll back the addition to the
customers table (if there was one).
To support the rollback of partial transactions, you must be able to put place-
holders 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 SAVEPOINT
statement, as follows:
▼ Input
SAVEPOINT delete1;
Each savepoint takes a unique name that identifies it so that, when you roll
back, MariaDB knows where you are rolling back to. To roll back to this save-
point, do the following:
▼ Input
ROLLBACK TO delete1;