MariaDB Transactions - MariaDB

What are Transactions in MariaDB?

MariaDB transactions are subsequent group operations which functions like a single unit. These transactions do not exist until all operations within the group execute successfully. Total transaction will be failed if a single failure occurs in the group then the entire transaction fails and causes it to have no impact on the database.
Transactions conform to ACID (Atomicity, Consistency, Isolation, and Durability) −
  • Atomicity − It make sure that the success of all operations by aborting on failures and rolling back changes.
  • Consistency − It make sure that the database applies changes on a successful transaction.
  • Isolation − It supports independent transactions operation of transactions.
  • Durability − It make sure that 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 like CREATE, ALTER, and DROP cause an implicit commit, and they also cause an error when used within transactions.
Some specified options like SAVEPOINT and LOCK TABLES included in MariaDB transactions.
  • SAVEPOINT is used to restore point to utilize with ROLLBACK.
  • LOCK TABLES worked as a 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.

What is the structure of a Transaction?

Usually, a transaction statement begins with START TRANSACTION. The next step followed by inserting one or more commands/operations. After that inserting statements will 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 –

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

MariaDB Topics