MariaDB transactions

Transactions are sequential group operations. T hey run as a single cell and are not terminated until all operations in the group are successfully executed. A single failure in a group causes the entire transaction to fail and causes no impact on the database.

Transaction compliant with ACID (atomicity, consistency, isolation and durability) -

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

• Consistency - It ensures that the database applies changes to successful transactions.

• Isolation - It enables a transaction to operate independently of a transaction.

• Persistence - It ensures the persistence of successful transactions in the event of a system failure.

The transaction statement begins with the START TRANSACTION statement, followed by the COMMIT and ROLLBACK statements -

• START TRANSACTION - Start transaction.

• COMMIT - Saves changes to the data.

• ROLLBACK - End the transaction and destroy any changes.

COMMIT is executed when the transaction succeeds and ROLLBACK is executed when the transaction fails.

Note - Some statements result in implicit commits and errors when used in transactions. E xamples of such statements include, but are not limited to, CREATE, ALTER, and DROP.

MariaDB transactions also include options such as SAVEPOINT and LOCK TABLES. S AVEPOINT sets a recovery point to take advantage of ROLLBACK. L OCK TABLES allows access to tables to be controlled during a session to prevent modifications during certain time periods.

AutoCOMMIT variables provide control over transactions. S etting to 1 forces all operations to be considered successful transactions, and setting 0 causes persistent changes to occur only on explicit COMMIT statements.

The structure of the transaction

The general structure of transaction statements includes starting with START TRANSACTION. T he next step is to insert one or more commands/actions, insert statements to check for errors, insert ROLLBACK statements to manage any errors found, and finally insert COMMIT statements to apply changes to successful operations.

See the example given below -

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