Updated June 5, 2023
Definition of MariaDB Transaction
MariaDB Transaction is defined as the process that comes for rescue. This transaction procedure permits a user to run a set of MariaDB operations to confirm that the database does not include the result of partial operations ever. In case, suppose we have a group of operations in the server, and one fails to operate, then in such a condition, the transaction rollback is implemented for restoring the database server to its original transactional state. If no error occurs, the entire set of statements will be committed to the database server.
Syntax:
Generally, MariaDB transactions is initiated by using the SQL statement as START TRANSACTION and is ended by using the COMMIT or ROLLBACK statements. START TRANSACTION is equivalent to BEGIN WORK, but it cannot be used within stored programs since the keywords BEGIN and END are reserved to define code blocks.
Therefore, the over-all syntax can be defined as follows:
START TRANSACTION;
{transactional_characteristic {, transactional_characteristic}…}
Transactional characteristic:{
WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY
}
BEGIN {WORK}
COMMIT {WORK} {AND {NO} CHAIN} {{NO} RELEASE}
ROLLBACK {WORK} {AND {NO} CHAIN} {{NO} RELEASE}
SET autocommit = (1, 0);These MariaDB statements are provided with a few helpful queries to manage the transactions explained below:
- For starting a transaction in MariaDB, firstly, we need to type the command statement as START TRANSACTION, where the aliases of this can be the BEGIN or the BEGIN WORK.
- Next, we can apply the command COMMIT, which helps to commit the present transaction in MariaDB and can make its alterations permanent.
- Again, similarly, we can use the ROLLBACK command that is responsible for rolling back the present transaction and canceling its alterations or changes.
- Also, we can apply the command as SET autocommit statement useful to enable or disable the auto-commit mode for the present transaction.
- By default, the MariaDB server automatically commits the changes permanently to the database. The user can also force the MariaDB server not to commit the modifications spontaneously by applying the succeeding query statements:
SET autocommit = 0;
Or,
SET autocommit = OFF;- In the same way, to enable the auto-commit mode, we can use the statements below explicitly as:
SET autocommit = 1;
Or,
SET autocommit = ON;- The parser treats the command BEGIN {WORK} for the beginning of the BEGIN…..END code block within entire stored programs such as stored events, functions, procedures, and triggers.
- The WORK keyword is optional and supports the methods of ROLLBACK and COMMIT in a transaction. Furthermore, the RELEASE and CHAIN clauses provide additional control over completing a transaction.
How does Transaction work in MariaDB?
- In MariaDB, a user applies a group of SQL query statements regulating and controlling server transactions. So, these transactions permit the user to start, commit, or roll back a MariaDB transaction, but these operations can be implicit in a few cases.
- You can set an isolation level in MariaDB to control the acquisition of transactional locks and the consistency of reads. In advance, a user can also state that a transaction is read-only, permitting InnoDB to run additional internal optimizations.
- The Transactions life cycle starts with the keyword START TRANSACTION and terminates with either the ROLLBACK or COMMIT command.
- It’s important to note that several APIs in MariaDB provide procedures to initiate transactions when writing client applications. You can use these procedures instead of sending a START TRANSACTION query statement directly from the client.
- In the field of transaction processing, there exists a significant concept known as the ACID properties. These properties are the four keys to perform transaction: Atomicity, Consistency, Isolation, and Durability. All changes made to the data execute as if they constitute a single operation.
- Also, these properties are useful for consistency in a database server before and after any transaction processing.
Examples
Let us now discuss the examples to demonstrate the MariaDB Transaction in the server explained as follows:
We will implement the transaction by taking two tables as, Orders and OrderInfo, created in the MariaDB database. Here, the orders table contains the fields as OrderNum, OrderDate, datereq, dateship, status & customernum, and the OrderInfo table contains the fields as OrderNum, procode, orderquantity, eachprice, and orderlinenum.
Let us view some demo contents inserted in both tables as follows:
Orders:
select * from orders;
OrderInfo:
select * from orderinfo;
Firstly, we will start the transaction using the command START TRANSACTION. After that, we will select the newest sale order number from the table Orders and then apply the subsequent sale order number, referred to as the fresh sale order number.
Next, we will add a new sale order to the table orders. Following to it, again, we will enter the sale order items into the table OrderInfo. And then lastly, we will commit the whole transaction by means of the COMMIT command.
Optionally, we can choose data rows from both the tables, i.e., orders and orderinfo, in order to check the fresh sale order. Now, perform the script code below to execute the above actions:
START TRANSACTION;
SELECT
@OrderNum:=MAX(OrderNum)+1
FROM
Orders;
INSERT INTO Orders(OrderNum, OrderDate,datereq,dateship,status,customernum) VALUES(@OrderNum, '2020-11-08','2020-12-10','2021-02-02','In Process',125);
INSERT INTO OrderInfo(OrderNum,procode,orderquantity,eachprice,orderlinenum) VALUES(@OrderNum, 'P12_100',10,150,1), (@OrderNum, 'P12_110',20,200,2);
COMMIT;Output:
Now, to fetch the newly constructed sale order, we will run the query as follows:
SELECT a.OrderNum,OrderDate,datereq,dateship,status,customernum,orderlinenum,procode,orderquantity,eachprice FROM Orders a
INNER JOIN
OrderInfo b USING (OrderNum)
WHERE
a.OrderNum = 113;Hence, you can view the output as:
Conclusion
- MariaDB Transaction can be said to be SQL transaction processing, which signifies a series of executions of SQL query statements which is atomic with admiration to recovery.
- This denotes that in the server with the MariaDB Transaction, either the query execution output will be completely successful one, or it may not affect any SQL data or SQL schemas. Stored triggers or functions in MariaDB cannot implement transactions.
Recommended Articles
We hope that this EDUCBA information on “MariaDB Transaction” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
