Transaction Management in MySQL

Back to: MySQL Tutorials for Beginners and Professionals

Transaction Management in MySQL

In this article, I am going to discuss Transaction Management in MySQL with Examples. At the end of this article, you will understand what is a transaction and why we need transaction management as well as we will also discuss and explore the Transaction Control Language commands in MySQL with examples.

Transaction Control Language in MySQL

Before understanding MySQL Transaction Control Language commands, let us first understand what is a transaction, why do we need to manage the transactions i.e. transaction management, and then we will see how to manage the transaction using TCL commands in MySQL.

What is a Transaction?

The transaction allows us to group a set of related tasks as one logical unit and all of these sets of related tasks are either get committed or get rollback if there is an error. In technical words, we can say that a transaction in MySQL is a set of SQL statements (mostly DML Statements) that should be executed as one unit.

So, a transaction ensures that either all of the command succeeds or none of the commands succeeds. If one of the commands in the transaction fails, then all of the commands fail and any data that is modified in the database is rolled back. If all the commands executed successfully, then the modification made to the database are committed.

Why we need Transaction Management in MySQL?

When we use the DML statement as soon as we execute the SQL statement the changes are made to the database and it is difficult to verify or roll back the changes made by DDL statements.

Let us see with an example, suppose user 1 and user 2 have accounts in the same bank and user 1 wants to transfer $100 to user 2 account. There will be a series of statements involved in it, first user 1 account would be debited by $100, then the user 2 account will be credited by $100 and finally, the transaction details will be saved in the bank records.

In this case, we need to perform at least two updates. The first update is happening in the sender’s account from where the money is sending and the other update is happening in the receiver’s account who is receiving the money. Both of these updates should either get committed or get rollback if there is an error. We don’t want the transaction to be in a half-committed state. For better understanding, please have a look at the below image.

Suppose, the first update work successfully and user 1 was debited by $100 and the 2^nd update failed and the user 2 account was not credited by $100 and the 3^rd query works successfully and the transaction details saved successfully. In this example, though all the queries interrelated they work separately and in case of failure of any of the queries, it makes it difficult to keep track of data or revert the changes. In such a case, we need to use transaction.

Let us see another example. Suppose, we have a Customer table and an Address table. And we want if someone adds a customer and an address, then both of the inserts should be successful or both of the inserts should fail, then in such cases, we need to use transactions.

A transaction is a logical group of one or more SQL statements. We can go interrelated SQL query in a single transaction and if any one of the queries fails, we can revert back the changes and if all the queries are successful, we can make the transaction complete

What is Transaction Management in MySQL?

The process of combining sets of inter-related Operations into a single unit and executing those operations by applying the do everything or do-nothing principle is called transaction management in MySQL. For example, the transfer money task is the combination of two operations

1. Withdraw money from the Senders account
2. Deposit Money into the Receivers account.

We need to execute the above two operations by applying the do-everything or do-nothing principle is nothing but transaction management. So, every transaction has two boundaries

1. Beginning
2. Ending

And controlling the boundaries of a transaction is nothing but transaction management.

How to implement Transaction Management in MySQL?

In order to understand how to implement transaction management in MySQL, please have a look at the following diagram. As you can see in the following diagram, transaction management involves three steps. First, we need to begin the transactions. Then we need to write the SQL Statement which we want to execute as a single unit. In the third step, we need to check for errors. If there is any error i.e. any of the SQL statements fails, then roll back the transaction (any data that is modified in the database will be rollback) else commit the transaction so that the data is saved permanently to the database.

Transaction Control Language Commands in MySQL:

To manage the transaction in MySQL, we are provided with transaction control language (TCL) commands. Transaction Control Language provides the following commands which we can use to implement transactions in MySQL.

START TRANSACTION: It indicates that the transaction is started.

COMMIT: It indicates that the transaction completed successfully and all the DML performed since the start of the transaction are committed to the database as well as frees the resources held by the transaction.

ROLLBACK: It will roll back the data to its previous state.

SAVEPOINT: This is used for dividing or breaking a transaction into multiple units so that the user has a chance of roll backing a transaction up to a point or location. IT creates points within the groups of transactions in which to ROLLBACK.

Note: The Transactional Control Language commands are only used with the DML statements such as INSERT, UPDATE, and DELETE. The TCL Commands cannot be used while creating tables or dropping (basically DDL operations) them because these operations are automatically committed to the database. In the next article, we will discuss the different types of Transactions in MySQL.

Example to implement Transaction in MySQL:

Let us understand how to implement transactions in MySQL with some examples. We are going to use the following Product table to understand transactions in MySQL.

Please use the following SQL Script to create and populate the Product table with the required sample data.

CREATE DATABASE ShoppingCart;
USE ShoppingCart;

CREATE TABLE Product
(
 ProductId INT PRIMARY KEY, 
 ProductName VARCHAR(40), 
 Price INT,
 Quantity INT
 );
 
 -- Populate Product Table with test data
 INSERT INTO Product VALUES(1001, 'Product-1', 1000, 100);
 INSERT INTO Product VALUES(1002, 'Product-2', 2000, 150);
 INSERT INTO Product VALUES(1003, 'Product-3', 3000, 200);
 INSERT INTO Product VALUES(1004, 'Product-4', 4000, 250);

Example of COMMIT Statement with DML statements in MySQL

The commit statement completes the transaction and saves the changes in the database permanently. The transaction can be inserted, update or delete queries. Once we execute the commit statement, we cannot revert back the changes. The commit statements can be executed after the transaction statement.

Let us understand the use of the Commit Statement with an example. In the below code, first, we start the transaction by executing the START TRANSACTION statement. Then we have two DML statements. One is inserting a new record while the second is updating a record in the Product table.

START TRANSACTION;
INSERT INTO Product VALUES(1005, 'Product-5', 5000, 300);
UPDATE Product SET Price =3500 WHERE ProductID = 1003;

Now execute the above three statements. Remember we are not executing either the rollback or commit statement. Now log out from the root user account and again log in to the root user account and verify the Product table and you will see the following records and you will Insert and Update statements data are not there in the table. So, in short, the insert and update were not successful as we didn’t run the commit statement.

The following is an example of a Commit Transaction. In the below code, first, we start the transaction by executing the START TRANSACTION statement. Then we have two DML statements. One is inserting a new record while the second is updating a record in the Product table. The last statement is the commit statement.

START TRANSACTION;
INSERT INTO Product VALUES(1005, 'Product-5', 5000, 300);
UPDATE Product SET Price =3500 WHERE ProductID = 1003;
COMMIT;

Now, execute the above statements one by one and once you execute the commit statement, then you cannot Rollback the above two DML statements. After committing the transaction, now the Product table will have the following data.

Note: The point here is that only after executing the commit statement the transaction is complete and the changes were saved in the database permanently.

What is the need for the ROLLBACK command?

The Rollback command in MySQL is used to undo the transactions that have not already been saved permanently to the database and get back to the initial state from where the transaction was started. So, if you want to restore the data into its previous state, then you need to use the ROLLBACK command at any time after the DML queries have been written but remember once the COMMIT statement has been executed then you cannot ROLLBACK the data.

Example of ROLLBACK Statement in MySQL:

The ROLLBACK statement reverts back the changes or restores the table to its previous data, the ROLLBACK must be executed before the COMMIT statement. After the ROLLBACK statement, we cannot COMMIT the statement, the ROLLBACK can be executed after the transaction statement.

Please execute the below SQL statement to understand the Rollback command. Here, first, we start the transaction and then we execute three DML statements.

START TRANSACTION;
INSERT INTO Product VALUES(1006,'Product-6',6000, 300);
UPDATE Product SET Price =550 WHERE ProductID = 1005;
DELETE FROM Product WHERE ProductID = 1004;

When you execute the above transaction and as you can see the transaction is not committed, but if you verify the table, then you will see the above changes in the database as shown in the below image. But these changes are not in a permanent state.

As you have not executed the COMMIT statement, then you have a chance to roll back all the DML operations on the table by using the Rollback command as follows.

ROLLBACK;

Once you execute the ROLLBACK statement, then verify the Product table and you will see the data in its previous state i.e. the state before the transaction as shown in the below image.

Why we need Transaction in MySQL?

We need the Transaction in MySQL to safeguard the enterprise data. A transaction is the propagation of one or more changes to the database. For example, if we are creating a record or updating a record, or deleting a record from a table or tables, then we are performing transactions on the table or tables. It is important to control transactions to ensure data integrity and to handle database errors.

What is the thumb rule of Transaction?

The rule of transaction tells that either all of the DML statements in the transaction executed successfully or none of those statements be executed.

In the next article, I am going to discuss the SAVEPOINT in MySQL with Examples. Here, in this article, I try to explain Transaction Management in MySQL with Examples. I hope you enjoy this Transaction in MySQL with Examples article and understand the need and use of Transactions in MySQL.