MariaDB Transaction – Helpful Guide

In this MariaDB tutorial, we will learn about MariaDB Transaction. Additionally, we will cover the following topics.

• MariaDB Transaction
• MariaDB Transaction Rollback Example
• MariaDB Transaction Log
• MariaDB Transaction Isolation Level
• MariaDB Transaction Timeout
• MariaDB Transaction Rollback On Error
• MariaDB Transactional DDL
• MariaDB Transaction Example
• MariaDB Transaction Alter Table
• MariaDB Statements That Cause An Implicit Commit
• MariaDB Transaction Autocommit
• MariaDB Kill Transaction
• MariaDB Transaction Query

MariaDB Transaction

Here, we will learn about the basics of MariaDB Transaction.

MariaDB Transactions are a sequence of operations performed in a group. Each group works separately, and will not terminate until its operations are complete.

A single failure within the group will fail the entire transaction, which will not affect the database.

A database maintains consistency by following certain properties before and after a transaction. These properties are called ACID properties.

• Atomicity – It guarantees the success of all operations by aborting and rolling back changes in the event of failure.
• Consistency – In this way, the database ensures that changes are applied on a successful transaction.
• Isolation – It enables independent transaction operations.
• Durability – It ensures that even the failure of the system will not affect the persistence of the transaction.

A MariaDB Transaction goes through various stages, starting with the START TRANSACTION or BEGIN statement followed by COMMIT and ROLLBACK statements.

• START TRANSACTION/BEGIN – Initiates a transaction
• COMMIT – Saves the changes on the data durig the transaction
• ROLLBACK – Ends the transaction while destroying all the changes.

Note – If the transaction is successful, then COMMIT acts, and in case of failure of a transaction, ROLLBACK acts.

Transactions in MariaDB can also be performed with options such as SAVEPOINT and LOCK TABLES.

• SAVEPOINT creates a point to restore to with ROLLBACK.
• LOCK TABLES prevents modifications to tables during sessions.

The general structure of a transaction consists of the following things.

• Begins with START TRANSACTION/BEGIN statement
• Inserting one or more commands/operations
• Inserting statements for checking errors
• Inserting ROLLBACK statements in case of error being discovered.
• Finally COMMIT statement to apply changes to successful operations.

Read: MariaDB Union Operator

MariaDB Transaction Rollback Example

Here, we will learn about MariaDB ROLLBACK, its syntax, and an example

Rollback in MariaDB is the process of undoing transactions that have not been saved in the database. It can only undo the changes since the last commit.

The syntax of the Rollback Statement in MariaDB is given below.

ROLLBACK;

MariaDB ROLLBACK has no parameters or arguments in its syntax.

It is mostly used after a set of statements leading to changes in the data since the last commit.

Example illustrating the working of ROLLBACK statement in MariaDB.

The table used i.e. cities before any changes are shown below.

START TRANSACTION;
DELETE FROM cities
WHERE city_id > 5;

Here, we are removing the data of the cities table using the DELETE statement where city_id is greater than 5 in MariaDB.

After running the DELETE Statement on the cities table in MariaDB.

Here, we can see that the records of the cities table have been removed in MariaDB.

Now, we will run MariaDB ROLLBACK STATEMENT to see what happens.

ROLLBACK;

Here, you can see that the records of the cities table which was previously deleted are again present in the cities table in MariaDB.

Read: MariaDB Select Statement

MariaDB Transaction Log

In this, we will learn about the MariaDB Transaction Log, how to enable it and display it on MariaDB Client-Server.

To view all the Transactions performed in MariaDB, you need to view the General Query Log which contains the log of each and every MariaDB query.

The General query log is a collection of each and every MariaDB Query received from the client, as well as each client connect and disconnect. It grows quickly as it is a record of every query received by the server.

The general query log is disabled by default. To enable the general query log, set the general_log to 1 using SET GLOBAL.

Before setting general_log to 1, let us check the general query log by typing the below command.

SELECT * FROM mysql.general_log\G

To get the log of all transactions, you need to type the following command.

SET GLOBAL general_log=1;

This is how you can enable the general query log but this can only be disabled for a connection by a user with super privilege by changing the SQL_LOG_OFF variable to 1.

After setting the value of general_log to 1, we need to select the General Query Log Output Destination.

The General Log Query can be written in 2 ways.

• Writing General Log Query to a file
• Writing General Log Query to a table

Writing General Log Query to a file

By default, the general log query is output to a file. Although, it can be changed dynamically by setting the log_output system variable to FILE using SET GLOBAL.

To change the General Log Output Destination to a FILE, you need to type the following command.

SET GLOBAL log_output='FILE';

Writing General Log Query to a table

By default, the general log query is output to a file. Although, it can be changed dynamically by setting the log_output system variable to TABLE using SET GLOBAL.

To change the General Log Output Destination to a TABLE, you need to type the following command.

SET GLOBAL log_output='TABLE';

After setting the General Log Output Destination to either a FILE or TABLE, you need to type the following command to get all the queries in a log.

SELECT * FROM mysql.general_log\G

Here, you can find the log of all MariaDB queries which contains the log of all MariaDB transactions too.

This is how we can get the MariaDB Transaction Log.

Read: MariaDB Join with Examples

MariaDB Transaction Isolation Level

Here, we will learn about MariaDB Transaction Isolation Level, its types with an example.

A transaction’s isolation level determines how it is isolated from other transactions. It is the process of separating data or resource modifications carried out by different transactions. There are several levels of isolation that allow concurrency side effects, such as dirty reads or phantom reads.

According to the SQL standard, there are four isolation levels.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

In addition, the isolation level implemented in MySQL and the isolation level implemented in SQL Server are somewhat different at the same level.

The default isolation level in MariaDB is a repeatable read. Transaction isolation is achieved by locks, which is the isolation level in the transaction characteristics (ACID).

Read Uncommitted – Transactions in MariaDB at this isolation level can see the execution results of other uncommitted transactions. Since its performance is not much better than other levels, this isolation level is rarely used in practical applications. Uncommitted data is also known as dirty data.

Read Committed – Most database systems (except MySQL) use this level of isolation by default. In other words, it satisfies the simple definition of isolation: a transaction can only see changes made by a committed transaction.

A transaction only sees changes made to it by a committed transaction, because the transaction satisfies the simple definition of isolation. It also supports the so-called Nonrepeatable Read, since other instances of the same transaction may have new commits during that instance’s processing, so the same selection may produce different results.

Repeatable Read – MySQL’s default transaction isolation level guarantees that multiple instances of the same transaction will see the same row of data when reading data concurrently. Theoretically, though, this leads to a second thorny problem i.e. Phantom Reading.

The term ‘hallucination‘ refers to causing the user to find “hallucination” rows after reading a range of data rows, another transaction inserting new rows, and the user re-reading the range of data rows. With MVCC (Multiversion Concurrency Control), InnoDB and Falcon solve this problem.

Serializable – In the highest isolation level, transactions must be sorted to make it impossible to conflict with each other. This solves the hallucination problem. Each row of reading data is locked with a shared lock. It may lead to a large number of timeouts and lock competitions at this level.

Here, we have learned about MariaDB Transaction Isolation Level, its types with an example.

Read: MariaDB Select Into

MariaDB Transaction Timeout

Here, we will learn about the basics of MariaDB Transaction Timeout.

In MariaDB, there are settings to manage wait_timeout and interactive_timeout, which close connections after a set amount of inactivity.

wait_timeout – When a connection is active, the server waits for it to become active before closing it. An interactive connection’s session will be initialized either by the global value or by the interactive_timeout value, when it starts, depending on whether the connection is non-interactive in MariaDB.

interactive_timeout – Before closing an interactive connection (a connection using mysql_real_connect() CLIENT_INTERACTIVE option), the server waits for it to become active for a specific number of seconds in MariaDB.

These are, however, set to have a long waiting period by default. A shorter timeout and more granular control could be beneficial in cases where transactions may be started but not committed or rolled back so that locks are not held for an excessive amount of time.

Three new variables were introduced by MariaDB to deal with this situation.

• idle_transaction_timeout – for all transactions
• idle_write_transaction_timeout –  write transactions called idle_readwrite_transaction_timeout.
• idle_readonly_transaction_timeout – for read transactions

idle_transaction_timeout – Waiting time in seconds for the server to kill the connection when there are no transactions. If it is set to zero by default, then the connections are never killed.

An example for MariaDB Transaction idle_transaction_timeout is given below.

SET SESSION idle_transaction_timeout = 60;
BEGIN;
USE US_data;
Select * from cities;

##wait for 61 seconds

Select * from cities; 

Here, we are providing the idle_transaction_timeout a certain value and performing some operations, and again waiting for some duration that is greater than the idle_transaction_timeout and displaying the result as given below.

idle_write_transaction_timeout – When idle read-write transactions exist, the server waits for them for a certain amount of time before killing the connection. If it is set to zero by default, then the connections are never killed.

An example for MariaDB Transaction idle_transaction_timeout is given below.

SET SESSION idle_write_transaction_timeout = 60;
BEGIN;
SELECT * FROM cities; 

##wait for 61 seconds

INSERT INTO cities(cname) VALUES
   ('New Jersey')

SELECT * FROM cities; 

##wait for 61 seconds

SELECT * FROM cities; 

Here, we are providing the idle_write_transaction_timeout a certain value and performing some operations, and again waiting for some duration that is greater than the idle_write_transaction_timeout and displaying the result as given below.

idle_readonly_transaction_timeout – The number of seconds the server waits for idle read-only transactions before killing the connection. If it is set to zero by default, then the connections are never killed.

An example for MariaDB Transaction idle_transaction_timeout is given below.

SET SESSION idle_readonly_transaction_timeout = 30;
BEGIN;
Select * from cities;

##wait for 31 seconds

Select * from cities; 

Here, we are providing the idle_readonly_transaction_timeout a certain value and performing some operations, and again waiting for some duration that is greater than the idle_readonly_transaction_timeout and displaying the result as given below.

Here, we have learned about the basics of MariaDB Transaction Timeout.

Read: MariaDB Case Statement

MariaDB Transaction Error On Rollback

Here, we will learn about MariaDB Transaction Error On Rollback.

Error while performing a Rollback in MariaDB can only happen because of two reasons given below.

• Commit Has Been Performed
• Rollback On Statements Which Causes Implicit Commit

Commit Has Been Performed – During a transaction, if we commit the changes done on a MariaDB transaction then we can’t perform a rollback on it. Even if we try to rollback the changes, it would result in it in an error or unexpected results.

Example for Rollback after the commit has been performed.

INSERT INTO cities(state_name) VALUES
    -> ('California');

SELECT * FROM cities;

Here, we are inserting the records in a table i.e. ‘California‘ in the state_name column in the cities table.

Now, we will try to rollback the changes using the below statement in MariaDB.

ROLLBACK;

SELECT * FROM cities;

Here, we can see that even after the Rollback Statement, there are no changes as the commit has been already performed in MariaDB.

Rollback On Statements Which Causes Implicit Commit – During a transaction if we make the changes using the statements that cause an implicit commit on a MariaDB transaction then we can’t perform a rollback on it. Even if we try to rollback the changes, it would result in it in an error or unexpected results.

Example for ALTER TABLE illustrating the implicit commit property.

ALTER TABLE cities
     DROP COLUMN city_id;

Here, we are removing one of the columns from a table i.e. city_id from the cities table.

Now, we will try to rollback the changes using the below statement.

ROLLBACK;

Here, we have learned about MariaDB Transaction Error On Rollback.

Read: MariaDB Window functions

MariaDB Transactional DDL

Here, we will learn about the MariaDB Transaction DDL.

The data definition language (DDL) statements are used to describe the database structure. A data definition language describes how data should exist in the database, so statements like CREATE TABLE or ALTER TABLE fall under the DDL.

DDL commands include CREATE, ALTER, and DROP statements. These commands are used to CREATE, ALTER, OR DROP the database objects (Tables, Views, Users).

Most of the DDL statements are implicitly committed at the end of any statement or after the execution of a statement.

Here, the DDL statements list implicitly ends any transaction that is active in the current session, just as if you had committed the session before executing the statement.

Most of these statements cause implicit commits after execution. The intention is to handle each such statement as a separate transaction.

Data definition language (DDL) statements that define or modify database objects.

S. No.	Statements	Description
1.	ALTER EVENT	Modifies the characteristics of an existing event in MariaDB.
2.	ALTER FUNCTION	Changes the characteristics of a stored function in MariaDB.
3.	ALTER PROCEDURE	Changes the characteristics of a stored procedure in MariaDB.
4.	ALTER SERVER	Changes/modifies the server information in MariaDB.
5.	ALTER TABLE	Used to add, drop/delete, or modify columns in a table in MariaDB.
6.	ALTER TABLESPACE	Used to add a new data file or drop a data file from a tablespace in MariaDB.
7.	ALTER VIEW	Modifies the definition of an existing view in MariaDB.
8.	CREATE DATABASE	Used to create a new database in MariaDB.
9.	CREATE EVENT	Used to create a new event in MariaDB.
10.	CREATE FUNCTION	Used to create a new stored function in MariaDB.
11.	CREATE INDEX	Used to create a new index in MariaDB.
12.	CREATE PROCEDURE	Used to create a new stored Procedure in MariaDB.
13.	CREATE ROLE	Creates one or more roles in MariaDB.
14.	CREATE SERVER	Used to create an entry in mysql.servers table in MariaDB.
15.	CREATE SPATIAL REFERENCE SYSTEM	Used to create Spatial Reference System in MariaDB.
16.	CREATE TABLE	Used to create a new table in a database in MariaDB.
17.	CREATE TABLESPACE	Used to create a new tablespace in MariaDB.
18.	CREATE TRIGGER	Used to create triggers in MariaDB.
20.	CREATE VIEW	Used to create a view of a table in MariaDB.
21.	DROP DATABASE	Used to drop all tables in a particular database and delete the database in MariaDB.
22.	DROP EVENT	Used to drop or delete events completely in MariaDB.
23.	DROP FUNCTION	Used to drop a stored function in MariaDB.
24.	DROP INDEX	Used to drop/delete completely an index.
25.	DROP PROCEDURE	Used to drop a stored procedure in MariaDB.
26.	DROP ROLE	Used to drop an already existing role in MariaDB.
27.	DROP SERVER	Used to drop a server in MariaDB.
28.	DROP TABLE	Used to drop a table from a database in MariaDB.
29.	DROP TABLESPACE	Used to drop an already existing tablespace in MariaDB.
30.	DROP TRIGGER	Used to drop an already existing trigger in MariaDB.
31.	DROP VIEW	Used to drop a view of a table in MariaDB.
32.	INSTALL PLUGIN	Used to install an individual plugin from a specified library in MariaDB.
33.	RENAME TABLE	Used to change the name of a table in MariaDB.
34.	TRUNCATE TABLE	Used to delete all rows from a table similar to Delete statement without Where clause in MariaDB.
35.	UNINSTALL PLUGIN	Used to install an already installed individual plugin in MariaDB.

Example for ALTER TABLE illustrating the implicit commit property.

ALTER TABLE cities
    -> DROP COLUMN city_id;

Here, we are removing one of the columns from a table i.e. city_id from the cities table.

Now, we will try to rollback the changes using the below statement.

ROLLBACK;

If the TEMPORARY keyword is used with CREATE TABLE and DROP TABLE statements, no transaction is committed. But with temporary tables, other operations such as ALTER TABLE and CREATE INDEX do cause a commit.

In addition to the absence of implicit commits, such statements cannot be rolled back, resulting in a violation of transactional atomicity.

For example, if you create a temporary table and roll back the transaction, the table remains in existence.

Here, we have learned about the MariaDB Transaction DDL.

Read: MariaDB Row_Number Tutorial

MariaDB Transaction Example

Here, we will learn about the MariaDB Transaction, syntax, and an example.

A MariaDB Transaction goes through various stages, starting with the START TRANSACTION or BEGIN statement followed by COMMIT and ROLLBACK statements.

• START TRANSACTION/BEGIN – Initiates a transaction
• COMMIT – Saves the changes on the data durig the transaction
• ROLLBACK – Ends the transaction while destroying all the changes.

Note – If the transaction is successful, then COMMIT acts, and in case of failure of a transaction, ROLLBACK acts.

Transactions in MariaDB can also be performed with options such as SAVEPOINT and LOCK TABLES.

• SAVEPOINT creates a point to restore to with ROLLBACK.
• LOCK TABLES prevents modifications to tables during sessions.

The general structure of a transaction consists of the following things.

• Begins with START TRANSACTION/BEGIN statement
• Inserting one or more commands/operations
• Inserting statements for checking errors
• Inserting ROLLBACK statements in case of error being discovered.
• Finally COMMIT statement to apply changes to successful operations.

Usually, MariaDB Transaction is initiated using the START TRANSACTION statement in MariaDB. Likewise, BEGIN WORK is an alternate keyword for START TRANSACTION. If the transaction is successful, then COMMIT acts, and in case of failure of a transaction, ROLLBACK acts.

The overall syntax can therefore be written as follows.

START TRANSACTION;
{trans_charact {, trans_charact}…}
Trans charact:{

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);

Here:

• MariaDB START TRANSACTION, where the synonym of the keyword can be BEGIN WORK.
• COMMIT command to save the current MariaDB transaction and make the changes permanent.
• ROLLBACK command to rollback the current transaction and undo its changes.
• SET autocommit to activate or deactivate the mode of auto-commit for the current transaction.
• By default, MariaDB server automatically commits changes to the database and permanently.
• The parser interprets the BEGIN [WORK] command as the initial part of the BEGIN…END code blo