MySQL Transaction

A transaction in MySQL is a sequential group of statements, queries, or operations such as select, insert, update or delete to perform as a one single work unit that can be committed or rolled back. If the transaction makes multiple modifications into the database, two things happen:

Either all modification is successful when the transaction is committed.
Or, all modifications are undone when the transaction is rollback.

In other words, a transaction cannot be successful without completing each operation available in the set. It means if any statement fails, the transaction operation cannot produce results.

A transaction in MySQL starts with the first executable SQL statement and ends when it finds a commit or rolled back either explicitly or implicitly. It explicitly uses COMMIT or ROLLBACK statement and implicitly when a DDL statement is used.

Let us understand the concept of a transaction through the following explanation.

We can understand the concept of a transaction in MySQL by considering a banking database. Suppose a bank customer wants to transfer money from one account to another account. We can achieve this by using the SQL statements that will be divided into the following steps:

First, it is required to check the availability of the requested amount in the first account.
Next, if the amount is available, deduct it from the first account. Then, update the first account.
Finally, deposit the amount in the second account. Then update the second account to complete the transaction.
If any of the above processes fails, the transaction will be rolled back into its previous state.

Properties of Transaction

The transaction contains mainly four properties, which referred to as ACID property. Now, we are going to discuss the ACID property in detail. The ACID property stands for:

Atomicity
Consistency
Isolation
Durability

Atomicity: This property ensures that all statements or operations within the transaction unit must be executed successfully. Otherwise, if any operation is failed, the whole transaction will be aborted, and it goes rolled back into their previous state. It includes features:

COMMIT statement.
ROLLBACK statement.
Auto-commit setting.
Operational data from the INFORMATION_SCHEMA tables.

Consistency: This property ensures that the database changes state only when a transaction will be committed successfully. It is also responsible for protecting data from crashes. It includes features:

InnoDB doublewrite buffer.
InnoDB crash recovery.

Isolation: This property guarantees that each operation in the transaction unit operated independently. It also ensures that statements are transparent to each other. It includes features:

SET ISOLATION LEVEL statement.
Auto-commit setting.
The low-level details of InnoDB locking.

Durability: This property guarantees that the result of committed transactions persists permanently even if the system crashes or failed. It includes features:

Write buffer in a storage device.
Battery-backed cache in a storage device.
Configuration option innodb_file_per_table.
Configuration option innodb_flush_log_at_trx_commit.
Configuration option sync_binlog.

MySQL Transaction Statement

MySQL control transactions with the help of the following statement:

MySQL provides a START TRANSACTION statement to begin the transaction. It also offers a "BEGIN" and "BEGIN WORK" as an alias of the START TRANSACTION.
We will use a COMMIT statement to commit the current transaction. It allows the database to make changes permanently.
We will use a ROLLBACK statement to roll back the current transaction. It allows the database to cancel all changes and goes into their previous state.
We will use a SET auto-commit statement to disable/enable the auto-commit mode for the current transaction. By default, the COMMIT statement executed automatically. So if we do not want to commit changes automatically, use the below statement:

SET autocommit = 0;
OR,
SET autocommit = OFF:

Again, use the below statement to enable auto-commit mode:

SET autocommit = 1;
OR,
SET autocommit = ON:

MySQL Transaction Example

Suppose we have two tables named "employees" and "Orders" that contains the following data:

Table: employees

Table: orders

COMMIT Example

If we want to use a transaction, it is required to break the SQL statements into logical portions. After that, we can define whether the data should be committed or rollback.

The following steps illustrate to create a transaction:

Begin the transaction using the START TRANSACTION statement.
Then, select maximum income among the employee.
Add a new record to the employee table.
Add a new record into the order table.
Use the COMMIT statement to complete the transaction.

Below are the commands that perform the above operations:

-- 1. Start a new transaction

START TRANSACTION;

-- 2. Get the highest income

SELECT @income:= MAX(income) FROM employees;

-- 3. Insert a new record into the employee table

INSERT INTO employees(emp_id, emp_name, emp_age, city, income) 
VALUES (111, 'Alexander', 45, 'California', 70000);

-- 4. Insert a new record into the order table

INSERT INTO Orders(order_id, prod_name, order_num, order_date) 
VALUES (6, 'Printer', 5654, '2020-01-10');

-- 5. Commit changes    
COMMIT;

The below image explains it more clearly:

ROLLBACK Example

We can understand the rollback transaction with the help of the following illustration. First, open the MySQL command prompt and log into the database server using the password. Next, we have to select a database.

Suppose our database contains the "Orders" table. Now, the following are the scripts that perform the rollback operations:

-- 1. Start a new transaction
START TRANSACTION;

-- 2. Delete data from the order table
DELETE FROM Orders;

After the execution of the above statement, we will get the output as below that shows all the records from the table Orders were successfully deleted.

Now, we need to open a separate session of MySQL database server and execute the below statement to verify the data in Orders table:

It will give the output as below.

Although we have made changes in the first session, we still can see the records are available in the table. It is because the changes are not permanent until we have not executed the COMMIT or ROLLBACK statement in the first session.

Therefore if we want to make changes permanent, use the COMMIT statement. Otherwise, execute the ROLLBACK statement to roll back the changes in the first session.

-- 3. Rollback changes    
ROLLBACK;

-- 4. Verify the records in the first session
SELECT * FROM Orders;

After the successful execution, it will produce the following result where we can see that the change has been rolled back.

Statements that cannot be a rollback in using MySQL Transaction.

MySQL Transaction cannot be able to roll back all statements. For example, these statements include DDL (Data Definition Language) commands such as CREATE, ALTER, or DROP database as well as CREATE, UPDATE, or DROP tables or stored routines. We have to make sure that when we design our transaction, these statements do not include.

SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT

The SAVEPOINT statement creates a special mark with the name of the identifier inside a transaction. It allows all statements that are executed after savepoint would be rolled back. So that the transaction restores to the previous state it was in at the point of the savepoint. If we have set multiple savepoints in the current transaction with the same name, the newly savepoint is responsible for rollback.

The ROLLBACK TO SAVEPOINT statement allows us to rolls back all transactions to the given savepoint was established without aborting the transaction.

The RELEASE SAVEPOINT statement destroys the named savepoint from the current transaction without undoing the effects of queries executed after the savepoint was established. After these statements, no rollback command occurs. If the savepoint does not exist in the transaction, it gives an error.

The following are the syntax of the above statements in MySQL Transaction:

SAVEPOINT savepoint_name
ROLLBACK TO [SAVEPOINT] savepoint_name
RELEASE SAVEPOINT savepoint_name

Example

Let us understand how to use these statements through the example. In the below example, we are going to use SAVEPOINT and ROLLBACK TO SAVEPOINT statements that explain how a savepoint determines which records of the current transaction can be rolled back.

START TRANSACTION;

SELECT * FROM Orders;

INSERT INTO Orders(order_id, prod_name, order_num, order_date) 
VALUES (6, 'Printer', 5654, '2020-01-10');

SAVEPOINT my_savepoint;

INSERT INTO Orders(order_id, prod_name, order_num, order_date) 
VALUES (7, 'Ink', 5894, '2020-03-10');

ROLLBACK TO SAVEPOINT my_savepoint;

INSERT INTO Orders(order_id, prod_name, order_num, order_date) 
VALUES (8, 'Speaker', 6065, '2020-02-18');

COMMIT;

In the above,

We have to first begin the transaction and then show the records available in the Orders table.
Next, we have inserted one record into the table and then creates a savepoint mark.
Again, we have inserted one record into the table and then use a ROLLBACK TO SAVEPOINT statement to remove changes where the savepoint established.
Again, we have inserted one record into the table.
Finally, execute the COMMIT statement to make changes permanently.

The output below explains the above steps in a sequential order that helps to understand it very easily.

Now, we will use a SELECT statement to verify the above operation. In the output, we can see that the order_id=6 and order_id=8 is added successfully, but order_id=7 is not inserted into the table. It rolls back the values entered after the savepoint was established:

Now we are going to take another example RELEASE SAVEPOINT that establishes the my_savepoint and then removes a savepoint.

START TRANSACTION;

INSERT INTO Orders(order_id, prod_name, order_num, order_date) 
VALUES (7, 'Ink', 5894, '2020-03-10');

SAVEPOINT my_savepoint;

UPDATE Orders SET prod_name='Scanner' WHERE order_id=8;

RELEASE SAVEPOINT my_savepoint;

COMMIT;

In the output, we can see that all statements in the transaction executed successfully. Here, both INSERT and UPDATE statements modify the table at COMMIT.