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 Durability) −

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

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

  • Isolation − It enables independent transactions operation of transactions.

  • Durability − 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 implicit commit, and they also cause an error when used within transactions. Examples of such statements include, but are not limited to CREATE, ALTER, and DROP.

MariaDB transactions also include options like SAVEPOINT and LOCK TABLES. SAVEPOINT sets a restore point to utilize with ROLLBACK. LOCK TABLES allows controlling 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 explicit 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;
Advertisements