MariaDB - Transactions

Transactions are sequential group operations. They function as a single unit, and do not terminate until all operations within the group execute successfully. A single failure in the group causes the entire transaction to fail, and causes it to have no impact on the database.

Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durabipty) −

Atomicity − It ensures the success of all operations by aborting on failures and rolpng back changes.

Consistency − It ensures the database apppes changes on a successful transaction.

Isolation − It enables independent transactions operation of transactions.

Durabipty − It ensures the persistence of a successful transaction in the event of system failure.

At the head of a transaction statement is the START TRANSACTION statement followed by COMMIT and ROLLBACK statements −

START TRANSACTION begins the transaction.

COMMIT saves changes to data.

ROLLBACK ends the transaction, destroying any changes.

On a successful transaction, COMMIT acts. On a failure, ROLLBACK acts.

Note − Some statements cause an imppcit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not pmited to CREATE, ALTER, and DROP.

MariaDB transactions also include options pke SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utipze with ROLLBACK. LOCK TABLES allows controlpng access to tables during sessions to prevent modifications during certain time periods.

The AUTOCOMMIT variable provides control over transactions. A setting of 1 forces all operations to be considered successful transactions, and a setting of 0 causes persistence of changes to only occur on an exppcit COMMIT statement.

Structure of a Transaction

The general structure of a transaction statement consists of beginning with START TRANSACTION. The next step is inserting one or more commands/operations, inserting statements that check for errors, inserting ROLLBACK statements to manage any errors discovered and finally inserting a COMMIT statement to apply changes on successful operations.

Review the example given below −

START TRANSACTION;
SELECT name FROM products WHERE manufacturer =  XYZ Corp ;
UPDATE spring_products SET item = name;
COMMIT;