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:
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:
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: 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:
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:
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:
Durability: This property guarantees that the result of committed transactions persists permanently even if the system crashes or failed. It includes features:
MySQL Transaction Statement
MySQL control transactions with the help of the following statement:
Again, use the below statement to enable auto-commit mode:
MySQL Transaction Example
Suppose we have two tables named "employees" and "Orders" that contains the following data:
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:
Below are the commands that perform the above operations:
The below image explains it more clearly:
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:
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.
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:
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.
In the above,
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.