• Choose a category
• All
• Classic-Fiction

• Transaction—A block of SQL statements

• Rollback—The process of undoing specified SQL statements

• Commit—Writing unsaved SQL statements to the database tables

• Savepoint—A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)

Controlling Transactions

Now that you know what transaction processing is, let’s look at what is involved in managing transactions.

The key to managing transactions involves breaking your SQL statements into logical chunks and explicitly stating when data should be rolled back and when it should not.

The MariaDB statement used to mark the start of a transaction is

Input

START TRANSACTION

Using ROLLBACK

The MariaDB ROLLBACK command is used to roll back (undo) MariaDB statements, as seen in this next statement:

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 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 statements. (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 database 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;

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 executed, 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 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 SAVEPOINT statement, as follows:

Input

SAVEPOINT delete1;

Each savepoint takes a unique name that identifies